SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A Simple Approach to SQL Server 2005 Encryption

By Mike Good,

An Approach to SQL Server 2005 Encryption


There is an abundance of information regarding SQL Server encryption, too much almost. When starting out, I just did not get it. I read and re-read many articles, executed samples, but it all just seemed very confusing. I'm not an encryption expert, just a DBA trying to figure out how to implement new 2005 functionality.

Now with quite a bit of time invested, a lot of experimentation, and a lot of feedback from my coworkers, I believe I finally have figured this out well enough to have a plan of attack. Here I want to try to explain it in a way that I hope is clear and understandable on the first reading.

I also look forward to feedback, especially if I've made some fundamental error or have missed an important consideration.

Simplest Approach

When I want to use SQL encryption, what I really want to do is encrypt columns of data. SQL Server doesn't offer an "encryption" attribute for columns, so what we do is manually encrypt the data on insert/update and then decrypt the data when selecting. Let's use the following table for test purposes:

create table MyTable
  ID int not null identity(1,1) primary key clustered,
  ClearText varchar(16) not null, --would not have this column in a real table
  EncryptedText varbinary(68) not null

The simplest way to encrypt/decrypt data is with the EncryptByPassphrase() and DecryptByPassPhrase() functions. The problem with these is that we need to supply the passphrase every time we want to encrypt or decrypt the data. Hard-coding the passphrase into a stored procedures is no good, so we'll probably have to pass the passphrase in as a parameter to our data access stored procedures. Which means all calling applications will have to know about and protect the passphrase. All in all, I feel this is not the best way to proceed. My only point here is that we could do it as simply as this, if we chose to:

--passphrase approach
declare @s varchar(16);
set @s = 'TestText';

insert MyTable(ClearText, EncryptedText) values (@s, EncryptByPassPhrase('MyPassPhrase', @s));

--verify we can decrypt data
select ID,
  cast(DecryptByPassPhrase('MyPassPhrase', EncryptedText) as varchar(16)) as "Decrypted",
  EncryptedText --just to verify the data really is encrypted
  from MyTable;

That's it. Please note that so far we have NOT had to deal with certificates, master keys, service master keys etc. Our DB may not even have a master key yet, no problem.

Better Approach

The big problem with the above approach is dealing with the passphrase. How are we going to secure it? I don't want to have to worry about that if I don't have to.  Storing this passphrase inside a stored procedure is clearly no good.

Perhaps if we instead use the EncryptByKey() and DecryptByKey() functions these problems go away? Turns out the answer is "maybe"...it depends on how we encrypt our key. If we encrypt "by password" then we'll have the same fundamental problem as with the passphrase approach, and that password will be required for all data access.

But if we encrypt our symmetric key "by certificate" then there is no password/passphrase problem! We create our encryption key use the following syntax:

create symmetric key MyKey with algorithm=AES_256 encryption by certificate MyCert;

...oops, that doesn't work "Server: Msg 15151, Level 16, State 1, Line 2 Cannot find the certificate 'MyCert', because it does not exist or you do not have permission." I guess it makes sense that we need to create the certificate first:

create certificate MyCert with subject = 'MyCertSubj';

...oops, that doesn't work either! "Server: Msg 15581, Level 16, State 1, Line 2 Please create a master key in the database or open the master key in the session before performing this operation." I guess we need to create the DB master key first:

create master key encryption by password = 'SecretPassword';

That worked (finally!). Now we create the certificate, and then the key:

create certificate MyCert with subject = 'MyCertSubj';
create symmetric key MyKey with algorithm=AES_256 encryption by certificate MyCert;

Now we have a key. We only need to change our example data access code only a little bit: a) add a step to open the key, and b) replace the passphrase functions with their counterparts EncryptByKey() and DecryptByKey():

--symmetric key approach; need to open the key once per session
open symmetric key MyKey decryption by certificate MyCert;
--eliminate old data, then insert new data
declare @s varchar(16);
set @s = 'TestText';
delete MyTable;
insert MyTable(ClearText, EncryptedText) values (@s, EncryptByKey(Key_GUID('MyKey'), @s));
--verify we can decrypt data
select ID,
  cast(DecryptByKey(EncryptedText) as varchar(16)) as "Decrypted",
  from MyTable;

Note that nowhere in the data access code do we need to specify a password...this is definitely the way to go!

We should now be able to see why so many of the encryption articles discuss master keys etc...we must have one in order to create a certificate. I don't know much about master keys and I am pretty sure I don't need to know or care much about them, just need to have one.

