Transparent data encryption (TDE) is a new feature in Microsoft SQL Server 2008 Enterprise edition and in my opinion one of the best small features to come out of this release. It is a source of annoyance that you have to fork out the not inconsiderable sum of money for the Enterprise Edition in order to access this feature, but with a bit of luck it will eventually filter through to the other versions.

The implementation and use of TDE is incredibly simple, as you would expect from something that claims to be “Transparent”. It is also very well documented. However, in the event that you move the encrypted database or are required to restore it to another instance, you will be required to remove TDE and that is not so clear cut. Failing to follow the proper steps will result in wasted time or at worse, an unrecoverable database!

There are plenty of articles and blogs detailing the implementation of TDE that can be found by a simple Google search. This article aims to provide advice on how to remove TDE safely so that you are not left with a load of useless bits and bytes. The knowledge in this article was gained from painful experience.... and shouting.

T-D-What?!?!?

For those of you that don’t know what TDE is, it’s a method of encrypting the physical files associated with a database. This means that it covers any backups taken from the database in addition to data and log files. Any data stored in a database secured with TDE is automatically encrypted when written to disk and decrypted when read from disk. The users and clients are totally unaware of these automated actions, hence the term “Transparent”. It is worth noting that as well as the data files and any backups taken from an encrypted database, Tempdb is also encrypted on that instance. This is done to protect the transient data while it resides in TempDB. This will cause a small overhead on other, unencrypted databases on that instance.

TDE is used to secure the physical files from loss or theft and this protection is accomplished through an encryption key hierarchy that exists externally from the database in which TDE has been enabled.

Removing TDE

So, there you are with your TDE secured database, a good set of maintenance plans looking after the backups and most importantly, a secure backup of the certificate used to encrypt the database. You’re feeling pretty good about things.

Then one day you are asked to removed TDE from the database. “No Problem” you think. You set the encryption property to ‘Off’ and then drop the encryption key. For good measure you also delete the certificate that was used to create the encryption key from the instance. Voila, one unencrypted database. Happy days!

Weeks go by until one day the database server in question is rebooted. You then find that the database which was previously encrypted will not mount. You are faced with the error “Cannot find server certificate with thumbprint '0xBB1B0816079C10A6342683443A8E576196EFBE73'.”. You then try to restore the database from a backup and get the error “The file "DatabaseFile" failed to initialize correctly. Examine the error logs for more details.”

This has happened because there are still encrypted values in the log file. In my case the header could not be read because it was still encrypted. If you reach this point and you still have a copy of the certificate used to encrypt the database, restore that certificate to the Master Database and the database that was previously encrypted should mount. If you no longer have the certificate then you can try attaching just the mdf file which should rebuild the log file, but I have had mixed results with this. If you only have access to a backup file and not the certificate, then you really are stuck.

The reason for this issue can be found on MSDN:

Because the transaction log is designed as a write-once fail safe, TDE does not attempt to encrypt portions of the logs that are already written to disk. Similarly, the log header cannot be re-written because of this write-once principle so there is no guarantee that data that is written to the log even after TDE is enabled will be encrypted. The TDE background scan forces the log to roll over to the next VLF boundary, which allows for the key to be stored in the header. At this point, if the file scan is also complete, the DEK state changes to Encrypted and all subsequent writes to the log are encrypted.

The same also holds true when the process is reversed. Just because encryption has been turned off and the data file(s) has been decrypted, there is no grantee that data already written to the log file will be decrypted. This means that when the database is mounted, parts of the log file are unreadable which will cause the database to become unmountable.

The best solution is to make sure the log file is totally empty after TDE has been removed. The following the steps will take a database out of TDE and then clear the log file:

1. Alter the database to have the ENCRYPTION option set to the value of OFF. This decrypts the database and can take some time if the database is large. If there are no other database using TDE then an unencrypted TempDB will be created next time the instance starts.

USE MASTER
GO
ALTER DATABASE {Database Name}
SET ENCRYPTION OFF
GO

2. Wait until the decryption process is complete. Use the sys.dm_database_encryption_keys DMV to determine its status. A value of "1" returned in the encryption_status column indicates that the decryption is complete.

3. Drop the database encryption key for the database.

USE {Database Name}
GO
DROP DATABASE ENCRYPTION KEY
GO

4. Truncate the database log file.  This will remove all of the data contained within the log file, including any data that is still encrypted.

5. Set the database recovery mode to simple and then shrink the log file of the database. This removes any encrypted headers that are in the database. Once this has been done, the recovery mode can be set to Full if required. This step will cause the header of the log file to be rewritten, this is important as the header may still be encrypted even after TDE is removed. Note: If you switch back from Simple to Full logging, you should take a full back up immediately to re-establish the log chain.

6. Restart the instance in which the database resides. If there are not any other user databases on the instance that have TDE implemented, this action will force the recreation of the TempDB database in an unencrypted format.

Summary

This article has outlined some of the basic functions of Transparent Data Encryption, it has also given a number of scenarios where even though TDE has been switched off, aspects of that database remain encrypted and explained the potential consequences of this. Finally, this article has documented the proper steps that should be taken in order to ensure the all encrypted data has been decrypted or removed after TDE has been switched off.