Disabling Database Encryption

Home / SQL Server 2008 / 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.

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.

7 Comments

  • stanque

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

  • scarydba

    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.

  • ZinDog

    This is a pain in the proverbial. I tried doing a backup of the log/database/log/database after encryption had been removed and still could not restore or attach to another server. Even someone else gets in the same situation (!) you can do this convoluted process to get the data (probably not good if you DB is huge though)

    1. Restore encrypted db to new database on same server
    2. Script and create a new database with same structures
    3. Using SQL Server Export tool (or scripts) to export data to new database
    4. You can then backup and restore that db to new server

  • Scott Gleason

    Hey Grant; Just to confirm your reply (shown above on November 10, 2009), “remove the certificate from the database”. After that I was able to backup from 1 server and restore to a 2nd server which never had TDE enabled. Running SQL 2008 R2 10.50.2806 🙂

  • Bill Lugaila

    Read the article and made sense, but I need the following. Trying to create a system that processes backups and creates scrubbed databases for developers.

    I need a script that will turn off encryption and once the database is decrypted drop the key and certificate. Currently when I turn off encryption on a 140 GB database it takes a while to decrypt and then I can drop the cert and key. I am trying to automate it and need a script that will drop the cert and key when the database is decrypted.

OK, fine, but what do you think?