TDE: Looking inside your 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 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

 

1 thought on “TDE: Looking inside your 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s