It would not seem that once a database has TDE enabled there would be a need to turn this feature off. There may be times reasons to remove TDE from a database, because sensitive information has been removed, or the performance issues prove to be problematic. In any case, this article will look at the steps to remove TDE from a database, as well as the potential problems if the procedure is not completed in its entirety.
To remove encryption from a database, there is a very simple command that needs to be run. This an ALTER DATABASE statement to turn the encryption off. For this example, let's continue with the database I've used in the rest of the series, TDE_Primer. To decrypt the database files, I can run:
ALTER DATABASE TDE_Primer SET ENCRYPTION OFF ;
This start the process of decryption, much like the process of encryption. This command returns immediately with the "Command(s) completed successfully." message in your query application. However, the decryption process will spawn a thread that will go through your database, decrypting each page and rewriting the un-encrypted version of the page back to disk.
As with the encryption process, you can track the status of your decryption by running this query:
SELECT db.name, db.is_encrypted, dm.encryption_state, dm.percent_complete, dm.key_algorithm, dm.key_length FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id;
Note the query shows all databases, whether they are encrypted or not. If I want to just see the results for my database, I can run this:
SELECT db.name, db.is_encrypted, dm.encryption_state, dm.percent_complete, dm.key_algorithm, dm.key_length FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id WHERE db.name = 'TDE_Primer'
When I run this, I will results similar to this:
The value of five in ff means that the database is in the process of being decrypted. This is documented in the sys.dm_database_encryption_keys DMV. The progress towards decryption is displayed in the percent_complete column. This number will rise to 100, at which point it will return to 0 and the encryption_state will change to 1.
At this point, the database is decrypted, but I still have an encryption_state of 1. My query will show these results.
The encryption_state of 1 means that the database (and log) are unencrypted, but the database still has a Database Encryption Key. I also need to run a DDL to remove this DEK. I can do this with:
DROP DATABASE ENCRYPTION KEY
This removes the DEK and returns the database encryption_state to 0, as you can see below.
Once this is complete, the data files are the same as they would have been before TDE was enabled.
Verifying the Decryption
How can we be sure that the database has been returned to this state? We do this the same way we verified the encryption was complete. We detach the database and examine the files. First, I will make a backup of the database. Then I can detach it.
BACKUP DATABASE TDE_Primer TO DISK = 'TDE_Primer_Full_Unencrypted.bak' ; go USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N'TDE_Primer' ;
Now I can use a hex editor to view the data file. I will browse to the location of the datafile and open it. When I search for "Steve Jones", which is a value stored in one of my tables, I find I can easily view the row data.
Similarly, let's open the backup file. I can do this and search for the same string. I also find the value here and can read the file.
We can see that our data is now decrypted.
Problems with Restores
If you do not remove the DEK, then you will have issues restoring database backups. If you do not have a copy of the server certificate, you won't be able to perform a restore, which could cause you quite a few problems in a disaster situation. Let's look at the issue.
I've rebuilt my demo and I've kept the DEK in the database after turning off encryption. I can see this when I run a query about the status of the database.
I decide to run a backup to disk. When that is finished, I'll copy it over to the backup folder for another instance. In this case, I have a 2019 instance handy, and when I select the "Restore database" dialog, I can see my database backup.
When I create the script and try to restore this backup, I get an error:
Even though the data isn't encrypted, I can't restore this database. The certificate is needed because the header notes a DEK. Let's fix that.
Using DDL, we can drop they key with the DROP DATABASE ENCRYPTION KEY command. I can easily do that and then run another backup. I'll see this in SSMS.
I then take this file and copy it over to my new instance. When I do that, I can then perform the restore. I need the WITH MOVE option here. I didn't get the error above because the process stopped without the certificate. Here, I would get a file error, but with the movement of the files, this proceeds.
I didn't do anything to either instance between the restore failure and the second try after removing the DEK. This just works if there isn't a DEK.
Removing encryption from a TDE database is fairly simple, but it does have a small gotcha. You need to ensure you remove the DEK after turning off encryption. Otherwise, you may impact your ability to handle a DR situation.