Recently at an IndyPASS meeting I facilitated a general question and answer session preceding our regularly scheduled feature presentation for the month. There was a lot of great questions covering many aspects of SQL Server; but I was most excited to hear some great questions in regard to Transparent Data Encryption (TDE). In the next few blog entries I hope to cover some of the items that were raised. This entry focuses on the Master Key that is used for TDE.

If you have an instance of SQL Server that does not contain databases that have TDE enabled, the first step in the process of the implementation of TDE is to create a Database Master Key (DMK) for the MASTER database. An example of this syntax is below:

      USE MASTER
      CREATE MASTER KEY
      ENCRYPTION BY PASSWORD = 'MySampleStrongP@ssword2008'

In general when a DMK is created for a database it would be created using the database in which we intend to apply the encryption (target database). In the case of TDE we do not create the DMK in our target database, but do so directly in the MASTER database. This placement of the DMK in the MASTER database facilitates the server's handling of the encryption/decryption process automatically without the additional code that cell-level encryption requires.

Only one DMK can be created for any given database. If the above commands are executed against the MASTER database that already has a DMK defined an error will be returned. To determine if a DMK has been previously created you can execute the sys.symmetric_keys catalog view on the MASTER database. The record with the name column value of "##MS_DatabaseMasterKey##" is your DMK.

The creation of a DMK in the MASTER database does not imply that the MASTER database has been encrypted. In addition, the creation of a DMK in the MASTER database does not imply that the entire instance has been encrypted. Creating a DMK simply produces an symmetric key that is utilized for the encryption/decryption process.

As you proceed with the implementation of TDE keep the following items in mind:

• When the database backups occur, the DMK, or any other key, is not included in the backup. You must backup all keys individually as a separate step to your plan.
  
• The DMK is protected by the Service Master Key (SMK). The SMK is created at the time the instance is created. If the SMK is changed or lost, recovery of the DMK to unlock the encrypted database will not be a pleasant experience and will likely result in an unrecoverable database.
  
• All databases in a single instance that have TDE implemented will utilize the single DMK that was generated in the MASTER database.

Certainly as you read this information additional questions will arise. In the following blog entries, I will cover other aspects of TDE that will hopefully answer many questions. Feel free to comment on this blog with your questions or contact me directly if you have more detailed or specific questions in regard to DMK or TDE.