Rotating Expired SQL Server TDE Certificates

In general, SQL Server TDE certificates will expire at some point.

In SQL Server, if we have setup TDE using SQL certificates we may have reached a point in time when the cert has expired.

Maybe we haven’t.

Maybe we don’t even know.

We can check quickly to see if our TDE certs are close to expiring or perhaps already have pretty easily with a quick query to list the certificates with expiration dates used in each of our TDE encrypted DBs:

SELECT D.name
     , DEK.encryptor_type
     , C.name
     , C.expiry_date
FROM   sys.dm_database_encryption_keys AS DEK
       LEFT JOIN sys.certificates AS C ON DEK.encryptor_thumbprint = C.thumbprint
       INNER JOIN sys.databases AS D ON D.database_id = DEK.database_id
WHERE D.is_encrypted = 1;

We have expired or expiring SQL TDE certificates! What now?

Well, the first thing we do is not panic. Even if our TDE certificate expires it won’t cause any issues. The SQL Server will continue to work normally. Even if we restore the DB elsewhere using the expired certificate we will just get a warning that the certificate is expired.

A warning is nice, and the system still working let’s us breathe a little easier, but we know that an updated certificate is a much better thing to have. In fact, setting up a regular key rotation schedule is even better and a recommended practice.

Let’s quickly rotate our SQL TDE certificates!

1 – Create a new SQL TDE certificate.

Use master;
Go

CREATE CERTIFICATE TDECERT2019
WITH SUBJECT = '2019 TDE Certificate'
EXPIRY_DATE = '20191231';
GO

The creation of a new SQL TDE certificate is easy. Some people like to obscure the name so it isn’t obvious, and we can do whatever we want. In an effort to remember to rotate the keys in the future we will set an expiration date on this new certificate.

2 – Backup the new SQL TDE certificate so we can create it on other systems as needed.

BACKUP CERTIFICATE TDECert2019
TO FILE = 'c:\temp\TDECERT2019.cer'
WITH PRIVATE KEY (File = 'c:\temp\TDECERT2019.pvk',
ENCRYPTION BY PASSWORD = 'S0m3th1ngStr0ng');
GO

Make sure we don’t store our certificate’s backups on the server, or with the database backups. The path above is just for the code sample. I find it easier when working with multiple servers or nodes in an availability group to but the backups somewhere on the network that the servers can access, but is still secured.

3 – Create the same SQL TDE certificate on your other servers, if needed. If using Availability groups, install the certificate on all nodes.

CREATE CERTIFICATE TDECERT2019
FROM FILE = 'c:\temp\TDECERT2019.cer'
WITH PRIVATE KEY (FILE = 'c:\temp\TDECERT2019.pvk',
DECRYPTION BY PASSWORD = 'S0m3th1ngStr0ng');
GO

We don’t restore a certificate from the backups made, but create new ones. We can also change the name to anything we like, but I try to keep it consistent between servers, so I don’t get too confused when looking up my certs.

3 – Change encryption key for your databases – aka Rotate the SQL TDE certificate

USE TESTDB
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TdeCert2019;
GO

This is the process to rotate the keys and certificates used for TDE encryption. We tell the DB to encrypt using the new key. Behind the scenes, the server starts re-encrypting the data without having to decrypt everything first. Just like when TDE is initialized, the process works its way through and rotates the encryption key.

I recommend keeping the old, expired SQL TDE certificates on the server for a while. If you have to restore an older backup you may still need the old SQL TDE certificate in place. Once you have reached a point that you no longer need the old SQL TDE certificates feel free to go ahead and remove them.

What if we have an availability group with multiple nodes?

That’s easy.

Since we already restored the certificates to the other nodes. When we issued the command to alter the primary node DB to encrypt with a new certificate the command is sent to the other nodes, and the rotation will happen automatically on them.