Playing with TDE

UPDATE November 2016 – With SQL Server 2016 SP1, Microsoft will include key enterprise-class features in every edition of SQL Server 2016. This includes TDE! So lets have a recap.

What is it?

Quite simply – ENCRYPTION at rest; your data files will be encrypted on disk which is done at page level. So the page will be encrypted when written to disk and decrypted when read into the buffer pool.

Architecture

Taken from Books on line (BOL) we will ultimately be going though the below steps in strict order.

tde

TSQL

SELECT * FROM sys.symmetric_keys

tde1

You will see that I have a Service master key and I have already created my Database master key. So let’s create a certificate.

USE master
GO
CREATE CERTIFICATE TDECertAW WITH SUBJECT = 'tde Certificate AW'

Now that we have created a certificate we will use it to encrypt the Database Encryption key in the user database.

USE [CustRecords]
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDECertAW;

GO

Output = Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

So yes, you really should follow the instructions above and store these files in a secure offsite location.

USE master;
GO
BACKUP CERTIFICATE TDECertAW
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\TDECertAW.cer'
WITH PRIVATE KEY
(FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\certificate_TDECertAW.pvk',
ENCRYPTION BY PASSWORD = '!£$Strongpasswordherewelikesqlserver#')

Enable it

ALTER DATABASE [CustRecords] SET ENCRYPTION ON;

GO
--check

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 = 'CustRecords'

State 3 which means encrypted. (Please note that TempDB will also get encrypted).

tde3

Digging deeper

Let’s look at a page before encryption was enabled. First the contents of a table from SSMS (SQL Server Management Studio)

tde4

So let’s calculate the offset for page 78:  78*8192 = 638976, so when we use the hex-editor we shall look for 638976 (decimal) as the beginning of the page.

tde5

You will see the ASCII representation of the data. This is what we expect for a database that does not have TDE enabled.

So what happens when TDE is enabled?

Let’s check a page first using print option 3 which means per row interpretation.

DBCC TRACEON (3604);
GO
DBCC PAGE (CustRecords, 1, 78, 3);

tde6

Above is a snippet where you see decrypted data – as you would expect.

So let’s look at the MDF (on-disk) using the technique discussed earlier – again navigating to the offset via a hex-editor.

tde7

This is the outcome of enabling TDE – see the difference?

 

 

4 thoughts on “Playing with TDE

  1. Pingback: TDE Recovery | All about SQL

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