A guide to setup TDE for SQL Server

In addition to a general database policy and practices of securing databases such as : Security architecture, Asset encryption ,Firewalls & Regular Audits , there is an option to apply Transparent Data Encryption (TDE) to a SQL Server database  - aka "data at rest encryption". TDE  is about securing data in the situation of backup device\drive theft or adhoc backup file protection.

ANSI standard for Key security uses a key hierarchy from the top to protect everything . Ultimately - the question remains how to protect the key that scures the data encryption key?

Broadly - these are 3 main choices 

Method 1 : As part of the SQL Server TDE architecture - a number of elements are already set up - such as the Service Master Key . The Windows Operating System Level Data Protection API (DPAPI) encrypts the service master key , which in turn is used to encrypt the Database Master Key for the master database.    When you execute the CREATE MASTER KEY statement , the Service Master Key is used to encrypt the Database master key.  The code below follows this model 

The main problem with this method - is that any Administrator can encrypt\decrypt the keys

Method 2 : Applying passwords to encrypt any key in the hierarchy. Main advantage is you can control who has access to different parts of the hierarchy . Main disadvantage is you take on the burden of manage the passwords

Method 3: EKM  (Extensible Key Management).  i.e securing the the keys to a third-party hardware appliance aka HSM.The HSM can replace the key hierarchy management - described in Method 1 - or can be supplementary.  SQL Server EKM was introduced in 2008 . 

Setting up TDE in SQL Server is in summary 

1. Create a master key.

2. Create or obtain a certificate protected by the master key.

3. Create a database encryption key and protect it by using the certificate.

4. Set the database to use encryption

If managing a large amount of databases - especially  if fulfilling daily database backups & restores or other database refreshes , key management is critical to the smooth operation. Develop a process to make keys available for restores. 

This SQL Code is a used to  create certificates and apply TDE to SQL Server databases.  

use master
go
CREATE DATABASE myDB;

USE master
GO  
--Service Master Key encrypts the Database Master Key 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'a_strong_password'
go  
--master database key creates a certificate in the master database 
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'
go 
--check certificates
GO


-- export the backup certificate to a file
BACKUP CERTIFICATE MyServerCert TO FILE = 'h:\MyServerCert.cert'
WITH PRIVATE KEY (
FILE = 'h:\MyServerCert.key',
ENCRYPTION BY PASSWORD = 'My!!Strong123478Cert')
GO 
 

USE myDB
GO  
--creates a key that encrypts a database
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO  
--the ALTER DATABASE option used to enable TDE
ALTER DATABASE myDB  
SET ENCRYPTION ON
GO  

--HOW TO REMOVE TDE----- 
--Turn OFF TDE
ALTER DATABASE MyDB SET ENCRYPTION OFF
GO
 
--Remove database encryption key
USE MyDB
GO
DROP DATABASE ENCRYPTION KEY
GO
 
--Remove certificate
USE master
GO
DROP CERTIFICATE MyServerCert
 
--Remove master key
USE master
GO
DROP MASTER KEY
GO

***Note: The encryption from the tempdb database will be removed after restarting the SQL Server service 

SQL Code to monitor TDE

SELECT [name]
, [principal_id]
, [algorithm_desc]
, [create_date]
from master.sys.symmetric_keys

--list the certificates
SELECT name AS certificate_name,
pvt_key_encryption_type_desc AS pvt_key_encryption,
thumbprint,
expiry_date
FROM master.sys.certificates 

--list certificates with related database encryption keys 
USE master
GO
SELECT
          DatabaseName    = DB_NAME(dek.database_id),
          dek.encryption_state,
          CertificateName = cer.name,
          cer.expiry_date
                ,*
FROM
          sys.dm_database_encryption_keys AS dek
     JOIN sys.certificates                AS cer
         ON dek.encryptor_thumbprint = cer.thumbprint;


SELECT DB_NAME(database_id) AS database_name,
key_algorithm,
key_length,
encryptor_type,
encryptor_thumbprint
FROM sys.dm_database_encryption_keys

SELECT DB_NAME (e.database_id) AS DatabaseName,
e.database_id,
e.encryption_state,
CASE e.encryption_state
WHEN 0 THEN 'No database encryption key present, no encryption'
WHEN 1 THEN 'Unencrypted'
WHEN 2 THEN 'Encryption in progress'
WHEN 3 THEN 'Encrypted'
WHEN 4 THEN 'Key change in progress'
WHEN 5 THEN 'Decryption in progress'
END AS encryption_state_desc,
c.name,
e.percent_complete
FROM sys.dm_database_encryption_keys AS e
LEFT JOIN master.sys.asymmetric_keys AS c
ON e.encryptor_thumbprint = c.thumbprint

SELECT 
d.name,
d.is_encrypted,
dek.encryption_state,
dek.percent_complete,
dek.key_algorithm,
dek.key_length
FROM
sys.databases as d
INNER JOIN sys.dm_database_encryption_keys AS dek
ON d.database_id = dek.database_id

A common question when designing a TDE enabled database is the performance impact. Read How to measure Transparent Data Encryption (TDE) Performance   

Read more on database encryption 

A guide to restore a TDE database backup to another SQL Server Instance

Impact on TDE encryption in progress on SQL Server restart

Tested TDE with Instant File Initialization on SQL Server databases

Encrypting data in SQL Server - SQL Server DBA