Removing TDE from a Database: Level 4 of the Stairway to TDE

  • Hi Steve,

    something has gone wrong here, there is no link back to the article from this discussion thread as it is as stated, not currently available on the site, but why is the discussion available?



  • A scheduling mishap. This thread shouldn't be visible either.

  • Comments posted to this topic are about the item Removing TDE from a Database: Level 4 of the Stairway to TDE

    Vishnu Gupthan

  • I love articles like this, explosing the hidden gotcha.  Thanks for sharing.

  • BTW, I found another "gotcha". After turning off encryption stats off for the only encrypted database on the instance, encryption_state was still 1, and tempdb was showing up as encrypted. The drop DEK statement generated an error. I bounced the instance, no change. Finally I dropped the formerly encrypted database, and still got the error trying to drop the DEK. Finall I just tried removing the certificate, and it worked. The DEK was gone as well. Apparently there's also a known bug in updating the DMV for tempdb as well. It's described as "benign", but it certainly doesn't seem so when trying to back out the encryption, such as in this scenario.

  • You also need to bear in mind that even after decrypting the database and dropping the encryption key and cert it is still possible part of the transaction log is still encrypted, so you may still need the cert for an extended time until the log recycles.

    In sql 2019 onwards there is an extra column in the DMV "sys.dm_db_log_info" to track this


    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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