At the heart of encryption are keys. These allow you, the authorized user, to unlock the subsequent key in a key hierarchy or the cipher text contained within a database. This hierarchical relationship in SQL Server 2008 is illustrated at http://msdn.microsoft.com/en-us/library/ms189586.aspx. For SQL Server 2005, the hierarchy is illustrated at http://msdn.microsoft.com/en-us/library/ms189586(SQL.90).aspx

Within SQL Server, the head of the encryption family is the Service Master Key. This key is a Symmetric key that is based off of the service account credentials as well as the machine key from the Windows Data Protection API (DPAPI). There is only one Service Master Key per instance of SQL Server and is created at setup of that instance.

The Database Master Key is a Symmetric key that is unique to each catalog (database) within the SQL Server instance. This key is encrypted using the Service Master Key of that instance. When a catalog is created the Database Master Key is not automatically generated. These keys are created using the CREATE MASTER KEY command.

Once the Database Master Key is created, additional keys can be created to increase the granularity of of the encrypted data. For example a table or cell can be encrypted with one key while another table or cell could be encrypted with a separate key. Both keys are therefore encrypted by the Database Master Key. These keys can fall into the following types:

Asymmetric Keys are the combination of a private key and a public key. The plain text is encrypted using the public key, which is distributed to others, and is decrypted by a corresponding private key which is very limited in its distribution. An Asymmetric Key is created using the CREATE ASYMMETRIC KEY command.

A Symmetric Key is a single key that is used to encrypt and decrypt data. A Symmetric Key can only decrypt the data that was encrypted by itself. As previously noted the Service Master Key and Database Master Key are considered this type of key. A Symmetric Key is created using the CREATE SYMMETRIC KEY command.

Certificates are private or public keys that are digitally associated with an individual or device. The use of a Certificate is very similar to Asymmetric Key. These can be created externally from SQL Server and can offer expiration management. To create a Certificate within SQL Server you will use the CREATE CERTIFICATE command.

When a database is backed up through the standard means, the keys that are applied to the instance and subsequent catalogs are not included. Each key must be individually backed up using the BACKUP SERVICE MASTER KEY, BACKUP MASTER KEY or BACKUP CERTIFICATE commands. A best practice is not to store these key backups on the same device as the database backup in the event that the device is compromised.

While a mile of text could be used to provide more details regarding keys, this cursory review should provide the novice cryptographer with some basic information that will help begin to lift the fog in the implementation of this valuable feature of SQL Server.