Rotating a Certificate Used by TDE

In terms of encryption, Key Rotation is the process of replacing your encryption keys on a periodic basis. This is considered good practice and is required by many security certifications.

In practice, if you had to rotate/replace the key that is used to encrypt your data then that would be an intensive activity requiring all your data to be decrypted with the old key before being replaced with the new. This could also create a vulnerability where data sits in an unencrypted state during the process.

This is a reason why many forms of encryption maintain a separation between the actual key used to protect the data and a second key used to protect the encryption key. In terms of TDE, we have the DEK which is protected by the certificate and associated key pair. In general with TDE, when we talk about rotation, we just rotate the certificate. This means the activity can be done without changing the underlying DEK, so the data does not need to be decrypted and re-encrypted. Thus, there is minimal overhead and the process is quick and secure

Many sources will tell you that this process DOES decrypt and re-encrypt your data. This is not correct and can be demonstrated with a simple test.

The DEK used by TDE is held securely. It is only stored encrypted in the database, and we never see the unencrypted value of the key. The certificate however is a little more public and we must backup it and the private key outside of the database. This makes it a little more vulnerable and so it makes sense that we would want to rotate it periodically. Due to that consideration certificates have an expiry date. This date is a reminder to us that, as a good practice, we should create a new certificate and use that going forward before the existing one expires.

TDE doesn’t stop working if the certificate expires, it is up to you to monitor your certificates and replace them when they come to the end of their life. One option is to monitor them using Policy Based Management.

Creating a New Certificate

If we query  the sys.certificates view with the following SQL we can find our TDE certificate and examine the expiry date:

USE master;
SELECT name, subject, expiry_date
FROM sys.certificates
WHERE name = 'MyTDECert';

Here is the output:

I didn’t specify an expiry date for this certificate when I created it, so it was automatically given one that was a year in the future. Let’s create a new certificate, and this time we’ll specify a longer expiry. Then we will rotate the encryption to use that one. Here’s the code to create a new certificate and specify the expiry date:

USE master;
WITH SUBJECT = 'Certificate used for TDE in the TestTDE database for years to come',
EXPIRY_DATE = '20251231';

Let’s have a look at the values that has in the sys.certificates view using the same query from above:

We’re now ready to “rotate” from the old certificate to the new one.

Rotating the Certificate

Rotation is the process of moving from the old certificate to the new one. In this case all that happens is that the encrypted value of the database encryption key (stored in our TDE protected database) is decrypted with the old certificate, re-encrypted with the new certificate, and that new encrypted value is stored in the database, overwriting the old one.

The key value itself hasn’t changed, just the object protecting it, and as such we can still read/write data from the database without any change to the encryption of the underlying data. It is a simple command to rotate the certificate:


The operation is almost instantaneous, shouldn’t require any database down time, or create additional overhead on your server. Though in production I’d still do it when things are quiet just in case anything goes wrong!

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.

3 thoughts on “Rotating a Certificate Used by TDE

  1. “Many sources will tell you that this process DOES decrypt and re-encrypt your data”. I think the misconception came from the incorrectly rotating the certificate. If you issue command with an argument REGENERATE WITH ALGORITHM (as provided by some sources) it will decrypt – encrypt. Example would be:

    USE TestTDE;

    But if you read and follow the book (or this post) you can skip decrypt – encrypt part by omitting “REGENERATE WITH ALGORITHM”. I tested certificate rotation with VLDB and it went successfully without decrypting – encrypting part.

Leave a Reply