This post goes out to both the administrators and security folks out there who may be looking after encrypted databases. If you have schedules in place to maintain your certificates for any accounts, web sites, SFTP, etc. then you may want to add the certificates protecting your databases into the mix too.
One element of the certificates which we haven’t yet touched on is that like these other types there’s also an expiry on the ones we create in SQL Sever. Here we’ll look at the impact that’ll have and how we can resolve this when the time comes.
There’s an expiry date?
Yep, each certificate will have one. By default a certificate will be created with an effective date at the point you created it, and it’ll be effective until one year after that (the time is recorded in UTC for reference). We can see these details in the certificate DMV which we’ve used before:
SELECT
name,
start_date,
expiry_date
FROM
sys.certificates;
If you’re looking to establish any best practices around your certificates this may be a DMV you want to interrogate and potentially look to alert for any upcoming certificate expiries. Alternatively you could check the expiry for certificates based on the databases they’re encrypting:
SELECT
db_name(database_id) [Database],
c.name [Certificate],
c.expiry_date
FROM
master.sys.dm_database_encryption_keys dek
INNER JOIN master.sys.certificates c
ON dek.encryptor_thumbprint = c.thumbprint;
This means that we’ve got encryption keys protected by our expired certificate. Eek. If we find some of these what do we need to do about it?
What do we need to do now
This is the immediate question which comes to mind when we find out that there’s an expiry date on the certificate used for your encryption, particularly when we see an expiry looming or passed. Fortunately as stated in the documentation, Microsoft have got us covered:
expiration isn’t enforced when the certificate is used for database encryption
There was much rejoicing.
Our database won’t just stop working or backing up, our data is still secured, it’ll be exactly as it was before the expiry. However the expiry is present for a reason and we’re here because we want to understand how we can keep these in a valid state over time.
Creating a new certificate
The first step for us is to create a new certificate which we can use to replace the old one. This time in our creation script we want to specify the effective range of the certificate so our creation will look slightly different:
USE master;
GO
CREATE CERTIFICATE FreshNewCertificate
WITH SUBJECT = 'All the shiny',
START_DATE = '2022-01-01',
EXPIRY_DATE = '2023-12-31';
GO
If the start or expiry dates aren’t specified then the certificate will default to starting immediately and expiring 12 months later. The issue with leaving these as defaults is that the 12 month window typically ends up being a Friday or Sunday – that’s from experience and finding out the hard way.
As we said, it isn’t quite as critical as a web server certificate for example where a site may start flagging up an invalid certificate, but it could indicate a gap in a security policy if you’re dealing with particularly sensitive data.
Rotating the certificate
The certificate isn’t what’s protecting our database. This is the responsibility of the Database Encryption Key as we covered in our introduction, however this is what is protected by our certificate. As a result it means that we can change the certificate protecting the key without altering the key itself.
This is referred to as ‘rotating’ the certificate.
By rotating only the certificate we’re still keeping the key protected but crucially we’re not changing the key so there’s no need to re-apply the encryption to the database. Changing the certificate can be achieved for our database as follows:
USE Blog;
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE FreshNewCertificate;
GO
There we have it. This operation should be near-instantaneous as its only decrypting and re-encrypting the key which we have stored for the database.
Rotating the encryption key
As an alternative to rotating the certificate it’s also possible to rotate the actual key protecting our data. This could be done in the same window as the certificate is rotated or any other time if required.
Unlike rotating the certificate this may not be a quick operation. By rotating the key which encrypts the data this will mean that the entire database needs to be decrypted (using the old key) and then re-encrypted (using the new key).
The key can be rotated (regenerated) as follows:
USE Blog;
GO
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256
GO
This could take a considerable amount of time depending on the volume of data you’ve got so you’d need to factor an appropriate maintenance window around this resource intensive operation.
Tidying up
Now that we’ve got the work done there are a couple of points to consider when tidying up the instance. Firstly you’ll want to ensure the new certificate is backed up and kept secure as we’ll need this moving forward – as we covered in this post.
Once that’s done you may also want to consider removing the old / expired certificate from your environment which we also covered and can be done with the following command:
USE master;
GO
DROP CERTIFICATE SomeOldCertificate;
GO
What we should note however is that backups created with the old certificate will still require that present on the environment if they come to be restored. You may therefore either want to leave the expired certificate in place for a defined period, or ensure the backup of it is retained for as long as required outside of SQL Server.
Wrap up
Here we’ve looked at an important part of the TDE process which is the ongoing maintenance of the certificates and keys. It’s certainly possible to implement the encryption and leave it in place due to the expiries not being enforced however by actively using the feature there’s likely a security requirement to have some sort of maintenance in place to keep these current.
Rotating a certificate intermittently isn’t a challenge to get into a maintenance routine, however if you’re looking to rotate keys then you’ll need to be factoring that in too.
Andy Brownsword 