locked
TDE Certificate expires RRS feed

  • Question

  • I tested TDE certificate expiration on SQL Server 2012.

    After expiration i dropped old cert and created new one and restored backup with new one, and everything is successfull.

    However when we remove the old cert from the DB, backups made prior to new certifiate will not restore withour old certificate.

    What is the logic behind this? I am getting confusion. How the old database know about old certificate detail, because here are we are droping old cert (Expired)?

    Monday, June 27, 2016 2:29 PM

Answers

  • Yes, you can drop a TDE enabled database.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    • Marked as answer by VijayKSQL Wednesday, June 29, 2016 4:16 PM
    Wednesday, June 29, 2016 4:04 PM

All replies

  • this information is stored when the backup was taken

    select

    encryptor_thumbprint,key_algorithm,encryptor_type,*frommsdb.dbo.backupset


    Hope it Helps!!

    Monday, June 27, 2016 2:59 PM

  • You mean the New Certifcate gets old cert information from msdb.dbo.backupset and stored in new cert while creating new cert?

    I am correct?




    Monday, June 27, 2016 3:22 PM
  • I tested TDE certificate expiration on SQL Server 2012.

    Then you've found out that expiration dates don't matter to TDE, it'll continue to work just fine. However, intermediate key rotation (not the DEK, but higher) should be done periodically.

    However when we remove the old cert from the DB, backups made prior to new certificate will not restore with our old certificate.

    Correct, because it can't find the old certificate to decrypt the data with. This is why backups of the certificate are important and are *still* required to have until all of the older backups using that certificate are aged out.

    What is the logic behind this? I am getting confusion. How the old database know about old certificate detail, because here are we are droping old cert (Expired)?

    The logic is, it was backed up with the old cert and then looks for that cert again. That's all it knows about. If that cert isn't there, the database isn't going to be usable.

    You took a backup, create the new cert, then dropped the old cert. The backup **ONLY** knows about the old cert. The new cert will start to be used once data rotation is complete. The database holds a reference to the current DEK and the previous DEK so that DEK rotation can occur (informational only).

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Monday, June 27, 2016 3:39 PM
  • HI Sean,

    Thank you very much.

    But could you please explain little bit about the below sentence. I can't understood properly.

    "The new cert will start to be used once data rotation is complete. The database holds a reference to the current DEK and the previous DEK so that DEK rotation can occur (informational only)."

    Monday, June 27, 2016 3:59 PM
  • I just wanted (for completeness of the answer) to let you know that the certificate signature of the current and the previous DEK will be saved in the database (not viewable) configuration. This does not change anything I wrote above or how TDE works, at all.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Wednesday, June 29, 2016 4:24 AM
  • IS it possible to drop TDE enabled database?

    Becauese i tested am able to offline and delete database successfully.

    I red some where it is not possible.

    • Edited by VijayKSQL Wednesday, June 29, 2016 1:56 PM add
    Wednesday, June 29, 2016 1:53 PM
  • Yes, you can drop a TDE enabled database.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    • Marked as answer by VijayKSQL Wednesday, June 29, 2016 4:16 PM
    Wednesday, June 29, 2016 4:04 PM