Transparent Data Encryption (TDE) has been around for a long time. It first appeared in SQL Server 2008, and after a rocky start with some bugs, it has become a regularly used feature for many organizations. While not perfect, it does provide some protection and auditors like to see physical protection features being used. It's the default in Azure SQL Databases, and Microsoft has enhanced it to use Azure Key Vault and you can bring your own key for use in securing the Database Encryption Key (DEK).

One of the concerns many people have with TDE concerns key management, which is really the hardest part of any encryption scheme. Keys will get broken and certificates expire. This article looks at the process of rotating your certificates in conjunction with TDE.

The TDE Encryption Hierarchy

In TDE, there is a set of keys that protect the data and other keys. These are arranged in a hierarchy, with each key protecting the one below it in the hierarchy. The last key then protects the data itself. In the case of TDE, our hierachy looks like this:

The first level of protection is the Windows DPAPI, which protects the Service Master Key (SMK). This then protects a Database Master Key (DMK) in the master database, which protects the private key of the certificate created for TDE. The certificate protects the Database Encryption Key (DEK), which in turn encrypts and decrypts the data. The DEK is a symmetric key and doesn't change, which is the case for the SMK and DMK. The certificate, however, is the part that expires and needs rotation.

We won't discuss how to enable TDE, but we have lots of articles at SQLServerCentral on different scenarios.

Rotating a Certificate

When we enable TDE, we first create the DEK with a statement like this one:

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert2019;
GO

This statement uses a previously created certificate to encrypt the DEK. The private key of the certificate is automatically protected by the DMK.

Note: If the certificate was created with password encryption, you will need to password to use the certificate.

Now, let's assume it's almost a year later. I realize my certificate is expiring soon, and I want to rotate the certificate. The first thing I need to do is create a new certificate.

CREATE CERTIFICATE TDE_Cert2020
WITH SUBJECT = 'TDE DEK Certificate for 2020'
, EXPIRY_DATE = '20200901';
GO

Now, I can see that the existing certificate is protecting my DEK with this query:

USE master
GO
SELECT
          DatabaseName    = DB_NAME(dek.database_id),
          dek.encryption_state,
          CertificateName = cer.name,
          cer.expiry_date
 FROM
          sys.dm_database_encryption_keys AS dek
     JOIN sys.certificates                AS cer
         ON dek.encryptor_thumbprint = cer.thumbprint;

This gives me the following result in my lab.

The key rotation is accomplished by moving the encryption of the DEK from the TDE_Cert2019 to the new certificate, TDE_Cert2020. I can do this with this code:

USE TDE_Primer
GO
ALTER DATABASE ENCRYPTION KEY
 ENCRYPTION BY SERVER CERTIFICATE TDE_Cert2020

Once I do this, if I re-run my query, I see that the new certificate is protecting my database.

Note, I do get this message (reformatted for this page) if I haven't backed up the new certificate. If you get this, back up your certificate immediately.

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.

Handling Database Restores

Now that I've rotated the certificate, I can delete the old one, correct?

Yes, and no, or in DBA terms, it depends. The new certificate will only allow restores of databases backed up since the key rotation. Meaning, if I rotate the certificates on Tuesday afternoon, and then I need to restore Monday night's backup, I'd need the older certificate on the system. If I needed a restore on Wednesday, I'd use the new certificate.

Note: these certificates remain in SQL Server, even if expired. They must be manually deleted to be removed.

While you might think keeping the old certificates on the instance will be enough, it isn't. If you lose need to recover a database on another instance because of a DR situation, it's possible that you might not have the certificate. A backup of the master database will have the cert, but in a disaster, it's possible you might not restore master. In fact it's likely that you don't want to restore master. Or you might have some other issue. I would always want a separate backup of my certificates, preferably with the backup of the database. After all, this is still protected with the password in the private key.

In general, the advice is that as long as you have a backup file that needs a particular certificate, you ought to have a backup of the certificate (pbulic and private keys). Either in a backup of master or as a separate set of files (recommended). It is also recommended that you keep the backup of the certificate separate from the database backups.

Summary

The process of rotating the certificates is very easy. Create a certificate and run an ALTER command. The harder part, which is always the hard part, is the key management. The certificate backup is usually protected with a password on the private key. These are easy to track in a password manager, and should be maintained until you are positive all backups using a previous certificate have been deleted.

TDE is a useful feature, at least from the business standpoint of protecting physical files and proving this has been done for auditors. Be sure you know how to change keys, as this is something that is becoming a checklist item in audits, as it should. Practice knowing which server certificate(s) you need and ensure you can restore an older one if necessary.