Transparent Data Encryption (TDE) Demo

 This post demonstrates the use of Transparent Data Encryption (TDE) to protect database files including backups.  To understand how this features can be employed to improve the security of database applications, please review this post.

 The first step in the demonstration is to create an empty database which will be encrypted with TDE in a later step:

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'TdeDemo') DROP DATABASE TdeDemo;
GO

CREATE DATABASE TdeDemo;
GO

Next, create the server-level certificate which will protect the database key used to encrypt the database's files.  This certificate in turn will be protected by the master key which if it does not exist will need to be created:

USE master;
GO

IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE name LIKE '%[_]DatabaseMasterKey%') BEGIN
 CREATE MASTER KEY ENCRYPTION
  BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh';
END
GO

CREATE CERTIFICATE MyTdeCert
 WITH SUBJECT = 'My TDE Certificate';
GO

With the server-level components in place, the database can now be encrypted.  This is done by first creating the database (symmetric) encryption key within the database and then enabling TDE:

USE TdeDemo;
GO

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

ALTER DATABASE TdeDemo
 SET ENCRYPTION ON;
GO

Database encryption may take a while to complete.  While in progress, the sys.dm_database_encryption_keys data management view will show the database in an encryption_state of 2:

SELECT DB_NAME(database_id) as DB,encryption_state FROM sys.dm_database_encryption_keys WHERE database_id=DB_ID();
GO

DB encryption_state
-------- ----------------
TdeDemo 2

(1 row(s) affected)

 Once TDE encryption has been fully applied, the encryption_state will become 3:

SELECT DB_NAME(database_id) as DB,encryption_state FROM sys.dm_database_encryption_keys WHERE database_id=DB_ID();
GO

DB encryption_state
-------- ----------------
TdeDemo 3

(1 row(s) affected)

Now to demonstrate the protection of database backup files through TDE, backup the database and its certificate.  Please note that these are being backed up locally to the same location.  This is not a secure practice but is expediant for this demo: 

USE master;
GO

BACKUP CERTIFICATE MyTdeCert
 TO FILE = 'c:\temp\MyTdeCert'
 WITH PRIVATE KEY (
  FILE = 'c:\temp\MyTdeCertPrivateKey',
  ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh'
  );
GO

BACKUP DATABASE TdeDemo
 TO DISK = 'c:\temp\TdeDemo.bak'
 WITH INIT;
GO

By dropping the database and the server-level certificate, we can simulate a restore to a different server:

DROP DATABASE TdeDemo;
GO

DROP CERTIFICATE MyTdeCert;
GO

With the certificate missing, the restore operation will fail:

RESTORE DATABASE TdeDemo
 FROM DISK = 'C:\temp\TdeDemo.bak';
GO

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x686A8264E4A17572FBAE6A1D091A47D600847FB6'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

It's not until the certificate is recovered to the server that the backup file can be restored:

CREATE CERTIFICATE MyTdeCert
 FROM FILE = 'c:\temp\MyTdeCert'
 WITH PRIVATE KEY (
  FILE = 'c:\temp\MyTdeCertPrivateKey',
  DECRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh'
  );
GO

RESTORE DATABASE TdeDemo
 FROM DISK = 'C:\temp\TdeDemo.bak';
GO 

Processed 168 pages for database 'TdeDemo', file 'TdeDemo' on file 1.
Processed 2 pages for database 'TdeDemo', file 'TdeDemo_log' on file 1.
RESTORE DATABASE successfully processed 170 pages in 0.157 seconds (8.415 MB/sec).

To reset the environment:

USE master;
GO

DROP DATABASE TdeDemo;
GO

DROP CERTIFICATE MyTdeCert;
GO