How to Remove Transparent Data Encryption (TDE) from SQL Server database?

How to Remove Transparent Data Encryption? TDE is a robust security feature in SQL Server that encrypts data at rest, safeguarding sensitive information even if an unauthorized party gains physical access to the database files. However, there may be situations where you need to remove TDE from a database. This could be due to performance considerations, compatibility issues with other applications, or simply a shift in security requirements.

This blog post delves into the process of removing TDE from a SQL Server database, guiding you through the steps involved, highlighting key considerations, and addressing potential challenges. Whether you’re a seasoned SQL Server administrator or just starting to explore TDE, this comprehensive guide will equip you with the necessary knowledge to navigate the decryption process effectively.

Read More: How To Restore TDE Encrypted Database Backup: A Comprehensive Guide

Reasons for Removing TDE from a SQL Server Database

1. Migration to Another Environment: When migrating a SQL Server database to a different environment, it may be necessary to remove TDE. This ensures compatibility and smooth data transfer between environments.
2. Troubleshooting Encryption Issues: In some cases, issues related to TDE may arise, affecting database performance or causing errors. Removing TDE can be part of the troubleshooting process to identify and address these issues.
3. Changing Security Policies: As organizational security policies evolve, there might be a need to adjust encryption measures. Removing TDE allows for flexibility in adapting to new security requirements.

How to Remove Transparent Data Encryption?

TDE (Transparent Data Encryption) is used to encrypt the data to secure it. While we enable TDE, we first create a master key then create a certificate then a Database encryption key, and finally, we enable Transparent Data Encryption on the database. Now we have to reverse the process to Remove the TDE.

1. Backup Your Database: Before making any changes, it is crucial to create a backup of your SQL Server database. This ensures that you can restore the database in case of any unforeseen issues during the TDE removal process.
2. Turning Off Encryption: This initiates the decryption process, where the database data is converted back to its original unencrypted state.
3. Verifying Decryption: Once the decryption process completes, it’s crucial to confirm the database is indeed unencrypted.
4. Dropping the Database Encryption Key: This removes the key used to encrypt and decrypt the database, further enhancing security.
5. Optional Steps: Depending on your specific requirements, additional steps like removing the certificate used for encryption and taking a backup of the unencrypted database might be necessary.

NOTE: Before removing the TDE, take a backup of the master key and certificates and keep it in a safe location. These keys will be needed if you need to restore this database in the future with the help of its older backup files. Make sure to run a full database backup before executing this exercise

*Please test it in a lower environment first before the production database.

Detailed Steps for Removing TDE:

Please make sure you have the full valid database backup of the target database. Check SQL Server instance to verify whether our target database has TDE enabled or not. Run the T-SQL statement below to get details of all encrypted databases.

select encryptor_type, key_length, key_algorithm, encryption_state, create_date from sys.dm_database_encryption_keys GO SELECT name, is_encrypted FROM sys.databases Go

How do I turn off encryption in SQL Server? Turn Off TDE (Transparent Data Encryption)

The first step to remove the TDE from any SQL Server database is to turn it off by executing the below ALTER statement.

USE master; GO ALTER DATABASE DB_NAME SET ENCRYPTION OFF; GO

If the target database is huge in size, it will take some time to scan the database and disable the encryption.

How to turn off encryption using SSMS?

1. Go to Object_explorer
2. Expand the Databases folder
3. Right click on target database
4. Click on Properties
5. Click on Options
6. Scroll down to Database scoped options to ‘State’
7. Change the value of ‘Encryption Enabled’ from True to ‘False’ as show in given below image.

How do I remove an encryption key?

Remove the database encryption key from the database. Once you have turned off TDE from the user database, run the below T-SQL statement to drop the database encryption key.

USE DB_NAME; GO DROP DATABASE ENCRYPTION KEY; GO

The database encryption key is dropped now. We can check the TDE status for the target database again to ensure whether it is still there in the above DMV output or not. (How to Remove Transparent Data Encryption?)

We can not remove the database encryption key if we have not disabled Transparent Data Encryption from the target database and it will throw the errors.

select encryptor_type, key_length, key_algorithm, encryption_state, create_date from sys.dm_database_encryption_keys GO SELECT name, is_encrypted FROM sys.databases Go

Drop TDE certificate from MASTER database

Run the below T-SQL statement to drop the TDE certificate that was created to encrypt the database target database.

USE master Go DROP CERTIFICATE Certificate_Name_Cert; Go

The last step to complete the Transparent Data Encryption removal process is to drop its master key.

Drop MASTER KEY

The master key in TDE is used to protect the certificates associated with TDE encryption. As we have already decided to disable TDE from SQL Server and removed associated certificates in the above steps so there is no use in keeping the database master key on the SQL Server instance.

Now we can remove the master key by running the below T-SQL statement from the master database.

USE master Go DROP MASTER KEY; GO

The above T-SQL will drop the database master key and with this step, you are done with the Transparent Data Encryption removal process.

Now restart the MS SQL Server Service to take effect of the above activity.

Additional Considerations

• Performance Impact: The decryption process can consume system resources, impacting performance. It’s recommended to schedule this operation during off-peak hours to minimize disruptions.
• Backup Strategy: Before removing TDE, it’s vital to have a recent backup of the encrypted database. This backup serves as a safety net in case any unforeseen issues arise during the decryption process.
• Certificate Removal: If you used a certificate for encryption, you can drop it after verifying decryption and dropping the encryption key. However, ensure you no longer require the certificate for other purposes before proceeding.

You must immediately run a full backup of your unencrypted databases to ensure you have a healthy backup without any key or certificate. (How to Remove Transparent Data Encryption?)

---

FAQ: