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'

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.

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

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

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.

Nothing is human readable anymore.