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:

Basic SQL Server Encryption Hierarchy

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:

TDE Encryption Hierarchy

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.

Ed

Ed Leighton-Dick helps small and midsize businesses solve their most challenging database performance, resiliency, and data security issues at Kingfisher Data, the consulting firm he founded in 2014. He has taught thousands of people at over 200 events, including the world's largest Microsoft data platform conferences, and he has been a leader in the Microsoft data community since 2008. Microsoft has recognized Ed seven times as a Data Platform MVP for his expertise and service to the data community.

9 thoughts on “Replacing an expiring SQL Server encryption key

  1. Hi Ed,
    Just pointing out that a certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE. So, it is not a must to rotate certificate for TDE even when its expired.

    1. Technically, that is true. SQL Server does not strictly enforce expiration dates so that companies are not locked out of their data at the platform level. That doesn’t mean that you should ignore them, also. It just means that it’s up to you to enforce the dates through policy.

  2. Hey Ed, nice post.
    “The ALTER CERTIFICATE command has a clause that does just that – ADD ENCRYPTION BY.”
    Did you mean ALTER SYMMETRIC KEY? Cause that’s what’s actually in the code.

    1. Yup, that’s exactly what I meant – fixed. (How did I miss that one?!?) Thanks for catching that!

      1. Hey, also… do you know the mechanics to how SQL encrypts the data with both certs at once? It’s not logical encryption, it’s physical encryption, so is it safe to say that it takes up a lot more space during this time? But at the table-level, how does this work? Are the 2 copies of a table and an pointer, or what?

        1. Great question! At this level, it is actually closer to logical encryption. The data itself is always encrypted by only one key – the symmetric key. Even during rotation, that doesn’t change.

          The symmetric key is what is double-encrypted. I haven’t looked at the physical layout of it (I should – it would be an interesting post in itself), but logically speaking, a second copy of the encrypted symmetric key is stored that is encrypted by the new certificate. During the rotation, the symmetric key can be decrypted by either of the certificates. At the end, we drop the older encryptor from the symmetric key, and everything now goes through the new certificate. The data is never touched.

        2. Oh, and to answer your other question: Because the symmetric key is the only object that is re-encrypted, there is almost no difference in the size of the data during the rotation.

  3. What about always encrypted? Does it work the same way? And do you have to also push the new certificate out to all the clients?

    1. The concept is the same, but the mechanics are different. You’d rotate the column master key in that case, and yes, you’d need to push that key out to all clients. I’ll try to write a post on that in the near future.

Comments are closed.