TDE: Looking inside your database and backups

TDE (Transparent Data Encryption) encrypts the data files at rest but don’t forget that it also encrypts your backup file too. I fancied looking inside the MDF and a backup of my database before enabling TDE and comparing it to when enabled.

Let’s look at a database backup with no TDE.

USE master;
GO
SELECT db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,dm.key_algorithm,dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id WHERE name = 'DWHDB'

noencry

The Backup

Next step is to back it up.

BACKUP DATABASE [DWHDB] TO DISK = 'C:\SQLSERVER\DWHDbCopy.bak'
WITH COPY_ONLY, STATS = 10

Ok great let’s check this backup file using a cool tool (XVI32). I really want the contact number of a guy called SQLDOUBLEG because I need his help tuning my SQL Servers so I go looking for a text string, hopefully the phone number will be close by.

mrsql

Yep, it is highlighted in yellow – phone number 320-555-0195 (This is a fake number from AdventureWorks).

hello

So I take the hammer approach and enable TDE (you can do backup encryption only and I will show you that in another post one day – time pending).

Below is the code to create the DEK (Database Encryption Key) and finally enable it.

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert

GO

ALTER DATABASE [DWHDB] SET ENCRYPTION ON

On

Create a new backup to analyse.

BACKUP DATABASE [DWHDB] TO DISK = 'C:\SQLSERVER\DWHPOSTencrypt.bak'
WITH COPY_ONLY, STATS = 10

Again I go looking for my friend.

nostring

Nothing is human readable anymore.

junk

The Database

While I am here lets’ check the data file (MDF) file. I have switched encryption OFF.

ALTER DATABASE [DWHDB] SET ENCRYPTION OFF

I want page 1357.
1357

I then use the output of DBCC page to help calculate an offset of the record plus the starting column on the page which by default is in hex.

DBCC TRACEON (3604)
DBCC PAGE (N'DWHDB',1,1357,3);
GO

SELECT CONVERT (INT, 0x1a68 +62) as [offset decimal]

offset

Hence, 8192 X 1357 = 1116544 + 6822 = 11123366. Open up your HEX editor and go to it as shown below.

decimal

I have now found him and his phone number within the MDF file. I guess I could’ve searched for the text string version but where is the fun in that?

HelloSQL

Switch on encryption what would you expect to see? The offsets haven’t changed (so it seems).

ALTER DATABASE [DWHDB] SET ENCRYPTION ON

junk1
That is kind of what I expected for the MDF file. If you use DBCC PAGE after enabling encryption you can actually still see the data values, makes sense.

thepage

Finally lets switch it back off and there he is again within the data file.

ALTER DATABASE [DWHDB] SET ENCRYPTION OFF

hellyou

I think I will call him now.

Advertisements

One thought on “TDE: Looking inside your database and backups

  1. Pingback: TDE: Looking inside your database and backups - SQL Server Blog - SQL Server - Toad World

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