Encryption in SQL Server (Part 3)

Now that we have the database setup for encryption as shown in Parts 1 and 2 of this series, it is time to actually encrypt the data in the database and view the data in decrypted form.

The great thing about all of these encryption capabilities being built into the SQL Server engine is that the commands that you use to encrypt/decrypt the data is very easy, in fact it is using the same commands you are already using to with some minor differences.

Before updating the data a new column that is in VARBINARY datatype will be required, since that is the datatype required for encrypted values:

ALTER TABLE Person.Contact
   ADD Phone_encrypt VARBINARY(128);

The length of the VARBINARY column will vary based on the length of the data you are encrypting and the type of algorithm being used. To find out what length of VARBINARY will work best for you, it is a good idea to setup a test where you populate the maximum length value of the column you are going to encrypt and do the encryption into a VARBINARY(MAX) column and use the DATALENGTH function to find out what the length of encrypted data comes to. Hopefully you are not encrypting BLOB data or other very large columns, in most cases a VARBINARY(128) or VARBINARY(256) will cover you, but setup the test as described if you have any doubts as you don't want to truncate the encrypted data.

The command to populate the new encrypted value column is a simple UPDATE command that you are already used to:

OPEN SYMMETRIC KEY AdventureWorks_SymKey DECRYPTION BY ASYMMETRIC KEY AdventureWorks_AsymKey; 

UPDATE Person.Contact SET Phone_encrypt = EncryptByKey(Key_GUID('AdventureWorks_SymKey'), Phone);

CLOSE SYMMETRIC KEY AdventureWorks_SymKey; 

http://technet.microsoft.com/en-us/library/ms190499(v=sql.105).aspxhttp://technet.microsoft.com/en-us/library/ms174361(v=sql.105).aspxhttp://technet.microsoft.com/en-us/library/ms177938(v=sql.105).aspx

This will replace the values that are in the column specified in the UPDATE command with the encrypted version of that value using the Symmetric Key. Depending on the amount of data in your table and the specs of the computer/server this operation should not take very long to complete, in most cases no longer then it takes to perform that same UPDATE command without the encryption.

Notice the OPEN and CLOSE commands that are wrapped around the UPDATE, those are required as you need to make the Symmetric Key that you are using for encryption available and this is done by opening it. For good practices and security is best to then close the key after you have completed the work that you need to do.

Now that you have encrypted the data in that column, lets decrypt the data and see if comes back as the same value that you originally encrypted.

There are a couple of different ways to write the SELECT statement that does the decryption, first is using the Asymmetric Key directly:

SELECT Phone_encrypt, Phone,
CONVERT(nvarchar(25),DecryptByKeyAutoAsymKey(AsymKey_ID('AdventureWorks_AsymKey'), NULL, Phone_encrypt, 0)) AS 'CustomerPhone_decrypted' FROM Person.Contact;

http://technet.microsoft.com/en-us/library/ms365420(v=sql.105).aspx

And the other way is to open the Symmetric Key as was done above to perform the UPDATE and use it to do the decryption:

OPEN SYMMETRIC KEY AdventureWorks_SymKey DECRYPTION BY ASYMMETRIC KEY AdventureWorks_AsymKey; 

SELECT Phone_encrypt, Phone, CONVERT(nvarchar(25),DecryptByKey(Phone_encrypt))) AS 'CustomerPhone_decrypted' FROM Customer.Contact;

CLOSE SYMMETRIC KEY AdventureWorks_SymKey; 

http://technet.microsoft.com/en-us/library/ms181860(v=sql.105).aspx

Either way you do it you notice that you will have to convert the decrypted value returned into a datatype that you can use, as both the DecryptByKeyAutoAsymKey and DecryptByKey will return a VARBINARY datatype. The DecryptByKeyAutoAsymKey function is very useful in views as it doesn't require the OPEN/CLOSE statements.

It is also worth mentioning the GRANT statements that will be required to give the appropriate rights to be able to see this decrypted data for users.

GRANT CONTROL ON ASYMMETRIC KEY::AdventureWorks_AsymKey TO User1;
GRANT VIEW DEFINITION ON SYMMETRIC KEY::AdventureWorks_SymKey TO User1; 

http://technet.microsoft.com/en-us/library/ms187991(v=sql.105).aspxhttp://technet.microsoft.com/en-us/library/ms179887(v=sql.105).aspx

Notice that CONTROL has to be granted to the Asymmetric Key and only VIEW DEFINITION is required on the Symmetric Key. These are the lowest grants necessary for the keys to work for any user needing to execute either of the SELECT commands shown above. The decryption functions will not work unless the appropriate grants are issued on BOTH the Asymmetric and Symmetric Keys.

Now that we have encrypted data in the database and verified that the encryption works by decrypting that same data via a SELECT statement I will use the 4th and final part of this series to show you how to restore backups of a database that contains encrypted data on another server and still be able to decrypt the values.