This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.
Transparent Data Encryption uses certificates in its architecture for protecting your data while at rest. One attribute of a certificate is they have an expiration date. Certificates expire for a couple reasons, but the main reason is to enforce security. When a website certificate expires it forces the website owners to get a new certificate by proving they are who they say they are with a trusted third party.
SQL Server certificates that are used for TDE also have an expiration date, but these dates are only checked when you are creating a self-signed certificate using the “CREATE CERTIFICATE” T-SQL command. If you don’t supply an expiration date when creating your certificate SQL Server will assign one that is 1 year into the future.
Under normal circumstances, SQL Server will also prevent you from creating a new certificate with an expiration date that is in the past. However, if you really wanted to, you could play with the system clock on your server and set it to a date in the past to create a certificate with an expiration date in the past. I do not recommend doing this but if you must, please make sure no one else is using the server and don’t forget to reset the clock when you are finished.
After your certificate is created on the system SQL Server does not check the expiration date again when using that certificate for TDE. What this mean for you, and most other companies, is you are likely using an expired certificate but SQL Server doesn’t really care.
To check the expiration date of certificates on your server you can use the following query:
select name, start_date, expiry_date from sys.certificates;
To see if you are using an expired certificate in a TDE configuration for one of your databases you can use the following query:
select d.name, c.name, c.start_date, c.expiry_date, getdate() as today from sys.dm_database_encryption_keys dek join sys.certificates c on dek.encryptor_thumbprint = c.thumbprint join sys.databases d on dek.database_id = d.database_id
What does it mean if you have a database using an expired certificate? Nothing really.
Does it make your system less secure? That depends on who you ask.
From a pure security perspective, you should rotate your certificates at a predefined cadence. That way if someone was able to exfiltrate your current certificate and keys, the certificate they stole would eventually become worthless. Most companies don’t rotate their certificates though because it adds complexity to a recovery scenario and complexity can create confusion during an emergency which can slow things down or cause larger issues.
