By: K. Brian Kelley | Updated: 2012-12-19 | Comments (21) | Related: More > Auditing and Compliance
I have a requirement to encrypt the data within a database, but I cannot let the DBAs see the data. I could build encryption routines into the application, but I'd prefer to use SQL Server's built-in encryption. Is there a way I can do this? Check out this tip to learn more.
Absolutely. The key (pun intended) is to use a symmetric key with a specified password. Given a choice between symmetric and asymmetric keys (to include certificates), encryption via symmetric key algorithms is significantly quicker. However, most examples show the symmetric key being encrypted by a certificate or asymmetric key which is in turn encrypted by the database master key. Since the DBAs control the database master key, they can easily unlock the chain of keys and therefore they are able to decrypt the data.
To prevent this, we never allow the symmetric key to be encrypted by this chain of keys. Instead, we specify a password, one known to the application. When a password is specified, SQL Server will take appropriate steps to shield the password from the standard DBA toolset. As a result, we can rely on encryption of the symmetric key via a password to keep the DBAs' eyes off the data.
Let's demonstrate this with a test database:
CREATE DATABASE EncryptionTest; GO USE EncryptionTest; GO
Creation of the SQL Server Symmetric Key
Now let's create the symmetric key. Obviously, there is some overhead getting the key created without a DBA being able to record and/or memorize the password. Using a password generated by a random password generator is a start. But also make sure that the password doesn't exist in a script the DBA can store away and certainly do not put the script in source control.
-- Note the use of a symmetric key encrypted with a password CREATE SYMMETRIC KEY DataEncrypt WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = '17SomeHiddenPassword!76';
If you're worried about a DBA seeing the password using a trace, that's not a problem. SQL Server detects that a symmetric key is being created using a password and hides the sensitive information:
Encrypting Data Using the SQL Server Key
We'll need a table and we'll need some data:
CREATE TABLE dbo.EncryptedData ( EncryptedCol VARBINARY(128) ); GO OPEN SYMMETRIC KEY DataEncrypt DECRYPTION BY PASSWORD = '17SomeHiddenPassword!76'; GO INSERT INTO dbo.EncryptedData (EncryptedCol) VALUES (ENCRYPTBYKEY(KEY_GUID('DataEncrypt'), 'Big Brother DBA is now blind!')); GO
The OPEN SYMMETRIC KEY is hidden in profiler. While a DBA will know that the statement was executed, he or she won't be able to see the password.
Also note that since the INSERT statement is using encryption, SQL Server again hides the sensitive info by calling the whole thing a prepared query:
Attempts to Break the SQL Server Encryption
A DBA can try and get at the data, but it won't succeed. Without being able to open the symmetric key, the queries don't return anything usable. In order to simulate a DBA trying this, let's first close the key:
-- Close the symmetric key, which simulates the DBA not having the -- ability to use it because he/she doesn't have the password CLOSE SYMMETRIC KEY DataEncrypt; GO
You can try the following queries to see how everything fails without the key.
-- Try and query SELECT EncryptedCol FROM dbo.EncryptedData; -- Try and query using a conversion SELECT CONVERT(VARCHAR(MAX), EncryptedCol) FROM dbo.EncryptedData; -- Try again, trying to force a decryption SELECT CONVERT(VARCHAR(MAX), DECRYPTBYKEY(EncryptedCol)) FROM dbo.EncryptedData;
So the next step would be to try and get at the key. Let's try and open the key without specifying anything.
-- This will fail with a syntax error OPEN SYMMETRIC KEY DataEncrypt; GO
This doesn't work. In fact, it generates a syntax error:
What about specifying a password?
-- Trying to guess the password won't work. OPEN SYMMETRIC KEY DataEncrypt DECRYPTION BY PASSWORD = 'aDBAGuess!';
As you can see below, guess wrong and you get a different error. So while the DBA will know he or she didn't get the correct password, he or she still won't be any closer to cracking the data if you've been wise about choosing your password.
The Only Way to Get the Data in SQL ServerYou have to have the password for they key. If you have that, everything is easy. With respect to an application, you'd execute the OPEN SYMMETRIC KEY statement before performing any operations requiring the key, like so:
-- Only with the correct password does everything work. OPEN SYMMETRIC KEY DataEncrypt DECRYPTION BY PASSWORD = '17SomeHiddenPassword!76'; SELECT CONVERT(VARCHAR(MAX), DECRYPTBYKEY(EncryptedCol)) FROM dbo.EncryptedData;
And as you can see, this retrieves the data:
Also, the SELECT statement itself is protected:
Therefore, if you have to protect the data from the eyes of the DBA, this is a relatively simple solution that keeps the keys in SQL Server, yet keeps the data out of the hands of the DBAs. I will note one important exception. Obviously, anyone with debug rights at the OS level can look at memory. Therefore, there's always the risk of exposure from someone that has those rights. By default, the local Administrators group has those rights. Therefore, your system administrators (if sufficiently skilled) could look at that information. If your DBAs are administrators on the server where SQL Server is installed, they potentially could as well. However, it should be noted that if you do encryption within the application, you still face the fact that a system administrator could scan the memory. So you can't completely eliminate the threat. However, by taking advantage of SQL Server encryption in this manner you can certainly minimize overall data exposure.
- Read this tip to understand why symmetric key algorithms are faster than asymmetric key algorithms.
- Learn how to use authenticators to further protect your data.
- Understand how to allow SQL Server to handle "key escrow" if the DBAs are allowed to see the data.
- If you're using SQL Server 2008/2008R2/2012 Enterprise Edition, see how Transparent Data Encryption can protect the whole database when the files are at rest.
Last Updated: 2012-12-19
About the author
View all my tips