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
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'
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'
BACKUP MASTER KEY
TO FILE = 'C:\localhost_AdventureWorks_DMK.dmk'
ENCRYPTION BY PASSWORD = 'U982LKJOWlkslpq&^@#lskjnkxOPx.w'
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.