TDE: Under The Hood With Disabling TDE

It is indeed that time of the year when the love of American Football can be reflected through using its terms in metaphors for everything from life, politics, relationships, and in this case… Transparent Database Encryption (TDE).

Life is not series of running plays
After careful consideration, evaluation, and research the decision to implement TDE may be reached. You, as the DBA, swiftly execute the commands to create the appropriate keys and certificates. The TDE feature has been enabled and the database is now protected.

A few weeks later the decision to remove TDE has been mysteriously reached and communicated to you… or immediately after implementing TDE and closer consideration the database that was to be encrypted was not the one in which it has been implemented. Regardless of the cause, it is time to reverse the actions that were taken to implement TDE.

Call in the back up
A life-saving practice is to perform a database back up before making any changes to database properties and schema. This practice includes the implementation of TDE. If the decision to reverse the implementation of TDE occurs immediately after its implementation and before any other changes have occurred, including data updates and inserts, the following steps can be followed to cleanly revert your database to its pre-TDE state:

  1. Restore the pre-TDE back up with the RESTORE DATABASE command.
  2. Drop the server certificate used for TDE in the MASTER database with the DROP CERTIFICATE command.*
  3. Drop the Database Master Key (DMK) in the MASTER database with the DROP MASTER KEY command.*
  4. Restart the instance in which these actions have occurred. If there are not any other databases in the instance with TDE enabled, this action will regenerate the TEMPDB database in an unencrypted format. For more information about how TEMPDB is affected by TDE, please refer to my blog entry on that subject.

* Please note that steps 2 and 3 noted above should not be executed if there are other databases within the instance that have TDE enabled and are to remain encrypted.

Playing the Audible
There may be times when a change in plans is called for well after TDE has been implemented and data inserts and updates have occurred. At this point the option to simply restore the pre-TDE back up is no longer a viable solution due to potential data loss. The following steps can be followed to cleanly revert your database to its pre-TDE state:

  1. Set the ENCRYPTION option of the target database to OFF using the ALTER DATABASE command.
  2. Drop the Database Encryption Key (DEK) in the target database with the DROP DATABASE ENCRYPTION KEY command.
  3. Restart the instance in which these actions have occurred. If there are not any other databases in the instance with TDE enabled, this action will regenerate the TEMPDB database in an unencrypted format.

Please note that the server certificate and the Database Master Key (DMK) in the MASTER database were not dropped in this sequence; although TDE is no longer enabled on the target database there still remains some dependencies on the server certificate. If the server certificate were dropped and the instance were restarted the target database would be marked as “Suspect” and would not be brought on-line. A review of the SQL Server Error Logs would reveal the following error:

          Cannot find server certificate with thumbprint [Binary Certificate Thumbprint]

This is the same error that is received when attempting to recover a TDE enabled database to an instance without the accompanying server certificate and DMK combination.

Reviewing the tapes
Once the above steps are taken it is important to review the results to ensure that TDE has been successfully removed. The following queries can be used to view the results of TDE removal:

  1. SELECT name, database_id, is_encrypted FROM sys.databases
    This query will return all of the databases in your instance and reflect the encryption status. The value of “1” in the is_encrypted column indicates that TDE is enabled. A value of “0” indicates that TDE is not enabled.
  2. SELECT * FROM master.sys.certificates
    This query will return the server certificates that have been created in the MASTER database. Upon execution of the DROP CERTIFICATE command the server certificate will no longer appear.
  3. SELECT * FROM master.sys.symmetric_keys
    This query will return the Database Master Keys (DMK) that have been created in the MASTER database. Upon execution of the DROP MASTER KEY command the DMK will no longer appear.
  4. SELECT * FROM sys.dm_database_encryption_keys
    This query will return all Database Encryption Keys (DEK) that have been created in your instance. Upon execution of the DROP DATABASE ENCRYPTION KEY command the DEK for the target database will no longer appear. If there are no other databases with TDE enabled on the instance, a restart of the instance will remove the DEK for the TEMPDB.
  5. Query any table in the target database to show that data is successfully returned.

Another option for reviewing the results of the removal of TDE is to make a back up of the database after TDE has been disabled. Once the back up is complete, open the [MyDb].bak file in Notepad, or your favorite text editor, and search for a known value contained in any table or the target database. Since an encrypted back up file will store the data in its binary hash, a plain-text search for a specific value will result in no results; while an unencrypted back up file will locate the value.