How To Use Transparent Data Encryption To Increase Your Security

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.

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  
 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'a_strong_password'
go  
 
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'
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  
 
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO  
 
ALTER DATABASE myDB  
SET ENCRYPTION ON
GO  

SQL Code to monitor TDE

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

SELECT name AS certificate_name,
pvt_key_encryption_type_desc AS pvt_key_encryption,
thumbprint
FROM master.sys.certificates 

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 [name], is_master_key_encrypted_by_server, is_encrypted
FROM master.sys.databases

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 

Encrypting data in SQL Server - SQL Server DBA

!