Also note that unlike many other examples, the "create certificate" statement above does not use the "authorization" qualifier. That qualifier limits the certificate (and ultimately the entire encryption scheme) to a single user. For me that's a deal-bre aker. The way around this is to simply avoid the authorization qualifier, and instead control access to the certificate with grant statements.

Open Symmetric Key Has a Cost

There is definitely a cost associated with this statement. As long as we don't explicitly close our symmetric key, it stays open for the duration of our session. Several simplistic encryption approaches I've seen have an "open symmetric key statement" at the top of every insert/update/select stored procedure or workflow. This is definitely much more expensive than only opening the key when it's not open already. That is to say, it's far cheaper to test if the key is open than it is to open it again.

In a very simple performance test I conducted, inserting a few thousand rows of encrypted data took 44sec when opening the key for every single-row insert, and took only 13sec when opening the key only as needed.

Here's one way to tell if a key is already open:
if not exists(select 1 
              from sys.openkeys 
              where key_name = 'MyKey' and database_name = db_name()


This useful function is simply a combination of an "open symmetric key" statement and the DecryptByKey() function. It will open the key only if it has to, then does the decryption. I've verified this with simple timing tests, and I'm positive this function does not open the key every time it's called. I see no downside to ALWAYS using this function instead of DecryptByKey() when using keys encrypted by a certificate.

Aside from using this to replace DecryptByKey() in our data access code, another pretty cool use for this function is that we can use it to create views which decrypt the data automatically, on-the-fly. No need to worry about user's connection opening a key or not, it's automatic. Try it, it's easy!

There is no corresponding function for encrypting data...too bad.

How Big Does the Encrypted VarBinary Column Need To Be?

While we can always get by with varbinary(max), http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx basically says that we can use the following formula to determine precisely how big to make our varbinary encrypted data column (this page also warns us to test the result by encrypting the largest possible string and checking the size of the encrypted result):

declare @PlainTextLen int, --length of the plain text
  @UsesAuth tinyint, --1 if using the optional authenticator parameter, else 0
  @BlockSize tinyint; --8 if using DES, else 16 if using AES

select @PlainTextLen = 16, --my plaintext is 16 chars long
  @UsesAuth = 0, --not using authenticator
  @BlockSize = 16; --AES algorithm

select @PlainTextLen as PlainTextLen,
  ((floor((8 + @PlainTextLen + (@UsesAuth * 20)) / @BlockSize) + 2) * @BlockSize) + 20 as CipherLen;

Optimization - Indexing Encrypted Data

If we ever need to use the encrypted data as primary search criteria in queries (assuming our data is not trivially small) then we will need to take additional steps. We can't simply add a DecryptByKeyAutoCert(...) = @param clause to our WHERE clause and expect decent performance...this will surely result in a clustered index or table scan every time.

One way to ensure adequate performance is to add an extra indexed column to our table, containing a hashed version of the PlainText. There are a few good articles published on this subject. It's not that simple! If we apply a simple hashing function (e.g. checksum()) to our plaintext data, then an attacker can use a dictionary attack to determine the plaintext corresponding to our hashed values. Some of the articles I've read suggest very complex solutions to this problem.

I don't think it has to be that complex. I believe that a satisfactory solution to this problem is to apply a hashing function to a subset of our plaintext only--this way a dictionary attack will yield at most only a subset of the original plaintext. We will need to determine what constitutes an acceptable subset of plaintext on a case-by-case basis. Obviously if the subset is large, we risk giving away too much of the plaintext to an attacker; if the subset is small, then the value of the index is reduced.

Thus a simple but effective algorithm for such a function is:

    hash value = checksum(subset(plaintext))

Whatever function we pick, we need to add a new indexed column to our table, populate it with the hash value on inserts, maintain it on updates, and then in our queries use both the new column and the encrypted column (decrypted) in our WHERE clause. The optimizer will use the index on the new column to narrow the overall search, and only matching rows will require decryption to see which rows ultimately match our criteria.

create table MyTable
  ID int not null identity(1,1) primary key clustered,
  ClearText varchar(16) not null, --would not have this column in a real table
  EncryptedText varbinary(68) not null,
  HashedText int not null
create index MyTable_HashedText on MyTable(HashedText);

--a UDF to implement hash function
create function dbo.fn_HashText(@Text varchar(16))
returns int
  return checksum(isnull(left(@Text, 6) + right(@Text, 4), ''))

--insert row
declare @s varchar(16);
set @s = 'TestText';

insert MyTable (ClearText, EncryptedText, HashedText)
select @s,
  EncryptByKey(Key_GUID('MyKey'), @s),

--search for row (assuming table is not trivially small)
declare @searchstring varchar(16);
set @searchstring = 'TextText';

select ID,
  cast(DecryptByKey(EncryptedText) as varchar(16)) as "Decrypted",
  from MyTable
 where HashedText = fn_HashText(@s)
  and cast(DecryptByKey(EncryptedText) as varchar(16)) = @searchstring;

Moving Encrypted Data to Another Server - Disaster Recovery

One thing we want to be sure of is that we can move our encrypted data to another server and still be able to decrypt it. For now let's call the server currently containing the encrypted data the "primary" server, and the server we want to move data to the "secondary" server.

The absolute simplest way to make this happen is to copy the primary server's service master key over to the secondary server. The only way to synch service master keys is to backup the key on the primary server and then restore it on the secondary server.

--on primary server: backup service master key
backup service master key to file = 'service.key' encryption by password = 'MyPwd';
--on secondary server: restore service master key
restore service master key from file = 'service.key' decryption by password = 'MyPwd';

Then when we restore/log-ship/replicate data from the primary server to the secondary, the same encryption/decryption mechanisms that work on the primary server will work on the secondary. For log-shipping, this is the only way that will let us access data in a standby secondary database.

Note: We don't have to know anything about these service master keys, just that they're synched!

Clearly, it's best to do this when the secondary server is relatively new and there are not any dependencies on the service master key. Once someone starts using encryption functionality on that server, we can no longer change the service master key without consequences. If we cannot replace the secondary server's service master key, then we must tackle things at the DB level. That is no big deal, but is beyond the scope of this article.

Proposed Approach

Now I'll sum all this up and present what I believe is the best way to implement data encryption in SQL 2005.


  • Create a master key, if one does not already exist.
  • Create a certificate, do not specify the authorization qualifier.
  • Create a symmetric key, being sure to specify the key_source and identity_value parameters--this way we will be able to recreate the exact same key in a different DB or on another server. We must choose an encryption algorithm; I typically use DES for playing around on my workstation, and AES_256 for production.
  • Create a DB role for our application (or whatever).
  • Grant control access on the certificate to the DB role. Users in this role will be able to automatically access the certificate.
  • Grant view definition access on the symmetric key to the DB role.
  • At this point all users in the DB role will be able to encrypt and decrypt data. Use role membership and standard TSQL grants (e.g. to views or stored procedures) to achieve more granular control as desired.
  • If the encrypted data will ever be the primary search criteria in queries, write a UDF hash function, add an indexed column containing the hash value, and reference that column in queries. See above for detail.
  • If there is any requirement to restore, log-ship, or replicate this data to another server, synchronize service master keys on both servers. See above for detail.
  • Save scripts for the above in source control. In the event that master keys are lost, we can always use these to gain access to encrypted data.
if not exists (select 1 from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##')
    create master key encryption by password = 'SecretPassword';
if not exists (select 1 from sys.certificates where name = 'MyCert')
    create certificate MyCert with subject = 'MyCertSubj';
if not exists (select 1 from sys.symmetric_keys where name = 'MyKey')
    create symmetric key MyKey with algorithm=AES_256,
     key_source='SomeUniqueString', identity_value='SomeOtherUniqueString',
     encryption by certificate MyCert;

create role TestRole;
grant control on certificate::MyCert to MyRole;
grant view definition on symmetric key::MyKey to MyRole;

Data Access:

  • To insert/update/encrypt data, check if the key is already open and if not, open it. Then perform the insert using EncryptByKey().
  • To select/decrypt data, use DecryptByKeyAutoCert() and cast the result to the proper datatype.
--open symmetric key if needed before insert/update
if not exists(select 1 from sys.openkeys where key_name = 'MyKey' and database_name = db_name())
  open symmetric key MyKey decryption by certificate MyCert;

insert ... EncryptByKey(key_guid('MyKey'), @ClearText) ...

--but no need to open symmetric key before select
select ... convert(varchar(16), DecryptByKeyAutoCert(cert_id('MyCert'), null, EncryptedText)) ...

Total article views: 20007 | Views in the last 30 days: 23
Related Articles




Encryption, Certificate

Encryption, Certificate management in database


Lost Certificate and Master Key

Lost Certificate and Master Key


Encryption Certificate in sql server 2008

Encryption Certificate in sql server 2008


SMKs, DMKs, Certificates for TDE and Encrypted Backups

This article details SMKs, DMKs and certificates in SQL Server as they relate to Transparent Data En...

sql server 2005