SQL Server › Transparent Data Encryption

What is Transparent Data Encryption in SQL Server, and what specifically does it protect?

Beginner

Answer

Transparent Data Encryption, often called TDE, encrypts an entire database's data and log files at rest on disk, protecting against the scenario where someone gains physical access to the actual database files or backup files and tries to read them directly, without requiring any changes to the application querying the database normally.

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

ALTER DATABASE MyDatabase SET ENCRYPTION ON;

Real-world example A healthcare company enables Transparent Data Encryption on their patient records database, ensuring that even if a backup file were somehow stolen, the data within it would remain completely unreadable without the proper encryption keys.

Common follow-ups: Does TDE protect data while it is being actively queried by an authorized user?;What performance impact does enabling TDE typically have?

Backup & Recovery;Dynamic Data Masking