Category: Blog

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One common task that you might need to handle if you work with encryption is dealing with a certificate. Whether this is for TDE, SSL, or something else, you will want to ensure that you know how to restore a certificate.

I’ve written about this before (restoring a certificate), but what about restoring an expired certificate? If you forget to replace one in TDE, does it still work?

Tl;dr yes.

Validating a Restore

I backed up an certificate from one instance that had expired. I used this in a customer demo a few years ago, and it was on my system. Here’s what I saw.

I ran the backup script shown to get the expired backup of the cert.

Next, I connected to a new instance and ran this.

CREATE CERTIFICATE FinanceCert

FROM FILE = N'C:\SQL\FinanceCert.cer'

WITH PRIVATE KEY

(
     FILE = N'c:\SQL\FinanceCert.pvk',
     DECRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You'

);

This completed, but I get the warning that this cert is expired. It’s a warning. SQL Server will still use the certificate to decrypt anything necessary, like a DEK for TDE.

This is a good reason to keep an eye on certificates, as someone might be worried about expired certs. They work, but some people (auditors) do not like to see these in use.

To change the certificate used, see Key Rotation in TDE.

SQLNewBlogger

Anything DR related is a good skill to showcase. Blog about your practicing different skills. The writing will cement the skills further in your mind and employers will appreciate you showing the way you handle things.