How to Remove Transparent Data Encryption From a Database

There may be a time when you want to turn off transparent data encryption on one of your TDE enabled databases.  One of these times might be if you want to send a copy of your database to someone else without having it encrypted.  Another reason might be you no longer need to have TDE turned on because you have either removed the confidential data you wanted encrypted, or you have encrypted your confidential data using something like Always Encrypted.

It is very simple to remove Transparent Data Encryption.  You can run the following code:

-- Turn off TDE
USE master;
-- Remove Encryption Key from Database
USE MyEncryptedDB;

Keep in mind, when you ALTER  your database and set the ENCRYPTION to OFF, that this is not instantaneous.  It will take SQL Server a while to decrypt your database. It does this decryption asynchronously.  If you want to see the status of your decryption you can run the following command:

SELECT db_name(database_id), encryption_state 
FROM sys.dm_database_encryption_keys;

If the encryption_state is set to “5” the database is still being decrypted.  If the encryption_state is set to a “1” then the database is decrypted, and if the encryption_state is set to “0”, then the database does not have an encryption key present.

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles