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

ALTER DATABASE TDE_Primer

  SET ENCRYPTION OFF;

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:

DROP DATABASE ENCRYPTION KEY

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

2 Responses to Post TDE–Getting Unencrypted Backups

  1. datasunrise says:

    This is good information for database backup , Thanks for sharing this Blog with us .DataSunrise provide the databadse backup software .It is a very important Software to secure all databases in the organization. Your comment is awaiting moderation.

  2. Hello M8 information on this site is just unbelievable to me it’s coming back time and time again, I am personally using this site, so I love it couldnt meet my wife puts me on any of the media, I think that, well, I’ve done all need others making the time spent on this fabulous to see these Thang, thank you! Okay, nice one to deliver

    IT Support Dallas

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