Disabling Database Encryption
March 13, 2009 at 10:48 am (SQL Server 2008, SQLServerPedia Syndication, TSQL) (backup, encryption, restore, tde, total database encryption, transparent 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.
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:
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.
stanque said,
November 10, 2009 at 6:15 am
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 said,
November 10, 2009 at 7:22 am
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.