Transparent Data Encryption is getting popular these days because every business owner is serious about protecting their data. Everybody wants to use the latest encryption technologies to make sure their systems are more secure and stable. SQL Server also offers some encryption features to protect client’s data like TDE (Transparent Data Encryption), Always Encrypt, etc. Sometimes, business wants to transform their existing encryption solution by implementing its advance versions or encryptions. You must remove the existing encryption solution before implementing any newer solution.
Today, I will explain how to completely remove TDE from your SQL Server instance so that you can implement another encryption solution for your SQL Server databases. There could be various reasons for removing exiting TDE implementation from SQL Server databases like you are considering another encryption solution, you have performance issues, or you need to send this database copy or its backup to other business units, etc.
Removing TDE is a straightforward process if we follow all steps in a sequence manner. If we will not follow the recommended process, then we will face issues while dropping its keys and certificates. We follow steps in reverse order which we had followed during creating the TDE configuration. The below sequence of activities needs to be followed to remove TDE from the SQL Server database.
- Turn off TDE on the database
- Drop Database Encryption Key
- Drop Certificate
- Drop master key
If you observe the process of removing TDE, you came to know that we are following the exact opposite sequence of activities as compared to its enablement process. While enabling it 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 whereas we are doing it here in reverse order.
If you are thinking, what will happen if we will not follow this sequence and try to drop database encryption key or certificates or master key without turning off TDE on the database. SQL Server will not allow you to drop any key or certificate unless you will follow this sequence. You will be getting below errors if you try to drop them before turning off TDE.
Attempting to drop master key without turning off TDE
Msg 15580, Level 16, State 1, Line 3
Cannot drop master key because certificate ‘TDE_DB_Cert’ is encrypted by it.
Attempting to drop certificate without dropping database encryption key
Msg 3716, Level 16, State 15, Line 3
The certificate ‘TDE_DB_Cert’ cannot be dropped because it is bound to one or more database encryption key.
Attempting to drop database encryption key without disabling TDE on database
MSG 33115, Level 16, State 1, Line 5
Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.
Even if you turn off Transparent Data Encryption from the database and you missed the sequence after that to drop the keys and certificate then also SQL Server will not allow you to drop that key because master key and certificates have dependencies, and they are integrated into each other. We need to first remove the dependencies and then go for their removal process. The above sequence will allow us to remove them smoothly without any issue.
Let’s clean your system by removing TDE and its components so that we can implement another encryption solution without any issue.
Prerequisites
Once you have decided to remove TDE from the SQL Server database, you must consider the below points as part of planning before its removal.
- 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. Read attached article to learn how to backup TDE certificates
- Do not remove the certificate if it is being shared by multiple databases and you want to remove TDE from only one database. If you want to clean complete the instance from TDE then you can go ahead with the removal
- If you have a requirement to remove TDE temporarily then do not remove its master key and certificates. You can just turn off TDE from the database and later turn on TDE by running ALTER statement
- If possible, reduce the database file size by removing unwanted data to reduce TDE scan time during the removal
- Make sure to run a full database backup before executing this exercise
- Always perform this activity during off business hours as TDE removal will initiate the scan process in the backend which will increase the load on the database system
Remove TDE (Transparent Data Encryption)
Check SQL Server instance to verify our target database has TDE enabled or not. Run the T-SQL statement below to get details of all encrypted databases.
|
1 2 3 4 5 6 7 |
----Check TDE on SQL Server Instance SELECT DB_Name(database_id) As [DB Name, encryption_state, encryption_state_desc FROM sys.dm_database_encryption_keys GO SELECT name, is_encrypted FROM sys.databases Go |
You can see its output in the below image. Database TDE_DB has encryption enabled. Tempdb is also showing as encrypted. We all know that the tempdb database is encrypted if we enable TDE on any user database. Read more about the relationship between Transparent Data Encryption and Tempdb in this article, Impact of TDE on Tempdb databases, where I have explained the reason why tempdb got encrypted during user database TDE enablement.
You can also see the associated certificate TDE_DB_Cert in the left side pane of the above image. Now, we must remove TDE from the user database TDE_DB on this SQL Server instance. Let’s follow the same sequence I have mentioned above.
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.
|
1 2 3 4 5 |
-- Replace “TDE_DB” with your target user database name USE master; GO ALTER DATABASE TDE_DB SET ENCRYPTION OFF; GO |
The above command will take some time if your database is big in size because a TDE scan process will be performed in the backend during the encryption or decryption process. This process is resource-intensive so plan this activity during off business hours.
Once the above command will execute successfully then the encryption state of the TDE_DB database will change to UNENCRYPTED as shown in the below image whereas tempdb is still showing as ENCRYPTED. If you have a bigger database, then the TDE scan will take time and show you the encryption state for this database as DECRYPTION IN PROGRESS during the scan process. Once the decryption process will complete this state will change from DECRYPTION IN PROGRESS to UNENCRYPTED whereas sys.databases system object has immediately set its state as unencrypted by setting its value from 1 to 0.
We can also turn off TDE using GUI by accessing the database properties window. You just need to launch the database properties window in SQL Server management studio and then click on the “Options” tab from the left side pane. You can see the “Encryption Enabled” option set as True in the state section in the right-side pane. Just choose False from the drop-down for this setting and click on the OK button to apply this change. You have disabled TDE using GUI for a SQL Server database.
If you don’t want to remove the database master key and associated certificate, then you can go ahead and restart the SQL Server service to remove encryption from the tempdb database as well. I have restarted the SQL Server instance and then checked the Transparent Data Encryption status again.
Database tempdb is no more showing as encrypted in both result sets. Although it’s recommended to restart the SQL Server service once you have completed all steps in a sequence manner if you want to remove TDE and all associated keys and certificates. If you want to disable TDE temporarily then it has been disabled from your target database “TDE_DB” as per the above images. You can run the above ALTER statement with the SET ENCRYPTION ON option in case you want to enable it for your target database.
As, DMV sys.dm_database_encryption_keys is still showing an entry for database “TDE_DB” as unencrypted, it means Transparent Data Encryption has not been completely removed and its associated database encryption key is still there. Next, we will drop the database encryption key to remove TDE completely from the database.
Drop Database Encryption Key
Let’s remove the database encryption key as we have already turned off TDE from this database. You would not be able to remove the database encryption key if you have not disabled Transparent Data Encryption from the user database and you will be thrown out of errors as I have given above in this article. Make sure to do it first as explained in the above step.
Once you have turned off TDE from the user database, run the below T-SQL statement to drop the database encryption key.
|
1 2 3 4 5 |
-- Drop Database Encryption key USE TDE_DB; GO DROP DATABASE ENCRYPTION KEY; GO |
Here is the output:
The database encryption key is dropped now. Let’s check the TDE status for the user database again to ensure whether it is still there in the above DMV output or not. I have executed the same T-SQL statements to check the Transparent Data Encryption state for this database post removing the database encryption key.
We can see there is no entry in this DMV now. It means Transparent Data Encryption has been removed from this user database completely but some of its associated files are still there in the master database that is master key and its certificate. If you are using the master key and same certificates to encrypt any other user database, then you should not remove them and leave them as it is. But if you want to completely remove TDE and its master key\certificates because you don’t have any database encrypted using TDE on your SQL Server instance then you must also remove them to clean your system. Next, I will show you how to remove its master key and certificate to clean Transparent Data Encryption components from SQL Server instance.
Drop TDE certificate from MASTER database
Run the below T-SQL statement to drop the TDE certificate that was created to encrypt the database TDE_DB.
|
1 2 3 4 5 |
-- Drop Certificate USE master Go DROP CERTIFICATE TDE_DB_Cert; Go |
The above command will drop the TDE certificate as we have validated it in the below image.
I have refreshed the master database and then expanded the security folder to see the associated certificate and we cannot see our target certificate TDE_DB_Cert in the above image. 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.
Let’s remove the master key by running the below T-SQL statement from the master database.
|
1 2 3 4 5 |
-- Drop master key 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.
If you have not restarted the SQL Server service as I suggested in the section “Turn OFF TDE” then you can now restart the SQL Server service to create new files for the tempdb database without having TDE configuration.
You must immediately run a full backup of your unencrypted databases to ensure you have a healthy backup without any key or certificate. Now, you can go ahead and perform activities for which you have decided to remove TDE from your SQL Server user database.
Conclusion
In this article, we learned how to remove TDE from SQL Server Databases using a sequence of activities. Please share this article and give your feedback in the comment section so that we can improve next time in a better way.
He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.
You can also find him on LinkedIn
View all posts by Manvendra Singh
- Configure backup storage redundancy for Azure Cosmos DB account - May 15, 2024
- Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
- How to do a group by clause - April 12, 2023