Replacing an expiring SQL Server encryption key

So you’re using encryption in SQL Server, but you’ve discovered that the expiration date of a certificate is expiring. What do you do? The process of safely replacing the certificate is called rotating the encryption key. It’s important to do, and SQL Server makes it a simple, quick process.

First, a little background

The most common model of encryption in SQL Server looks something like this:

Each layer is encrypted by the one above it – the data is encrypted by the symmetric key, the symmetric key by the certificate, and so on. It’s a very secure model. Microsoft even uses it for SQL Server’s internal needs.

Here’s the thing: Any lock can be broken given enough time. To ensure that your encryption is as secure as possible, you need to make it a moving target. You can do that by rotating your keys, which is just the process of re-encrypting your data with a different key.

Think back to the model I showed above. It would take a long time to re-encrypt all of your data with the symmetric key, right? For most companies, it was hard enough to encrypt it in the first place; we don’t want to do that again if we don’t have to. However, we can easily re-encrypt that symmetric key – that’s only one value. Since the symmetric key doesn’t change, we don’t have to re-encrypt the data itself, but we make it a moving target. Mission accomplished – with a lot less work.

How to do it

So, now that we know what we need to rotate, how do we do it?

First, obtain a new certificate. SQL Server has the capability to generate its own certificates. For many purposes, that’s enough. However, if your company has to comply with auditing or regulatory requirements, you may need to obtain the new certificate from an outside source. Often, this is a third-party certificate authority. Some companies use a system called Encryption Key Management (EKM, also known as a Hardware Security Module, or HSA, after the device used to store the master key). (Obtaining an external certificate is a subject for an upcoming post.)

However you obtained the certificate, install it. Make sure to back it up securely, including the private key.

Next, add the new certificate to the symmetric key. The ALTER SYMMETRIC KEY command has a clause that does just that – ADD ENCRYPTION BY.

Finally, remove the old certificate from the symmetric key. You’ll again use ALTER SYMMETRIC KEY, but this time with the DROP ENCRYPTION BY clause.

Here’s an example of a complete rotation script:

CREATE CERTIFICATE newCertificate
    WITH SUBJECT = 'My new encryption certificate',
         EXPIRY_DATE = '12/31/2018';

BACKUP CERTIFICATE newCertificate
    TO FILE = 'Z:\safeBackupLocation\newCertificate.cer'
    WITH PRIVATE KEY (FILE = 'Z:\safeBackupLocation\newCertificate.pvk',
                      ENCRYPTION BY PASSWORD = 'str0ngPa$$w0rd');

ALTER SYMMETRIC KEY mySymmetricKey
    ADD ENCRYPTION BY CERTIFICATE newCertificate;

ALTER SYMMETRIC KEY mySymmetricKey
    DROP ENCRYPTION BY CERTIFICATE oldCertificate;
GO

That’s it! It’s a straightforward process, and because it only affects the certificate, it’s very fast. Best of all, since the symmetric key is encrypted by both certificates simultaneously during the transition, the data is never inaccessible – and more importantly, never exposed.

Key Rotation with Transparent Data Encryption

If the expiring certificate is being used for Transparent Data Encryption (TDE), the process is even easier. The encryption hierarchy for TDE looks very similar:

In this diagram, the server certificate is just a certificate stored in the master database, and the database encryption key is just a specialized symmetric key.

To rotate the certificate for TDE, add the new certificate as above, then execute the command ALTER DATABASE ENCRYPTION KEY with the ENCRYPTION BY SERVER CERTIFICATE clause:

ALTER DATABASE ENCRYPTION KEY
    ENCRYPTION BY SERVER CERTIFICATE newCertificate;

SQL Server re-encrypts the database encryption key with the new certificate, and drops the encryption by the old certificate when it’s finished. And as before, the data itself isn’t re-encrypted, so the process finishes almost immediately.

How often?

Rotating your keys is an important practice for any kind of encryption. At a minimum, I recommend doing it every two years. However, some regulations require more frequent rotation, as do some company policies. Frequencies as low as six months are not uncommon; I’ve even seen some cases where rotation happens every three months. Because it is a quick process, frequent rotation is not a big deal.

One caution: Always keep at least one backup copy of every certificate you use. If you ever need to restore a database that used encryption, you’ll need the certificate that was in effect at the time the backup was created. Make a habit of creating a certificate backup immediately after creating it in SQL Server. Store the backup in a safe place; also keep a copy of the passphrase you use to encrypt the certificate backup, preferably in a different safe place for security. Retain these forever, or until the last database backup that may possibly use them has been purged.

Conclusion

Replacing your expiring certificates is a quick, straightforward process, and it’s an important part of maintaining the security of your encryption. Make it a part of your regular database maintenance.