SSISDB – Certificates expired

Posted on

Question :

I was doing an audit of certificates in our environments to find those that have either expired or are expiring in 30 days. I used the following query:

SELECT  *
FROM    sys.certificates
WHERE   expiry_date < DATEADD(DAY,30,GETDATE());

I noticed in SSISDB that there are a large number of expired certificates:

enter image description here

What are these certificates used for, is it an issue that they have expired? I have not heard any complaints from our development team that SSIS isn’t working as expected.

Answer :

All certificates have an expiry_date, but for most uses of certificates in SQL Server, the expiry is not checked. A certificate that protects a symmetric key used for data encryption in a database (which is what I think these are), can be used indefinitely. IE SQL Server doesn’t check the expiry_date when you run OPEN SYMMETRIC KEY DECRYPTION BY CERTIFICATE.

From the docs:

EXPIRY_DATE = ‘datetime’

Is the date on which the certificate expires.
If not specified, EXPIRY_DATE is set to a date one year after
START_DATE. EXPIRY_DATE is in UTC time and can be specified in any
format that can be converted to a date and time. SQL Server Service
Broker checks the expiration date. Backup with Encryption using
certificates also checks the expiration date and will not allow a new
backup to be created with an expired certificate, but will allow
restores with an expired certificate. However, expiration is not
enforced when the certificate is used for database encryption or
Always Encrypted.

CREATE CERTIFICATE

Leave a Reply

Your email address will not be published.