The Certificate specified for backup encryption has expired

Question

The Certificate specified for backup encryption has expired

terry999

SSCertifiable

Points: 5036
More actions
October 11, 2023 at 2:10 pm

#4305918
Hi
Trying to backup Database encrypting the bak file with a certificate.
The certificate has expired by looking at
```
SELECT * FROM sys.certificates
```
the certificate expired almost 3 years ago. It has been running this back using the expired certificate for almost 3 years!
I read on numerous posts SQL server ignores certificate expiry_date.
Before I make a new certificate, I want to know what changed to cause SQL Server backup to take note of the expiry date
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

TDE certificates don't expire (from the docs for CREATE CERTIFICATE - EXPIRY_DATE entry at https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-ver15): "However, expiration is not enforced when the certificate is used for database encryption or Always Encrypted."

It's just a warning. You may set when a certificate expires when you create it. In SQL Server, this defaults to one year from when it was created. This warning just lets you know that you passed the date set in the certificate. It will work fine, but maybe 4 years is a bit long to go between cert rotations.

Eddie Wuerch
MCM: SQL

terry999 SSCertifiable Points: 5036 More actions · Answer 2

Thanks Eddie,

Sorry, I gave Wrong info. There are 2 certificates with very similar names one in Master DB and one in a user DB. I mixed them up

looking at

SELECT * FROM MASTER.sys.certificates

The certificate the backup is using is from the master Db. This expired on the day the backup stopped working.

So contrary to what I posted, it has NOT been working for 3 years with an expired certificate but rather Backup has respected the expiry date

If I try executing backup statement with the encryption option I get the error message

" The Certificate specified for backup encryption has expired" Looks like it does respect the expiry date for backup encryption.

However every thing I read, said expiry date was ignored.

Thanks,

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

It should be ignored. This is because even if expired, in a DR situation, you need to be able to use old certs.

Ken McKelvey SSCoach Points: 18856 More actions · Answer 4

What I remember from testing a few years ago:

A backup will not work with an expired certificate.
A restore will work with an expired certificate.

ie You should should leave all the expired backup certificates in place just in case you need to restore from a long term backup.

terry999 SSCertifiable Points: 5036 More actions · Answer 5

Thanks All

@ken can confirm (1) backup doesn't work with expired cert.

Cannot test restore because these backups aren't used for DR but as a way of transferring data between 2 servers, old versions aren't kept