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 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.

If you don’t have the backups of the certificate and private key from the old server, as well as the password used to encrypt the private key backup then you could be in a lot of trouble. There is one scenario where you have a way out. I’m going to assume you don’t have the possibility to recover your old server from a complete file system backup – if you do then you can do that and access all the keys you require. If the two following things are true though then you can still recover your database:

• You have a backup of the master database from the previous instance.
• The previous instance used a domain account as its service account.

The reason you are going to be okay is that all the objects in the SQL Server Encryption Hierarchy that sit above the Database Encryption Key (that exists in your TDE database) are stored in the master database. That includes the certificate and associated keys, the Database Master Key (DMK) and the Service Master Key (SMK). There are two copies of the SMK:

• One encrypted by the keys associated with the machine account.
• Once encrypted by the keys associated with the SQL Server service account.

The first copy is only going to be of any use to us if we can recover the old machine (and its account) directly from backups, but we’ve already ruled that out.

If the service account is a domain account though then we should be able to use it. The method is going to involve:

• Setting up a new SQL instance using the same service account as the old instance.
• Restore your backup of master from the old instance onto the new instance.
• Reboot your new server – that’s the whole server, not just SQL.
• Backup your certificate and private key – and don’t lose them this time!

My personal opinion is that it’s not the greatest of ideas to restore the master database from one instance onto a new one and expect everything to work okay. So, I’m only suggesting you use this so you can recover the certificate. Once you’ve got that, I would go back to the steps in the previous post on recovering your TDE protected database(s).

Let’s go into each of these steps in a little more detail.

Setting up a new SQL instance using the same service account as the old instance

What this obviously means is that your server must be on the same domain as the old server (or at least another domain that is trusted). You also must have the credentials for the service account.

You can’t fake this, for example setting up a new account on another domain called the same thing as the old one. The new account won’t have the same keys associated with it as the ones used to encrypt your SMK, so you will achieve nothing.

Restore your backup of master from the old instance onto the new instance

There are a lot of resources available on the internet that tell you how to do this in detail and will give you a number of methods you can use. In short you need to first stop your new SQL Server instance and then from a command prompt start it in single user mode with the following command:

sqlservr.exe -c -m -s {InstanceName}

Then you need to (again from a command line) issue the command to restore/overwrite the master database. First start SQLCMD with this command:

sqlcmd -s {InstanceName}

Then at the prompt that opens up within your command window you can execute the following SQL:

RESTORE DATABASE master FROM DISK = ‘C:\Test\master.bak’ WITH REPLACE;
GO

Reboot your new server – the whole server, not just SQL

If you restart the SQL Server service, rather than the machine itself, you can still go in and everything looks okay. You can even restore a TDE database from your old instance and you’ll find you can access the data.

Everything is not okay though, and if you tried to backup your certificate and private key you would get an error like the following:

Msg 15151, Level 16, State 1, Line 7
Cannot find the certificate ‘MyTDECert’, because it does not exist or you do not have permission.

The reason for this error is that the SMK isn’t in the correct state. The copy that is encrypted by the service account is fine, but the copy that is encrypted by the machine account is currently using the wrong machine account. You need to reboot the whole server to fix this, just restarting SQL doesn’t do it. On a full restart the SMK is retrieved from the copy encrypted by the service account, and then encrypted with the current machine account. That version then replaces the one using the wrong machine account.

Once that’s done the encryption hierarchy is fully fixed, and the certificate becomes accessible for a backup command.

Backup your certificate and private key – and don’t lose them this time

I’ve given the command to backup these a few times, but here it is again:

BACKUP CERTIFICATE MyTDECert  
TO FILE = 'C:\Test\MyTDECert.cer' 
WITH PRIVATE KEY  
( 
    FILE = 'C:\Test\MyTDECert_PrivateKeyFile.pvk', 
    ENCRYPTION BY PASSWORD = 'UseAStrongPasswordHereToo!£$7' 
); 
GO

You can now take those backup files and use them to restore the certificate and key to the SQL Server instance of your choice, and then restore the backups of your TDE protected database(s).

This has been mentioned a few times, but I’ll re-iterate once more as it is the most important issue to consider when managing TDE. Making sure you don’t lose these backups – or the password – is a vital consideration. If you’re responsible for setting up any form of encryption you need to think about the process that’s going to manage the objects used to protect your data. People move from one role to another, from one company to another, and often things tick along happily for many years before a failure happens. You need to be confident that come next year, or in five or ten years, whoever is responsible for the data will be able to recover it if the worst happens.

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.