inroads into sql database encryption [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

Questions asking us to recommend or find a tool, library or favorite off-site resource are off-topic for Stack Overflow as they tend to attract opinionated answers and spam. Instead, describe the problem and what has been done so far to solve it.

Closed 9 years ago.

Improve this question

I followed article http://msdn.microsoft.com/en-us/library/ms179331.aspx with success, but can't translate this into security value added. I have too little context to completely understand the article -- if someone gains access to the mdf files, why not assume they have access to the keys and certificates?

Getting up and running with SQL has been very easy from a VS C# perspective, but beginning to understand the security risks a开发者_StackOverflownd safeguards is not proving the same.

I allow for the windows authentication magic (which I will hopefully someday demystify) that the server does to prevent random people from querying the database. Is that even an issue if shared memory is the only protocol? My other defenses are equally high-level (e.g. SQL injection is a non-issue with LINQ).

I'm looking for a point of entry into understanding the categories of risks -- any book title or link.

---

In the example you linked the data is encrypted with the symmetric key (SSN_Key_01) which in turn is encrypted with the a certificate (HumanResources037) that in turn is encrypted by the database master key that in turn is encrypted with the service master key that is stored in DPAPI and therefore encrypted with a key that can is encrypted with a key derived from the service account password. This is a fairly typical encryption key hierarchy. It protects primarily against accidental media loss: if someone gets hold of the MDF/LDF files or a backup file (eg. from an old HDD improperly disposed or from a lost/stolen laptop), these files cannot be used to retrieve the encrypted information because the 'founder' cannot decrypt the database master key. As a side note, accidental media loss is the major reason for sensitive data leaks, so its well worth protecting against. This scheme though it does not protect against compromised access to the SQL Server itself: if an attacker can run queries on the server (eg. SQL injection) it can retrieve the encrypted data even though it does not know the encryption key (of course, data is still subject to access protection, ie. read/write privileges on the data and on the keys). While it may sound bad that any user can decrypt the data (given enough privileges, even if it doesn't know the password), it is a given for any scheme in which the server has to serve the data w/o asking the user for the decryption password. In general though, if you can afford it (Enterprise Edition license required) Transparent Data Encryption is a better alternative that this scheme.

Another common scheme is when the data is encrypted with a symmetric key that in turn is encrypted with a certificate that in turn is encrypted with a password. In this scheme the server cannot decrypt the data unless the certificate is open in the session using the password. This scheme is common in multi-tenant applications, where each tenant wants to make sure that other tenants and/or the system administrators/owners cannot access the data (since they do not know the password). The application has to request the data access password on each session, since the server cannot decrypt the data (it doesn't know the keys).