Guidelines on Managing Certificates used in Transparent Data Encryption


Summary


Transparent data encryption (TDE) is a new encryption feature introduced in Microsoft SQL Server 2008. It is designed to provide protection for the entire database at rest without affecting existing applications. TDE uses Key Management hierarchy for encryption.

At the root of encryption tree is the Windows Data Protection API (DPAPI), which secures the key hierarchy at the machine level and is used to protect the service master key (SMK) for the database server instance. The SMK protects the database master key (DMK), which is stored at the user database level and which in turn protects certificates and asymmetric keys. These in turn protect symmetric keys, which protect the data. TDE uses a similar hierarchy down to the certificate. When you use TDE, the DMK and certificate must be stored in the master database. A new key, used only for TDE and referred to as the database encryption key (DEK), is created and stored in the user database. For details about the Encryption Hierarchy, please refer to http://msdn.microsoft.com/en-us/library/cc278098.aspx#_Toc189384674

More Information


Certificates are commonly used in Transparent Data Encryption to protect the Database Encryption Key  (DEK). The Certificate must be created in the master database. The Dynamic management view sys.dm_database_encryption_keys provides information about the encryption state of a database and its associated database encryption keys.

Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, when you restore from these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available.

The backup of the Certificate and Database Master Key is also needed when the database is enabled for TDE and is used in Log Shipping or Database Mirroring.

It is also important to remember that the Certificate used to protect the Database Encryption Key should never be dropped from the Master database. Doing so will cause the encrypted database to become inaccessible. 

A Warning message similar to the following is raised after executing the "Create Database Encryption Key" if the certificate used in the command has not been backed up already.

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

The following query can be used to identify the Certificates used in TDE that have not been backed up from the time it was created.

SELECT pvt_key_last_backup_date,
       Db_name(dek.database_id) AS encrypteddatabase ,c.name AS Certificate_Name 
FROM   sys.certificates c
       INNER JOIN sys.dm_database_encryption_keys dek
         ON c.thumbprint = dek.encryptor_thumbprint 

If the Column pvt_key_last_backup_date is NULL, then the database correpsonding to that row has been enabled for TDE but the Certificate used to protect it's DEK has not been backed up. For more information on backing up a Certificate please refer to BACKUP CERTIFICATE topic in SQL Server Books Online.

For more information on Transparent Data Encryption feature, please refer to the article Database Encryption in SQL Server 2008 Enterprise Edition

For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:

 

 Rule software

 Rule title

 Rule description

 Product versions against which the rule is evaluated

SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)

Transparent Data Encryption Certificate

The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides rule to detect when a database that has been enabled for Transparent Data Encryption and the Certificate used to protect its Database Encryption Key has not been backed up from the time it was created.

If you run the BPA tool and encounter warning with the title of Engine - Transparent database encryption certificate, we detected that a database has been enabled for TDE but the certificate used to protect its Database Encryption Key has not been backed up.

SQL Server 2008
SQL Server 2008 R2

SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)

Transparent Data Encryption Certificate

The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides rule to detect   when the certificates used for encrypting the database encryption key has not been backed up with the private key.

If you run the BPA tool and encounter warning with the title of Engine - Transparent database encryption certificate, we detected that a database has been enabled for TDE but the certificate used to protect its Database Encryption Key has not been backed up.

 SQL Server 2012