How to Decrypt a TDE-Enabled Database in a SQL Server Always On Environment

 

There could be various reasons for removing an existing TDE (Transparent Data Encryption) implementation from SQL Server databases. You may be considering an alternative encryption solution, experiencing performance issues, or needing to share a copy of the database or its backup with other business units that do not support TDE. Regardless of the reason, properly decrypting a TDE-enabled database in a SQL Server Always On environment requires careful execution to ensure data integrity and minimize downtime. In this guide, we will walk through the steps to safely remove TDE encryption from a database while maintaining availability and security.

For encrypting an SQL Server database, please review Encrypt an Existing MS SQL Server 2019 Database in an Always On Environment Using TDE

1. Remove TDE from a database.

NOTE: Take a full Backup of the database.

NOTE: Back up the master key and certificates to a secure location. These are essential for future database restores using older backups.

If you need to disable data encryption temporarily, it is crucial to retain the master key and associated certificates. You can achieve temporary removal by disabling TDE at the database level using the ALTER DATABASE statement and subsequently re-enable it with the same command.

On the primary and secondary replicas, the following query shows which databases have been encrypted.

SELECT name, is_encrypted
FROM sys.databases
Go 

On the primary replica SQLNODE1\ AdventureWorks2019

On the secondary replica SQLNODE2\AdventureWorks2019

Turn off TDE on the primary database using the following SQL command.

use [AdventureWorks2019]
GO
ALTER DATABASE [AdventureWorks2019] SET ENCRYPTION OFF;
GO 

On the primary instance (SQLNODE1\ AdventureWorks2019) I executed the above command.

The following query shows database decryption is in progress on the primary and secondary databases.

SELECT DB_NAME([database_id]) AS 'Database Name',
   [encryption_state],
   [percent_complete],
   [encryption_state_desc],
   [encryption_scan_state],
   [encryption_scan_state_desc],
   [encryption_scan_modify_date],
   [create_date],
   [regenerate_date],
   [key_algorithm],
   [key_length],
   [modify_date],
   [set_date],
   [opened_date],
   [encryptor_thumbprint],
   [encryptor_type]
FROM [sys].[dm_database_encryption_keys] 

The following is the output from the primary replica (SQLNODE1\AdventureWorks2019).

The following is the output from the secondary replica (SQLNODE2\AdventureWorks2019).

NOTE: We should wait until the process on the primary replica is complete.

Upon successful execution of the command, the TDE_DB database's encryption status changes to UNENCRYPTED (as shown below), while tempdb remains ENCRYPTED. For larger databases, the decryption process may take longer, displaying a DECRYPTION IN PROGRESS status until completion, at which point it will change to UNENCRYPTED. Note that the sys.databases system object reflects the unencrypted state (value 0) immediately.

Now, it has been UNENCRYPTED.

NOTE: Step 1 alone will decrypt the database; As you can see in the above image the AdventureWorks2019 database is UNENCRYPTED but the Tempdb is still Encrypted the following steps (cleaning and deleting the certificate and master key) are optional.

2. Clean the instance from TDE.

2.1. On the primary database, turn Off Transparent Database Encryption (TDE)

NOTE: Back up the master key and certificates to a secure location, as they are essential for restoring databases from older backups.

NOTE: If a certificate is shared by multiple databases, removing it will disable TDE for all of them. Therefore, only remove a certificate if you intend to disable TDE for the entire SQL Server instance.

The previous and following queries show which databases have been decrypted after completing step 1.

use master
go
SELECT name, is_encrypted
FROM sys.databases 

The following is the output of both queries on the primary and standby databases, which have the same result.

2.2. On the primary database, drop the Encryption Key for Database

Using the following query, we can check that the DEK still exists in the AdventureWorks2019 database.

Use Master
Go
SELECT 
    d.name AS DatabaseName,
	d.is_encrypted,
    dek.database_id,
    dek.encryption_state,
    dek.key_algorithm,
    dek.key_length,
    dek.encryptor_type,
    dek.encryptor_thumbprint
FROM sys.dm_database_encryption_keys dek
INNER JOIN sys.databases d ON dek.database_id = d.database_id;

The following is the output of the above query on the primary and standby databases, which have the same result.

After disabling TDE on the user database, execute the following T-SQL statement on the primary replica SQLNODE1\ AdventureWorks2019 to remove the database encryption key.

USE  [TempestTest]
GO
DROP DATABASE ENCRYPTION KEY;
GO

I executed the above command on the SQLNODE1\ AdventureWorks2019 instance.

I re-executed the above query and the absence of entries in this query confirms that Transparent Data Encryption has been fully removed from the user database. The following is the identical output on the primary and standby replicas.

However, the related files—specifically the master key and its corresponding certificate—still exist within the master database. If these components are used to encrypt other user databases, they should be retained. However, if you want to completely remove TDE and its master key/certificates because no databases in your SQL Server instance are encrypted using TDE, then you must remove them to clean your system.

NOTE: Before proceeding, ensure that the encryption key has been dropped from all other databases that may be using it. For example, I have two databases: StackOverflow 2013 and AdventureWorks2019. To delete the master key, I first deleted all certificates associated with the current master key in both databases. 

Here, I deleted the master key from StackOverflow2013:

USE [StackOverflow2013]
GO
DROP DATABASE ENCRYPTION KEY;
GO

3. Drop TDE Certificate for Master Database

3.1. Drop the TDE certificate from the MASTER database on the primary replica.

Use the following query to find out the certificate name if you don’t know the certificate name. We should run these commands on both primary and secondary replicas.

use master
go
select * from sys.certificates 
Go

The following is the identical output on the primary and standby replicas.

Execute the following T-SQL statement to remove the TDE certificate used to encrypt the user databases on both primary and secondary replica.

use master
go
select * from sys.certificates 
Go

On the primary replica

On the secondary replica

Re-execute the above query to find out if the certificate has been deleted on the primary and secondary replicas.

3.2- Drop the Master Key

Note: We should run these commands on both primary and secondary replicas.

The following query shows the master key associated with the database.

use [master]
Go
SELECT name, symmetric_key_id, algorithm_desc, create_date
FROM sys.symmetric_keys 
WHERE symmetric_key_id = 101;

Remove the master key by running the following T-SQL statement from the master database. Execute it on both the primary and secondary replicas.

USE master
Go
DROP MASTER KEY;
GO

On the primary replica

On the secondary replica

Re-execute the above query to find out if the master key has been deleted primary and secondary replicas.