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.

Removing Encryption

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.

Conclusion

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.