Encrypted column - limit key access

Question

I'm new to SQL encryption and have a SQL Server 2012 database. The database itself needs to be accessible by DBAs but the data within one of the columns a table should only be viewable in plain text by a few users.

To do this, I tried using a Symmetric Key to encrypt the column which works fine. However, I don't see the security in this as any of the DBAs who have access to the database also have access to the key (seems to be stored within the 'Security' folder in Management Studio).

How can I ensure that only certain users have access to the key itself so that it can be decrypted even if more users have access to the database itself?

If there's a parameter I can pass from the app, that works for me too (C#/WPF)

Thanks

Answer 1

All versions prior to 2016 (currently in CTP/beta) doesn't allow you to easily lock DBAs from the data. Column encryption that you employed stores the key in the DB and secures it using the key hierarchy within the database which roots itself in the service master key. That means you can't lock it away from DBAs. The only way out is to use extensible key management (ekm) to store your encryption keys outside of the database. That allows you to cut DBAs out of the loop but it implies your users are either technically savvy or you have done a great job integrating the user's application with the ekm provider. You can read up more about ekm and implementation options in the links below

• https://msdn.microsoft.com/en-us/library/bb895340(v=sql.110).aspx
• https://msdn.microsoft.com/en-us/library/dn198405(v=sql.110).aspx
• https://www.thales-esecurity.com/knowledge-base/solution-briefs/thales-microsoft-sql-server-ekm
• http://www.safenet-inc.com/data-protection/database-encryption/sql-encryption/

If you can upgrade to SQL Server 2016 (still in CTP/pre-release but by the time you test, upgrade, test, modify app, test it'll probably be released), the new Always Encrypted feature makes this a lot easier. Encryption takes place on the client (app) side and the master key is stored client side. Server will only ever receive cipher text so DBAs are essentially blocked even if they run server side or network level trace. However, that's only in 2016 and there's no indication it will be backported to earlier versions (rarely ever happens).

Answer 2

As SQLmojoe said, SQL 2016 may allow client side keys, which will require SQL 2016 service side AND a new version of the native client client side.

That is, however, merely a special case of the general rule: If you don't want DBA's seeing your data, you MUST encrypt it BEFORE sending it to the database; i.e. the application must do it.

Or for SQL 2016, the driver has to do it :).

Anything else, and it's likely the DBA's can just run Profiler and see the data even if they don't feel like finding the keys themselves and doing it as a set*

*DBA's who don't do things in sets are usually not very good DBA's.