Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































SQL Server Encryption To Block DBAs Data Access

MSSQLTips author K. Brian Kelley By:   |   Read Comments (12)   |   Related Tips: More > 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:

In SQL Server Profiler, the tool detects that a symmetric key is being created and the data is not available

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.

The OPEN SYMMETRIC KEY command is hidden in Profiler as well

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:

SQL Server again hides the sensitive info from SQL Server Profiler

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:

Attempts to Break the Encryption via T-SQL commands

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.

Guess the wrong password and you get a different error

The Only Way to Get the Data in SQL Server

You 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:

The Only Way to Get the Data is with the OPEN SYMMETRIC KEY and DECRYPTION BY PASSWORD commands

Also, the SELECT statement itself is protected:

The contents of SELECT statements are protected in Profiler

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


Last Update: 12/19/2012


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

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Wednesday, December 19, 2012 - 6:10:20 AM - Abhi Read The Tip

Hi Brain,

Thanks for above article but my question here

how we can enrypt many tables at once, do we need to write query again and again i.e. same script for all tables or is there any single script for this

Thanks

Abhi


Wednesday, December 19, 2012 - 8:10:23 AM - K. Brian Kelley Read The Tip

Putting in SQL Server's built-in encryption after the fact is a time-consuming process. There's no quick way to do this. You'd have to build the scripts to create the varbinary columns, then encrypt the data into them, and then remove the old columns. As a result, there's nothing within SQL Server that would let you do this quickly and easily, especially for many tables.

You're probably going to need to look at a 3rd party product if that's the case. There have been some on the market that do just that: encapsulate whole tables and keep the data away from all eyes that don't come through the product, even the eyes of the DBAs.


Wednesday, December 19, 2012 - 10:46:53 AM - Pick your password Read The Tip

If you try encryption with a password, you must keep it secure.  More importantly, when people that know the password are no longer in positions where they should know the password, or when those who aren't in positions where they should know the password find it out (usually during some emergency... or an "emergency"), the password should be changed.

 

Therefore, one needs to consider how the passwords will be changed.

 

Further, guessing the password _does_ work once you guess successfully.  Therefore, you cannot use passwords like those listed if you actually want to keep your data secure against those using serious dictionary and rules-based dictionary password crackers.  A three word combination plus a couple numbers in front and three number/symbols at the end (_especially_ 1 or ! or 123, which are very commonly used) is going to be vulnerable to a serious brute-force effort... which can be done, by anyony with access to database backups (DBA), offline and on multiple machines at once.  Backup the database, take the backup offsite, crack to your heart's content.

 

Note that the very requirement to keep the data hidden from DBA's indicates that DBA's are not and cannot be trusted with this data per the rules of the organization, therefore, the security should be sufficient to protect against an active cracking attempt by said untrusted DBA's.

 

Good passwords are completely random.  Since completely random passwords are hard for most people to type, they should be stored in a secure, encrypted manner (Keepass 2.x, Office 2007+ Prepare, Encrypt, FreeOTFE drive, Truecrypt drive, etc.), and then copy and pasted when required.  Once you're at the stage of copy and pasting the password, length and complexity cease to matter; therefore, a maximum length, maximum complexity password is no more difficult to use in practice than a minimally secure password, and if you use maximums, you don't have to quibble and argue and debate over what the acceptable minimum is... nor do you have to change when the acceptable minimum changes due to law, regulation, or corporate decision.  For SQL Server, 128 characters long (maximum length) and including extended ASCII is valid.  For some programming languages, extended ASCII and certain symbols are problematic, but at length 128, even pure random digits (no symbols, no letters) gives 1E128 possible passwords, which is a slightly greater value than 2^384 (3E115, which is equivalent to a length 65 upper, lower, number random passwords where the characters in the password are not known), i.e. it's easier to brute force a 384 bit binary key.

 

I.e. a great password is:

qH!0M2NnUD0ZRr05Fvsnw+V?}!HGrXey(Jdh\5(P1F:H@rOq*r8#k9omD)y[jsz\_tYp#6?pd.\}fXp~AQ=pd5@sk1Y^H2tpV~fAf-4a5!X]X{.iLrvIaMan~9[HaHJ9

or

F0lV4aKc4qkBlwxg5SotzZqeAebwB6DnsAJp0ivJzYsx0fWQFLlYi7XhbUwmtVKNB3mRPiu5Ohcge7oDyu28ZgDzD4WnRQlYM2NcXLBh8bp25haLJcryKrldEWp8WKSj

 

and a good password is:

AauOdH2NeznzRapF3WRIJ6P37jj4sELpQobCgBTuWLKGjzv7aa5RbJcRgw0b0cxHXMACgtELXObDfTX7vLPg0uMr2Vwag0FQcl6i1CzmpMr1nBvVZmBCgDnz2H3577Fj

or

00612873175251783317215595986222170103437102201738622871651139904548606931195872422709496264462579332922109497631666156269839343

 


Wednesday, December 19, 2012 - 11:37:34 AM - Gene Wirchenko Read The Tip
But not now that you have mentioned them.

Wednesday, December 19, 2012 - 12:36:19 PM - K. Brian Kelley Read The Tip

"Note that the very requirement to keep the data hidden from DBA's indicates that DBA's are not and cannot be trusted with this data per the rules of the organization"

Let me correct something right away. The fact that you encrypt a data so a DBA can't see it doesn't say anythign about a DBA being trusted or not. Case in point: classified data in the military. I carried a secret clearance when I was in. However, I would only be allowed to view documents and material that were applicable to the job I did. This is known as "need to know." It's not that anyone is inherently untrustworthy. If that was the case, I wouldn't have had a secret clearance. And that would mean many, many folks that carry secret and top secret clearances would be untrustworthy, too.

The reason for need to know is several fold:

  • If you don't have access to the data, YOU have repudiation.
  • The less folks who have access to the data, the less folks you have to check out in case there is a disclosure.
  • The less folks who have access to the data, the less likely it will be disclosed, especially in an ACCIDENTAL manner.
  • Sometimes being able to see multiple sets of data at a lower level allows you to put the pieces together for data a higher level. This is why the military makes a big deal about Operational Security or OPSEC. So you protect all the lower levels as much as possible.

 


Wednesday, December 19, 2012 - 2:54:17 PM - SQLRumble Read The Tip

Question, can you implement this on a database being used by a third party application?  Will the application still work with no changes?  Does SQL Server decrypt everything?  OR will the application need amending to accomodate the encryption?


Wednesday, December 19, 2012 - 4:59:02 PM - TimothyAWiseman Read The Tip

This is a great article, thank you very much for posting it.  It relates to a question that came up somewhat recently on Ask.SQLServerCentral.

One thing you mention, but that bears repeating is to make sure that keys and passwords are appropriately backed up.  Losting those is effectively tantamount to losing the encrypted column.


Wednesday, December 19, 2012 - 5:55:07 PM - K. Brian Kelley Read The Tip

This can't be used with a third party app because opening the key in order to encrypt/decrypt the data must be called by the same SPID. As you might guess, this means that third-party app would need to know to do so.


Wednesday, December 19, 2012 - 5:57:13 PM - K. Brian Kelley Read The Tip

Aye, Timothy, that's very true. When you are dealing with encryption, you must have an absolutely sound plan for securing and storing the keys and/or passwords used. This is true whether we're talking SQL Server, EFS, Bitlocker, or some other encryption process.


Thursday, December 20, 2012 - 1:36:57 PM - Norman Heyen Read The Tip

Is the data sent unencyprted on the network? Meaning is it unencrypted at the SQL Server end or at the application client end? Otherwise WireShark comes to mind...


Thursday, December 20, 2012 - 2:46:46 PM - K. Brian Kelley Read The Tip

Yes, the data would be sent across the wire unencrypted. However, remember that with respect to Wireshark, it could be looked for, especially with tools like Altiris and System Center - Configuration Manager. Seeing it installed would be a red flag. That doesn't stop the networking folks from using a span port.

With that said, it is possible, using IPSEC policies, to ensure ALL database traffic is encrypted between client and server. This is transparent to SQL Server. And then, if SQL Server is so configured, it could ensure that everything transmitted is encrypted as well. This would get around a packet sniffer, whether installed on the host or via a span port.

 


Wednesday, March 27, 2013 - 2:03:07 AM - Andy Read The Tip

Brian, Norman Heyen,

I would like to mention we have quite unique tool for database encryption in SQL Server which solves the problem with Wireshark and SQL Profiler. It's name DbDefence (can be found easilty if you are interested)  It can:

-Encrypt all incoming SQL statements while transmitted over network protocols.

-Encrypt database files complely.

-Do not let DBA see database catalog

-Hide all related SQL statements from Profiler

-Automatically encrypt backups

-Encrypt database and wrap fontend application to use encrypted database without chaning ANYTHING in front-end.

-free version for small databases.

Please do not consider my post as advertising. I think that's very related to the problem and could be a solution for many.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.