Transparent Data Encryption (TDE)has many advantages, and satisfies a number of audit, security and compliance requirements. This article is not about setting up TDE.
What if however, for whatever reason, your organization wants to remove TDE from one or more databases? This is what this article is about.
1. Get the current Database Encryption status
Verify that the databases are TDE enabled:
encryption_state = 3
USE [master] GO SELECT DB_NAME([database_id]) AS DatabaseName ,[encryption_state] AS EncryptionState ,[key_algorithm] AS Algorithm ,[key_length] AS KeyLength FROM sys.dm_database_encryption_keys WHERE [database_id] = DB_ID('<DATABASE_NAME>'); GO
2. Turn off TDE
USE [master]; GO ALTER DATABASE <DATABASE_NAME> SET ENCRYPTION OFF; GO
3. Check the status of the decryption process
ALTER your database and set the
OFF, however that is not instantaneous and depends on the size of your database.
Run the following command to check progress:
USE [master] GO SELECT DB_NAME([database_id]) AS DatabaseName, [encryption_state], [encryption_state_desc] = CASE [encryption_state] WHEN '0' THEN 'No database encryption key present, no encryption' WHEN '1' THEN 'Unencrypted' WHEN '2' THEN 'Encryption in progress' WHEN '3' THEN 'Encrypted' WHEN '4' THEN 'Key change in progress' WHEN '5' THEN 'Decryption in progress' WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)' ELSE 'No Status' END, [percent_complete], [encryptor_thumbprint], [encryptor_type] FROM sys.dm_database_encryption_keys WHERE [database_id] = DB_ID('<DATABASE_NAME>'); GO
If the encryption_state has a value of 5 the database is still being decrypted.
If the encryption_state has a value of 1 then the database is decrypted,
If the encryption_state has a value of 0, then the database does not have an encryption key present.
4. Remove the Encryption Key
Once decryption is complete remove the Encryption Key from each Database
USE [<DATABASE_NAME>]; GO DROP DATABASE ENCRYPTION KEY; GO
5. Clean up
Assuming you are doing this as a proof of concept, remove the decrypted databases once ready.
USE [master]; GO DROP DATABASE [<DATABASE_NAME>]; GO