Disabling Database Encryption

March 13, 2009 at 10:48 am (SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , , )


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.

Msg 3283, Level 16, State 1, Line 1
The file “TDEDisabling_log” failed to initialize correctly. Examine the error logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

 

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:

SELECT

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.

2 Comments

  1. stanque said,

    It seems like presence of Db encryption key generated is causing this problem. When I dropped it and tried backup/restore everything worked fine.

  2. scarydba said,

    It’s slightly more complicated than that. According to MS on the Connect link above, logs might be encrypted. As long as any portion of the log is still encrypted, the certificate will still be required. So you have to remove the certificate, backup the log and backup the database. Then, backups occurring after that don’t require the certificate.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: