Removing Transparent Data Encryption (TDE)

This month blog post is about removing Transparent data encryption (TDE) from a SQL Server database. It's not as simple as you thought. If you do it carelessly, you will be doomed. You will find that the database which was previously encrypted will not mount. You are faced with the error "Cannot find server certificate with thumbprint 'XXXX'.". 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. 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. If you only have access to a backup file and not the certificate, then you really are stuck.

Let's go to the sample script about how to remove TDE correctly:

/* HOW TO REMOVE TDE

* https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16#remove-tde

* https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-database-encryption-key-transact-sql?view=sql-server-ver16#examples

*/

-- CHECK

USE master

GO

SELECT DB_NAME(database_id), * FROM sys.dm_database_encryption_keys;

GO

--DECRYPT

ALTER DATABASE TestDB SET ENCRYPTION OFF

GO

-- CHECK encryption_state 3 --> 1

SELECT DB_NAME(database_id), * FROM sys.dm_database_encryption_keys;

GO

-- WAIT

USE TestDB

GO

DROP DATABASE ENCRYPTION KEY

GO

-- Clear Log

USE master

GO

ALTER DATABASE TestDB SET RECOVERY SIMPLE;

GO

USE [TestDB]

GO

DBCC SHRINKFILE (N'TestDB_log' , 0, TRUNCATEONLY)

GO

-- Reset Log

USE master

GO

ALTER DATABASE TestDB SET RECOVERY FULL;

GO

-- Make Compressed Backup

BACKUP DATABASE TestDB TO  DISK = N'D:\Backup\TestDB.bak' WITH NOFORMAT, NOINIT,  NAME = N'TEstDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO