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; GO ALTER DATABASE MyEncryptedDB SET ENCRYPTION OFF; GO -- Remove Encryption Key from Database USE MyEncryptedDB; GO DROP DATABASE ENCRYPTION KEY; GO
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.
