SQL Server Transparent Data Encryption

Transparent Data Encryption is introduced in SQL Server 2008. This feature has been introduced to provide more security to the data stored at the database level. It has the most valuable features which will help secure data by help of exploiting multiple CPUs and less increase in the disk space used to save encrypted data.

Why Transparent Data Encryption?

•  Companies which maintain sensitive data should comply to more stringent security standards.
•  Data Encryption should be transparent to the applications using the database.
•  Encryption should be extended to the data at rest.
•  TDE helps with real time I/O encryption of data and log files.

How Transparent Data Encryption Works

TDE is enabled by using ALTER DATABASE command. After running the Alter SQL Server performs basic checks such as Edition Check, Read-only Filegroups, and presence of DEK etc. It returns immediately with success message but the database is not completely encrypted. A background process with shared lock runs to encrypt the database. Encryption is done in I/O path – RE-ENCRYPTION SCAN/ ENCRYPTION SCAN. All the data is read into memory and written back after encrypting. SELECT * FROM sys.dm_exec_requests returns command type “ALTER DATABASE E” and status “background process”.

The encryption status is stored at regular intervals to recover in case of server restart. TDE related DDLs do not work when any of the Filegroups is in Read-Only mode. Data files are encrypted at page level. 32 pages encrypted in a single go and checkpoint for every 1024 pages is issued. The process sleeps for 250ms for every 32 pages.

TDE works on the concepts of second checksum. Page header is also encrypted and checksum is for the encrypted data in the page. It is calculated and saved in header before encryption. Second checksum is used to check the decrypted data.

TDE Encryption does not encrypt already written log records in log files. Granularity of encryption in log files is at VLF level. This means that once the TDE is implemented the next active VLF will be encrypted. An overhead of one log record for every 4 extents (32 pages) encrypted is added to the log file.

TEMPDB is encrypted when any database is encrypted. TEMPDB Encryption is always with AES_265 algorithm. Encryption once done on TEMPDB stays even after Encryption is disabled on all user databases. Only TEMPDB system DB can be encrypted. Encryption Scan cannot be rolled back. Background encryption process can be pause using Trace Flag 5004 and slowed down using Trace Flag 5005.

Implementation of TDE

Below is a demo to implement TDE.

1. Creating Database Master Key:

use master;
go;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Myp@ssword123’;
go;

2. Creating a Certificate:

use master;
go;
CREATE CERTIFICATE MyCertDEK WITH SUBJECT = 'My DEK Certificate’;
go;

3. Backup the Certificate:

BACKUP CERTIFICATE MyCertDEK TO FILE = 'path_and_file_name.cer'
WITH PRIVATE KEY ( FILE = 'C:\MyCertDEK_Bak.pvk' ,
ENCRYPTION BY PASSWORD = 'Myp@ssword456' );
Go;

4. Create a Database to implement TDE

Create Database MyDBForTDE
GO;

5. Create Database Encryption Key in the User DB

use MyDBForTDE;
go;
               
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyCertDEK
go;

6. Enable the DB for Encryption

ALTER DATABASE MyDBForTDE
SET ENCRYPTION ON
GO;

Impact of TDE

You have to note these important points when you are going to implement TDE.

• Backup/Restore and Attach/Detach

Certificate must be backup with DB backup

Target server should have database master key and Certificate used for encrypting DEK on source

Backup compression has little or no effect

Third party backup tools which use log scan will break

Database compression is effective as it reduces I/O

• Key Management

Certificate change is faster as it encrypts only DEK

ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE  []

Regenerate DEK

ALTER DATABASE ENCRYPTION KEY REGENERATE WITH ALGORITHM = AES_128

• High Availability

No effect on Clustering and Replication

Mirroring and Log shipping –

• Create Database Master Key

• Backup and restore Certificate from Primary/Principal to Secondary/Mirror

•  Recovery Process

Recovery Process is single threaded

Recovery time is longer

Impact on Tools

Utilities that scan Transaction Logs will fail

Inbuilt functions that use log and VDI are not affected

Issues with TDE

Below are few issues you may see when you implement TDE.

High CPU Utilization – Expect high CPU utilization when you are going to implement TDE. We are gaining the benefits of security by exploiting CPU. It is recommended to implement TDE during low or no business hours and only on servers with more CPU to exploit. When you see CPU spikes as soon as you implement TDE check encryption status by using the DMV, sys.dm_database_encryption_keys. You can also use trace flags 5004 (pause background encryption process), 5005 (Slow down background encryption process)

Slow Query Performance – Expect to see slow performance of query on unencrypted databases. Performance degradation of 2-3% under normal CPU utilization and ~30% under 100% CPU utilization is expected.

High Disk Space Utilization – Check if snapshots are running for the database. Large database encryption can increase log file size significantly as the encryption process of whole database is logged.

Backup Restore Issues – Database Master key already exists on target server. There may be a missing certificate. Certificate must be backup using WITH PRIVATE KEY option. Changed DEK must be created on destination which is changed on source between log records.

Cannot Encrypt Database – Check the Edition of SQL Server and any read only file groups. Check the DMV sys.dm_database_encryption_keys to see if encryption is already enabled.

Mirroring and Log Shipping – When the data is out of Sync, Check for certificates on Secondary/Mirror server. Expect significant overhead due to log record for encryption.

Happy Learning.

Regards

Manohar Punna

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook