SQL Server Blog

What the SQL Server TDE Certificate Expiration Date Does (and does not) Mean

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;
sql server 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
expired sql server certificates

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.

Article by Mike Lynn
Mike got his start in computers in college after taking a class about Excel for an accounting major. After that class, he started taking more computer science courses and decided to change majors. After graduation, Mike took a job in Little Rock, AR as a Developer / DBA. The job was working with a .net 1.1 application, a SQL Server 2000 backend, and Microsoft Access/SSRS as the reporting tools. Mike quickly learned how much he enjoyed working with databases and has never looked back. The parts he enjoys most are helping people solve their pain points with data, whether that is helping with a performance problem or designing a new system to solve a particular need. He also enjoys automating work because it allows the person who was doing the work more time to focus on new business problems. Mike has worked with every major version of SQL Server since 2000, with the majority of his time spent on the 2008 R2, 2014, and 2016 releases of SQL Server.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This