Encryption in SQL Server (Part 2)

This is part 2 in my posts on setting up encryption in SQL Server. To get the details on how to setup encryption on your SQL Server instance and the database, please read part 1 first.

Now that we have the SQL Server instance and database ready to encrypt our data we can go into details on how the individual columns of data will be encrypted in the database. At this point you also have some decisions on what types of keys you want to have created and how strong the cryptography algorithm will be protecting them.

Before diving into the next layers of keys, it is important to understand the differences between Asymmetric and Symmetric Keys and how SQL Server handles each. This article on TechNet - Cryptography in SQL Server does a great job explaining the differences.

The recommended best practice is to setup an Asymmetric Key as the next layer and make this key as strong as you can and use a Symmetric Key to do the actual data encryption. The reason for this is that Asymmetric Keys require a lot more processing power to use, so you only use the Asymmetric Key to protect the Symmetric Key. The Symmetric Key is what is used to actually encrypt your data, then only when the Symmetric Key is first being accessed does the system have to decrypt the Asymmetric Key. Otherwise you will have to pay the Asymmetric Key processing cost every time a value is being encrypted or decrypted.

In SQL Server you have many options when creating these Asymmetric Keys, but the one most often used is RSA_2048. This is currently the highest level of security that SQL Server supports for Asymmetric Keys. Below is the command you use to create the Asymmetric Key:

IF NOT EXISTS
(SELECT * FROM sys.asymmetric_keys WHERE [name] = 'AdventureWorks_AsymKey')
CREATE ASYMMETRIC KEY AdventureWorks_AsymKey WITH ALGORITHM = RSA_2048;

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

The naming of these keys is important, as you will need to reference this key in other commands to do the encryption/decryption so make sure you pick a name you can keep track of.

As mentioned above you will also need a Symmetric Key to do the actual data encryption that uses the Asymmetric Key. With Symmetric Keys you have to use a different set of algorithms because Symmetric Keys work differently than Asymmetric Keys as they have a pair of keys created (public/private). To get a great overview of what Symmetric Key encryption is see this HowStuffWorks article. Below is the command to create a Symmetric Key:

IF NOT EXISTS
    (SELECT * FROM sys.symmetric_keys WHERE [name] = 'AdventureWorks_SymKey')
    CREATE SYMMETRIC KEY AdventureWorks_SymKey
        WITH KEY_SOURCE = 'I82xskoiw820KOW>282kxow',
            ALGORITHM = AES_256,
            IDENTITY_VALUE = 'w,xi292XWE82S92iqoxl*&23'
        ENCRYPTION BY ASYMMETRIC KEY AdventureWorks_AsymKey;

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

I used AES_256 as the algorithm in this Symmetric Key as it is the strongest algorithm allowed at this time in SQL Server. The DES and RC algorithms are not recommended as they have all been cracked and could make your encrypted data accessible.

As I learned how to use these keys in my prototyping I ran into an important issue that I want to highlight here. When you create the Symmetric Key it is very important that you be able to re-create the exact same key if you accidentally lose that key. Since the Symmetric Key is going to be used to do all of the data encryption, if that key is lost you will not be able to get your data back in decrypted form if you lose it. This is where the KEY_SOURCE and IDENTITY_VALUE parameters of that command come into use. As long as you make sure to use those same values then you can recreate the same Symmetric Key (assuming you have the Database Master Key and Asymmetric Key setup the same as well, I will cover how to get this setup if you have moved to another server in a later part of this series).

There are no commands to backup the Asymmetric or Symmetric Keys like we had for the Service Master Key and Database Master Keys. If you decide to use Certificates over the keys, then you can backup the certificates. I have not used certificates in the systems that I setup encryption on, mainly because SQL Server currently does not fully handle certificates as it should. If you would like to get more details on creating certificates in SQL Server, see this TechNet article: http://technet.microsoft.com/en-us/library/ms187798(v=sql.105).aspx.

Now we have laid all of the groundwork for setting up encryption on your SQL Server database, so the next part we will start to actually encrypt data and decrypt that data as well!