TDE vs Always Encrypted in SQL Server: What Each One Actually Protects

Access control (previous lesson) governs who’s allowed to query what. Encryption is a completely different layer: what happens if someone gets to the raw data anyway — a stolen backup, a compromised disk, or even a DBA whose access exceeds what they should see. These get confused in interviews constantly, because both are “encryption,” but they solve genuinely different threats.

The Key Distinction

The Interview-Ready Answer

TDE protects against physical theft of files — a stolen backup tape, a compromised disk, a leaked VM snapshot — but a legitimate, authenticated query still sees plaintext once it’s running against the live, unlocked database. A DBA with query access sees everything, TDE or not. Always Encrypted protects against exactly that scenario: even someone with full server access querying the table directly sees only ciphertext for protected columns, because decryption happens in the client driver using a key the server itself never holds.

-- Even a sysadmin querying a table directly on the server sees ciphertext
-- for an Always Encrypted column — there's no server-side bypass:
SELECT ssn FROM dbo.Employee; -- returns encrypted binary garbage, not the real SSN,
-- UNLESS the querying client has the column master key configured

-- Contrast with TDE: the exact same query, on a TDE-protected database,
-- returns the real value — TDE is invisible to queries, only protects the files at rest

Which One for Which Data?

Sensitive columns like SSNs typically call for Always Encrypted — the goal there is usually protecting against insider visibility, not just physical theft. TDE is a reasonable baseline for the whole database regardless, since it’s essentially free once configured (transparent, as the name says — no query or application changes needed) and closes the physical-theft gap that Always Encrypted alone doesn’t address for unprotected columns. They’re complementary, not substitutes: a well-secured sensitive database often runs both simultaneously.