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 |