Disabling Database Encryption

SQL Server 2008 introduced TDE (either Total Database Encryption or Transparent Database Encryption, I’ve seen both) which is a way to get a passive encryption in place on the database to prevent people from stealing backups or detaching files & stealing them. It works with a Certificate in the server, and there are mechanisms around password protection of the Certificates, management etc. Setting it up & turning it on are very easy. This example is from a chapter on a book I’m working on:

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Chapter8Backup’;

GO

CREATE CERTIFICATE Chapter8Certificate WITH SUBJECT = ‘Chapter 8 Certificate’

GO

USE AdventureWorksLT

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE Chapter8Certificate

GO

ALTER DATABASE AdventureWorksLT

SET ENCRYPTION ON

GO

That sets up the encryption and turns it on. Now when you run a backup, you need to move the certificate files with the backup in order to restore the backup to another machine. Cool stuff. What happens when you realize you don’t need to encrypt a backup? Simple, run the ALTER DATABASE to set encryption off. One of the posters at SQL Server Central, Amit Lohia, asked that same question and had the code to test it. What happens is, the backups taken after you disable encryption fails.

 

Examining the logs just shows the same error message. So a little searching around on the web found this on Connect (I do love that site). Apparently the certificate would have to be on the other server unless you removed it prior to backing up. Fine. Let’s drop the certificate.

 DROP CERTIFICATE Chapter8Certificate

Unfortunately, that didn’t work either. You can check the status of encryption using the dynamic management view sys.dm_database_encryption_keys like this:

db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys

 

That will show your database is not encrypted, a value of zero in the encryption_state column, but still listed on the encryption list. If you were dealing with a really large database, you can check that view to watch the status of encryptiong being turn on or turned off. Still, it’s that listing on the encryption view that seems to be the problem. I’ve looked and looked and I don’t have an answer. So I posted my own issue on Connect. We’ll see what MS has to say about it.

I’ll post any answers I get. If anyone has any good ideas, I’m all ears.