SQL Server › Transparent Data Encryption

What is the difference between what Transparent Data Encryption protects against compared to Dynamic Data Masking?

Beginner

Answer

Transparent Data Encryption protects data at rest, meaning the physical files on disk, guarding against theft of the actual database or backup files, while Dynamic Data Masking protects against unauthorized viewing of sensitive data by legitimate users who are already properly querying the database, meaning the two features address entirely different security concerns and are often used together.

-- TDE protects the physical files
ALTER DATABASE MyDatabase SET ENCRYPTION ON;

-- Dynamic Data Masking protects what authorized query users see
ALTER TABLE Customers ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

Real-world example A financial company uses Transparent Data Encryption to protect their database files from being stolen and read directly, while separately using Dynamic Data Masking to limit what sensitive data regular employees can see when they run normal queries.

Common follow-ups: Can TDE and Dynamic Data Masking be used together on the same database?;Which of these two features would protect against a stolen laptop containing a database backup?

SQL Server Security & Permissions;Backup & Recovery