SQL Server Encryption To Block DBAs Data Access

By: K. Brian Kelley   |   Updated: 2012-12-19   |   Comments (21)   |   Related: > Auditing and Compliance

Problem

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.

Solution

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 Server

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

Next Steps

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

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips