SQL Server Encryption To Block DBAs Data Access

By:   |   Comments (21)   |   Related: > 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:

USE EncryptionTest;

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
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)
DECRYPTION BY PASSWORD = '17SomeHiddenPassword!76';
INSERT INTO dbo.EncryptedData
(ENCRYPTBYKEY(KEY_GUID('DataEncrypt'), 'Big Brother DBA is now blind!'));

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

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
FROM dbo.EncryptedData;
-- Try again, trying to force a decryption
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

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.

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.
DECRYPTION BY PASSWORD = '17SomeHiddenPassword!76';
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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author K. Brian Kelley 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

Comments For This Article

Tuesday, October 6, 2015 - 5:23:52 PM - Troy Frericks Back To Top (38832)

Consider using end-to-end encryption. For example, if the application is a web app that accepts credit cards, use an asymetric key algorithm to encrypt the data in the browser using javascript. Do the decryption just prior to sending the information to the credit processor. Policies & procedures should be in place for key handeling, access levels, etc... such that no one person has access to or has the ability to decrypt the data.




Friday, May 29, 2015 - 8:56:05 AM - K. Brian Kelley Back To Top (37321)

In order to keep the DBAs from seeing the password, you'd want to store this with the application. Also, you wouldn't want to call a stored procedure, because that would be visable in a trace or via extended events. SQL Server keys on the particular commands with regards to hiding the T-SQL from view. Obviously, a stored procedure name wouldn't do it. So if you go down this road, stored procedures are out with respect to the encryption side of things.

Thursday, May 28, 2015 - 11:43:28 PM - Tushar Verma Back To Top (37316)

This works fine. Now my only question is that what is the safest place to store the "passwrod" using which the key is genereated. If I directly use it in a stored procedure, then the DBA might open the SP, and use the passwrod written there to decrypt the sensitive information. What can be done for this?

Monday, May 12, 2014 - 8:07:11 AM - K. Brian Kelley Back To Top (30735)

Circumstances change. Folks who are trustworthy can change when in dire straits. Think about a DBA whose personal life has fallen off the deep end because of divorce, because of a drug addiction, etc. Businesses may deem some data sensitive enough where very few have access (a la, the US military). This would exclude DBAs. Keep in mind that my shop may have different operating standards than your shop and we're both different from Sharon's shop. As a result, it's better to understand all the options available than to just say, "This doesn't apply to me." It may today, but tomorrow brings a whole new set of challenges.

Monday, May 12, 2014 - 12:39:10 AM - DBA_Bob Back To Top (30729)

You need more trustworthy DBAs in your shop. In our shop the developers never have the keys to ANYTHING. Our DBAs are the control point and are charged with data security and integrity. They do not answer to the development control areas, so there can be no data compromises or corruptions.

Thursday, May 8, 2014 - 10:21:07 AM - K. Brian Kelley Back To Top (30673)

Ideally, the DBA has the ability to see the data. This makes a lot of the troubleshooting options easier. Preventing the DBAs from seeing the data takes away a lot of troubleshooting issues, too.

However, there are cases where the DBAs should not see the data. As a DBA I may not like it, but I understand that those situations do occur. Then it's a question of risk. If you have to protect the data in that way, you do incur greater risks on the recovery front. If an organization decides to do that, then hopefully they address those risks. None of this is any different than anything else we do in IT. It is a matter of requirements, risks, and mediation.


To make a blanket statement that DBAs should always have access... Sorry, I can't buy that. And there are cases as a DBA I don't want that information if I can avoid seeing it. Case in point: privacy data. If I can't view the privacy data, then I can't be the one responsible if the organization suffers a breach of that data. Having that protection protects me just as much as the organization. 

Thursday, May 8, 2014 - 9:36:28 AM - Really??? Back To Top (30669)

@Unconvinced - Well said and good one :)

Tuesday, May 6, 2014 - 12:19:44 AM - Unconvinced Back To Top (30618)

Tis all well and good and the separation of duties is and should used to protect your data and it unintended use.  Now consider what you expect of your DBA - trust issues not withstanding - To secure your data, to Backup your data , to recover your data in case of emergency.  And these also are also a good set of tenents to uphold. But, some day soon, the inevitable will happen . . . . your CONSISTENCY check , however you do it, will send you a message that XXX consistency errors were found and data page corruption has been detected.  And, no matter what encryption you have in place who ya' goin' to call ???  Your DBA . . . and to all the DBA's who get lumbered (or who are about to) with trying to recover said encrypted dead database. I hope you have practiced your recovery techniques . . .

TIP from one who has . .

learn to read the output of

DBCC Page 

Best of luck

Wednesday, April 30, 2014 - 11:27:34 AM - Wanderlei Santos Back To Top (30567)

I believe sql 2014 has some extra security/role settings that would let you set up a dba that's not a sysadmin, and would have limited access to data.

I suggest taking a look at it: http://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx#Security

Wednesday, March 27, 2013 - 2:03:07 AM - Andy Back To Top (23029)

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.

Thursday, December 20, 2012 - 2:46:46 PM - K. Brian Kelley Back To Top (21066)

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.


Thursday, December 20, 2012 - 1:36:57 PM - Norman Heyen Back To Top (21063)

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

Wednesday, December 19, 2012 - 5:57:13 PM - K. Brian Kelley Back To Top (21035)

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.

Wednesday, December 19, 2012 - 5:55:07 PM - K. Brian Kelley Back To Top (21034)

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 - 4:59:02 PM - TimothyAWiseman Back To Top (21033)

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 - 2:54:17 PM - SQLRumble Back To Top (21032)

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 - 12:36:19 PM - K. Brian Kelley Back To Top (21031)

"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 - 11:37:34 AM - Gene Wirchenko Back To Top (21030)
But not now that you have mentioned them.

Wednesday, December 19, 2012 - 10:46:53 AM - Pick your password Back To Top (21028)

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:





and a good password is:





Wednesday, December 19, 2012 - 8:10:23 AM - K. Brian Kelley Back To Top (21027)

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 - 6:10:20 AM - Abhi Back To Top (21024)

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



get free sql tips
agree to terms