Transparent Data Encryption (TDE) uses database encryption symmetric key (DEK) for the purpose of encryption and decryption. DEK is stored in master databased secured by certificate or asymmetric key. TDE provides the ability to encrypt the data at root levels meaning data and log files. This way users can encrypt the data without modifying the design of existing applications. TDE does not affect the size of the database. Size of the database remains same with or without TDE encryption. for TDE enabled database, backup files are also encrypted using DEK. So, during the restore process, certificate protecting database must be available. Care must be taken to backup server certificates on regular basis.

Following are the important steps in configuring the Transparent Data Encryption (TDE).

1. Create a Master Key
2. Generate a certificateprotected by master key.
3. Create a database encryption key (DEK) protected by certificate.
4. Enable the TDE encryption for selected database.

Here is an example for enabling the encryption on database Northwind using a certificate named MySQLCertificate.

USE Master;
GO
 
--Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password1';
GO
 
--Create Certificate
CREATE CERTIFICATE MySQLServerCertificate WITH SUBJECT = 'My SQL Server Certificate'
GO
 
USE Northwind
GO
 
--Create Database Encryption Key (DEK)
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY SERVER CERTIFICATE MySQLServerCertificate
GO
 
-- Enable the TDE Encryption for the database
ALTER DATABASE Northwind
SET ENCRYPTION ON
GO