SQL Server Backup Certificate – Does Expiration Date Matter?

backupcertificatesql server

If I have a cert in SQL Server, used for backups:

  1. Can backups still be created after cert expires?
  2. Can restore still happen after cert expires?

What is the point of having these certs expire? (other than to expose me to backups failing due to expired cert…)

Best Answer

I have tested this on SQL Server 2016 (Developer Edition, though that shouldn't matter), and can confirm:

  1. No, after the Certificate expires, using it to encrypt a backup will cause the following error:

    Msg 3096, Level 16, State 1, Line xx
    The Certificate specified for backup encryption has expired.
    Msg 3013, Level 16, State 1, Line xx
    BACKUP DATABASE is terminating abnormally.

  2. Yes, after a Certificate expires, backups encrypted from that Certificate can still be restored. BUT, you still need to have the Certificate loaded into master in order to do the RESTORE.

Just create a new Certificate, and be sure to specify the EXPIRY_DATE = 'utc_date' clause, else the expiration date will default to 1 year from the moment it was created.

As to why Certificates have expiration dates, that is probably to give more control over the security mechanism, similar to how you can set a password policy for Logins to expire. Certificates can be backed-up and used in multiple places, so it can help ensure that you don't have a piece of information sitting out there, somewhere, forever that can be used to compromise your system.

Related Question