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'
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.