Encryption in SQL Server (Part 1)

Recently I have been doing some work to add encryption to an existing SQL Server 2008R2 database for a client and I learned a lot about how TDE or Transparent Data Encryption works in SQL Server. The requirements for adding encryption in this case was that only certain columns in some tables of the databases would be encrypted, so I could not encrypt the entire database or tables in a database (this is possible in SQL Server though). Also one of the main requirements was to leave the datatype for the column that would be encrypted the same, and mask the data in that column. So, all of the actual encrypted columns would be new columns added to the appropriate tables. It was also required that the only way to access the decrypted values was by using new views that did the decryption.

With those requirements set I started to learn how to setup encryption in SQL Server and found many TechNet articles that helped me. To begin with some of the great resources for introduction to encryption in general are:

• Understanding Transparent Data Encryption - http://technet.microsoft.com/en-us/library/bb934049(v=sql.105).aspx
• Encryption Hierarchy - http://technet.microsoft.com/en-us/library/ms189586(v=sql.105).aspx
• SQL Server and Database Encryption Keys (Database Engine) - http://technet.microsoft.com/en-us/library/bb964742(v=sql.105).aspx

These posts helped me to get an idea of how best to setup the different keys/certificates that are required in SQL Server to make the encryption work and also provide the required security to lock down who could access the decrypted values. The important thing to realize about setting up encryption is that SQL Server is using a layered approach, you aren't just setting up one set of keys and then you are done. Instead you are setting up multiple keys that each use the previous key to build the next (see Encryption Hierarchy article for graphical representations of this).

In SQL Server you start with the Service Master Key (SMK) which is setup for your automatically when you install a SQL Server instance. This key is protected by the Windows OS itself using the Data Protection API.

The next layer of key is the Database Master Key (DMK), which is required on each database that you will be using encryption in. The DMK is created by issuing a command on the database you want to create it on.

IF NOT EXISTS
   (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
   CREATE MASTER KEY ENCRYPTION BY
   PASSWORD = 'Ils20*(LKjqwnslqo372,cklweLKHJn'

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

In order to create the layers of keys required to actually do the encryption of your data you must have the DMK already created in each database. There can be only one DMK per database in SQL Server and it is protected using the password supplied and the Triple DES algorithm (AES_256 is used in SQL Server 2012 and above). Since automatic decryption of the DMK is required for use by other SQL Server commands, a copy of the DMK is also protected using the SMK and stored in the database it is created in along with the master database. This copy of the DMK is the one that can be updated easily as you move the database or it's backups from server to server and still allows you access to the encrypted data without requiring you to decrypt all of the contents and then re-encrypt them again using the new DMK. Updating the DMK is done with the ALTER MASTER KEY command (http://technet.microsoft.com/en-us/library/ms186937(v=sql.105).aspx).

Since all encryption is dependent on both the SMK and DMK, it is also good practice to backup both of these keys to files for safe keeping, which can be done with 2 commands:

BACKUP SERVICE MASTER KEY
    TO FILE = 'C:\localhost_SMK.smk'
    ENCRYPTION BY PASSWORD = 'ADa329wopkj*&ER.slkqksl'

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

BACKUP MASTER KEY
   TO FILE = 'C:\localhost_AdventureWorks_DMK.dmk'
   ENCRYPTION BY PASSWORD = 'U982LKJOWlkslpq&^@#lskjnkxOPx.w'

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

The password that is supplied with these BACKUP commands is only used to encrypt the files that are created on the file system. In order to restore these files, the appropriate passwords will be required, so they should be kept safe for future reference (as should all passwords created for these keys).

This will get your SQL Server instance and database all setup and ready to encrypt the actual data stored in them.

In my next post I will cover what Asymmetric Keys and Symmetric Keys are and how they are created and used in SQL Server to encrypt your data.