Skip to content

Does TDE really work on MDF files?

April 22, 2013

Yes, it does. However, let’s prove it. First let’s create a database, a table, and enter some data:

-- create a database
CREATE DATABASE TDE_Primer
;
GO
-- create and populate a table
USE TDE_Primer
go
CREATE TABLE MyTable
( myid INT
, myname VARCHAR(20)
, mychar VARCHAR(200)  
)
;
go
DECLARE @i INT = 65;
WHILE @i < 92
 begin
  INSERT mytable SELECT @i, 'Steve Jones', REPLICATE(CHAR(@i), 200);
  SELECT @i = @i + 1;
 END
;
GO
SELECT * FROM Mytable;
go

If I look at the table, I see my name with lots of data:

Capture_030

Now let’s detach the database and examine the results with a hex editor:

-- detach database
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TDE_Primer'
;

GO

I use XVI32 as an editor. It’s free, and you can download it. If I open up my MDF in this utility, here’s what I see:

Capture_031

If I search for my name:

Capture_032

I find it:

Capture_033

This is what I expect, and you should as well. Even without SQL Server, your data files are readable, which is why you must protect them.

Now let’s attach the file and enable TDE.

USE [master]
GO
CREATE DATABASE [TDE_Primer] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TDE_Primer.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TDE_Primer_log.ldf' )
 FOR ATTACH
GO

USE TDE_Primer
go
SELECT * FROM mytable
;
go

-- begin encryption setup
-- from http://msdn.microsoft.com/en-us/library/bb934049.aspx
USE master;
GO
-- create master key for master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AlwaysU$eaStr0ngP@ssword4This'
;
go

-- create certificate to secure TDE
CREATE CERTIFICATE TDEPRimer_CertSecurity WITH SUBJECT = 'TDE_Primer DEK Certificate';
go

USE TDE_Primer;
GO
-- Create DEK
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDEPRimer_CertSecurity;
GO

-- backup TDE cert
USE master
;
go
BACKUP CERTIFICATE TDEPRimer_CertSecurity
 TO FILE = 'tdeprimer_cert'
  WITH PRIVATE KEY (
               FILE = 'tdeprimer_cert.pvk',
               ENCRYPTION BY PASSWORD = 'AStr0ngB@ckUpP@ssw0rd4TDEcERT%')
;
go

-- check encryption status
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;
GO

-- enable encryption
USE TDE_Primer
;
GO
ALTER DATABASE TDE_Primer
  SET ENCRYPTION ON;
GO
-- check encryption status
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;
GO
-- TDE_PRimer and tempdb encrypted

-- detach database again.
-- detach database
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TDE_Primer'
;

GO

I won’t go into all the code, but this encrypts the database, backs up the certificate and then detaches it again. There are a few other things, but I cover them in another post.

Now let’s open up the file in the hex editor again.

Capture_034

It looks the same. It’s not in the image, but just below this you can see the database name. There is a header, which is not encrypted. However when I search for my name, it fails.

Capture_035

If you scroll further around, you’ll see that most of the file is now encrypted.

Capture_036

Play with this and prove to yourself that TDE does really encrypt things.

From → Blog

One Comment
  1. Gareth permalink

    Nice quick and clean example to show encryption does work :)

    I have been using TDE for over a year now.

Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 4,635 other followers

%d bloggers like this: