Transparent Data Encryption (TDE) encrypts SQL Servers, Azure SQL Databases, and Azure Synapse Analytics (SQL DW) data files, and is also known as “encrypting data at rest.” This includes Backups, TEMPDB, Data and Log files. The encryption and decryption of the Data and Log files is performed during real-time I/O. In order to encrypt the data, TDE uses a Database Encryption Key (DEK), which is stored in the database boot record that is available during recovery. In this blog, we describe how to setup TDE on SQL Server and provide an in-depth explanation of the behind-the-scene process that occurs when databases are being encrypted.
TDE provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.
How to Setup TDE on a Server
There are 4 major steps for implementing TDE:
- Create a Database Master Key
use master; go; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Myp@sswordString’; go;
- Create a Certificate
use master; go; CREATE CERTIFICATE MyCertificateDEK WITH SUBJECT = 'My DEK Certificate’; go;
- Create a Database Encryption Key in the User database to encrypt. The DEK is a symmetric key secured by using a Certificate stored in the master database of the server or an asymmetric key protected by an EKM module
use MyDatabaseForTDE; go; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyCertificateDEK go;
- Enable the SQL Server database for Encryption
ALTER DATABASE MyDatabaseForTDE SET ENCRYPTION ON GO;
- When setting up TDE the following actions should be taken:
- Perform regular backups of the Certificates WITH the PRIVATE KEY option
- Use a third-party tool or an in-house solution for Key Management
TDE – A Look Under the Hood
When TDE is enabled, SQL Server checks different settings, such as the creation of the DEK, the SQL server edition, and the presence of Read Only File groups, and uses a background process to encrypt the database. The background process uses shared locks to read data into memory and write it back to the disk after encryption.
Encryption is done in I/O path – RE-ENCRYPTION SCAN/ ENCRYPTION SCAN. To encrypt data files, the page level encryption is used. In one pass, the SQL Server encrypts 32 pages; encryption and checkpoints occur every 1024 pages. The background process sleeps for 250ms for every 32 pages and can be paused using Trace flag 5004.
The Checksum method is used when encrypting page headers and data on the page. It is calculated and saved in the header before encryption. Second, Checksum is used to check the decrypted data.
When records are already written to the log, they are not encrypted when the encryption process starts. The next active VLF will be encrypted. For every 4 encrypted extents (32 pages), an extra log record is added to the log file.
As soon as one database is encrypted on a server, the TEMPDB database is encrypted using the AES_265 algorithm. The only way to decrypt TEMPDB is to restart the SQL Server Instance after disabling Encryption and decrypting all user databases. This will prompt the TEMPDB to be recreated without encryption.
Considerations for a Successful Implementation
In a high availability setup, the recovery time is single threaded and, hence, will take longer. There will be an impact on tools, such as Utilities, that scan transaction logs. It is recommended to implement TDE during low or no business hours and to only do so on servers with enough CPU to support TDE.
One should expect to see slower performance of queries on encrypted databases. Performance degradation of 2-3% under normal CPU utilization, and approximately 30% under 100% CPU utilization is expected. When the data is out of sync, check for Certificates on the Secondary/Mirror server. We should expect significant overhead due to the log record for encryption.
Summary
Besides these considerations and performance issues for a few cases, Transparent Data Encryption is a technology widely employed by Microsoft, IBM and Oracle to encrypt database files for relational databases.
TDE solves the problem of protecting data at rest, encrypting databases on the hard drive and, consequently, on backup media with a pretty straightforward setup process. It does not protect data in transit nor does it protect data in use. Enterprises typically employ TDE to solve compliance issues such as PCI DSS which require the protection of data at rest. This ensures that a hacker cannot simply read data off physical data files.