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;
CREATE CERTIFICATE MyTDECert_with_longevity
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:
USE TestTDE;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE MyTDECert_with_longevity;
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 encryption in SQL Server. If you want it all in one go you can buy my book:
