Dear All

We have a requirement to either encrypt the whole of the database or part of it. To give you a bit of background. We write systems for customers including a SQL Server database 2008 R2. We have a requirement to encrypt due to some misuse of the database.

Our system uses a combination of SQL statements written directly into the Database, store procedures and views to access the data, which is through a .NET application.

We know that some of our customers will not install certificates onto the server.

We believe that encrypting all of the data will cause performance issues, so we believe that encrypting certain columns (i.e. those which are not primary keys) maybe the way to go.

What I would like to know is if we are going in the right direction here.

Thanks

I am including a few links at the end of the post that may be useful. Unfortunately there is no one-size-fits all solution; it depends on your particular scenario including what is the nature of your workload (i.e. how often do you have to write & read pages to/from disk vs. use the pages in memory), how easy it is to change the app, and how much transparency vs. isolation between users are you trying to achieve.

I will try to briefly explain the two main technologies that exist on SQL Server 2008 to help you protect data at rest: SQL Transparent Database Encryption (TDE), SQL Cell-Level Encryption (CLE) & app-specific data encryption.

TDE will encrypt data at rest. The physical DB files will be encrypted, but logically, SQL Server will read them and decrypt in memory, so no changes needed for the application (hence it is transparent encryption for the application). It is important to notice that this behavior means that SQL Server will have the plaintext pages in memory while the database is in use and any user with access to the tables will be able to read the data. Performance for TDE will depend on how often you write/read pages from disk.

CLE on the other hand requires that the application itself calls the functions to encrypt & decrypt the data as needed. The keys are accessible within SQL Server, so any user with sufficient permissions will be able to access the keys & read the data as plaintext, but users without permissions on the keys wont be able to read the data in plaintext, even if they have access to the table. Performance for CLE will depend on multiple factors, including the fact that CLE encryption is non-deterministic, so it is not possible to do a lookup on encrypted data (i.e. you will need to scan the whole table, or modify your app to create indexes on non-encrypted columns).

Both CLE & TDE can be used along with an HSM to encrypt the key-encrypting-keys outside SQL Server.

Finally, you can also decide that storing the keys within SQL Server is not secure enough for your scenario, and you decide to encrypt the sensitive data on the client code, probably using ad-hoc code or a third party library. In this case the application is 100% responsible for data encryption & key management.

In your description you mentioned misuse of the database. I assume this means that somebody was using an online DB and issuing unauthorized queries to extract data. Correct? If that is the case, it is likely that TDE will not meet your needs. CLE or app-specific encryption may be a better choice for your scenario, assuming the attacker does not have permission to access the keys.

The summary above obviously is lacking in detail, but the following links will provide much more details on each one of these technologies.

https://msdn.microsoft.com/en-us/library/bb669072%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

https://msdn.microsoft.com/en-us/library/ms189586.aspx

https://msdn.microsoft.com/en-us/library/bb934049.aspx?f=255&MSPPError=-2147217396

https://msdn.microsoft.com/en-us/library/cc645957.aspx

http://blogs.msdn.com/b/sqlsecurity/archive/tags/cryptography/

http://sqlcolumnencryption.codeplex.com/

 I hope this information helps,

-Raul Garcia

  SQL Server Security

Thank you for you help, you are a lifesaver :)

The misuse I said was to do with the database being read outside our suite of products. Basically our licencing agreement says that they data is theirs but it can only be read outside one of our products, we know some of our users are not doing that.

J