SQL Server 2012 – Limit Key Access for Encrypted Column

cencryptionSecuritysql-server-2012

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

Best Answer

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

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).

Related Solutions

Sql-server – Copy SQL Server database to same server & decrypt by symmetric key return null

Following worked for me on SQL Server 2008:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master_key_password'
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY

For me it was essential to drop the encryption first, although the 'add' command should usually do this automatically.

Sql-server – Why use asymmetric keys in SQL Server

The proper key hierarchy is the Service Master Key protects the Database Master Key, the Database Master Key protects either an asymmetric key or certificate, the asymmetric key or certificate can be used for encryption or can be used to protect a symmetric key which is used for encryption. The keys are created in that order. The system creates and protects the SMK, and the DMK and subsequent keys are created by the user.

The design of the key hierarchy in SQL Server protects both the data and keys from compromise. Remember that with a symmetric key, the same key is used to encrypt and decrypt data, or in this case other keys. The main purpose of introducing an asymmetric key or certificate into the hierarchy protected by the Database Master Key is to prevent an attack on the DMK and SMK from inside of the database. If someone malicious wanted to obtain the DMK unencrypted, then that person would need to do one of two things. Either decrypt the service master key and use it to decrypt the DMK, which is not possible because only the database engine can use the Data Protection API to do this, or attempt to decrypt from the top of the hierarchy down, which would be possible if there were not an asymmetric key or certificate in the way.

All of the signed or encrypted bits of the symmetric keys in SQL Server are in a system table named sys.crypt_properties in each database, including the encryption of the Service Master Key in the master database. There is no system table that contains the private key for either of the asymmetric key types. If all keys in a hierarchy were symmetric keys, then the SMK would encrypt the DMK and the DMK would encrypt the symmetric key that would encrypt the data. Because of the way that symmetric keys work, that would also mean that, if someone opens the symmetric key for the data, then it can theoretically decrypt the DMK and the decrypted DMK can be saved by a malicious user or used to decrypt the SMK because the same key is used for encryption and decryption. This is why an asymmetric key or certificate is required to be an integrated part of the encryption hierarchy.

Best Answer

Related Solutions

Sql-server – Copy SQL Server database to same server & decrypt by symmetric key return null

Sql-server – Why use asymmetric keys in SQL Server

Related Question