3

What is the best way to replace a soon expiring certificate? Downtime should be avoided. Databases size is 100GB.

4

If the certificate expires TDE will continue to work so don't panic there but you should still replace it to keep with your security policy. Because the certificate is used to encrypt the Database Encryption Key (DEK) and not the data, it is quite easy to change the certificate.

Create or import a new certificate and then use it to encrypt the DEK.

ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256 --Your choice of algorithm may be different
ENCRYPTION BY SERVER CERTIFICATE 'YourNewCertificate';

UPDATE

I made a mistake in my code above. It is not possible to alter the encryption algorithm and the certificate used in one statement. This was not required anyway so the following code is sufficient:

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE YourNewCertificate;
GO
  • There is a typo - it is ENCRYPTION – r0tt Oct 5 '16 at 12:16
  • @JamesAnderson - I wanted to play around with regenerating my database encryption key using your example above. That syntax doesn't appear to be supported under Sql 2016. The word ENCRYPTION is red-line-squiggled. I realize that Always Encrypted is the new way of doing things, but I can't find where the syntax you used was deprecated anywhere, yet, I get a syntax error on my 2016 instance. Is there something I'm missing? – Scott Hodgin Oct 24 '16 at 12:39
  • @ScottHodgin which encryption algorithm are you using? – James Anderson Oct 24 '16 at 12:54
  • @JamesAnderson - I tried to use your example just as it was written - so, AES_256. I literally copied your example to a Sql 2016 instance and immediately got the highlighted syntax error. Thinking it my be the quotes around 'YourNewCertificate', I took off the quotes - that didn't seem to make a difference. – Scott Hodgin Oct 24 '16 at 12:58
  • @ScottHodgin I have updated the answer. I was trying to do too much in one statement and not testing it was possible. – James Anderson Oct 24 '16 at 13:14
2

An expired certificate that is used in TDE should not cause you any problems. From this link:

A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE.

We have expired TDE certificates on our servers, and haven't encountered any problems. If you copy that TDE certification to another server (so you can restore TDE databases), you'll still get the annoying 'The certificate has expired' message, but encryption/decryption will still work fine.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.