SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Post TDE–Getting Unencrypted Backups

I saw a question posted recently about someone that had disabled TDE and was still having issues restoring a backup. This doesn’t seem like that should be an issue, but it can be. A little testing shows how.

Let’s assume I have an encrypted TDE database. If I run a query, I can see the status as 3, which is encrypted.

2016-11-23 11_16_07-11_TDE_Demo.sql - localhost_SQL2016.TDE_Primer (PLATO_Steve (64))_ - Microsoft S

If I take a backup at this point, the backup will be encrypted, and to restore this on another instance, I’d need to first restore the certificate. I don’t want to do that, so let’s remove encryption. This is a simple command



This runs quickly.

2016-11-23 11_18_08-11_TDE_Demo.sql - localhost_SQL2016.TDE_Primer (PLATO_Steve (64))_ - Microsoft S

If I now query for encryption, I see this.

2016-11-23 11_19_00-11_TDE_Demo.sql - localhost_SQL2016.TDE_Primer (PLATO_Steve (64))_ - Microsoft S

A one means that this is an unencrypted database, but a DEK (Database Encryption Key) exists. If I were to detach and examine this database file with a hex editor, the pages would be decrypted.

I’ll now take a backup and move that to another instance. Once I’ve copied that over, I’ll try to restore the backup. In T-SQL, I’ll see this:

2016-11-23 11_21_43-SQLQuery7.sql - (local)_SQL2016_qa.master (PLATO_Steve (60))_ - Microsoft SQL Se

Why is this? The database was decrypted, as was the backup. In fact, if I open my backup file in a hex editor, I can see row data.

2016-11-23 11_23_12-XVI32 - tde_primer_decrypted.bak

The Problem

When SQL Server goes to restore the file, it reads part of the header. In here, the process must detect the DEK and try to decrypt that key. However, since this new instance does not have the certificate, this doesn’t work and an error is thrown, despite not needing the key since the data isn’t encrypted.

The issue here is the DEK still exists in the source database.

The Solution

Let’s fix this. I’ll return to my first instance and the original database that was TDE encrypted and now is not. I can issue this:


Once I do this, it completes quickly. This is a standard DDL command, but one that’s not often used.

Once I do this, I’ll take another backup and return to the second instance. Now when I try the restore, I see this:

2016-11-23 11_27_28-SQLQuery8.sql - (local)_SQL2016_qa.master (PLATO_Steve (58))_ - Microsoft SQL Se

If you’re having issues restoring a database that used to be TDE encrypted, try removing the DEK and then backing it up.

Filed under: Blog Tagged: encryption, security, syndicated, tde

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...