Always Encrypted vs TDE vs dynamic data masking vs row-level security vs Ledger
Verdict: Always Encrypted alone hides plaintext from a db_owner; masking only obscures output for nonprivileged users. Row-level security filters rows, TDE encrypts files at rest, Ledger proves tamper-evidence.
| Criterion | Dynamic data masking | Transparent data encryption | Row-level security | Always Encrypted | Ledger |
|---|---|---|---|---|---|
| Protects | Masks column values in query results | Encrypts database files at rest | Restricts which rows a principal sees | Encrypts chosen columns; key never seen by the engine | Cryptographic proof records were not altered |
| Blocks a full-rights DBA seeing plaintext | No; privileged roles bypass the mask | No; DBA reads the live data | No; controls rows, not column content | Yes; administrators cannot decrypt | Not its purpose |
| Default on a new Azure SQL Database | Off | On, Microsoft-managed key | Off | Off | Off |
| Choose when | Hide PII from support or helpdesk staff | Baseline at-rest encryption; CMK for key custody | Multi-tenant isolation on shared tables | Protect columns from privileged insiders | Tamper-evidence for auditors or partners |
Rules
- TDE is enabled by default on every new Azure SQL Database, with the key protected by a Microsoft-managed server certificate.
- Choose Always Encrypted when administrators with full db_owner rights must never see plaintext; the key is kept outside the database engine.
- Choose row-level security for multi-tenant isolation: predicates filter rows by the query execution context on shared tables.
- Choose a database-level customer-managed key when one database needs its own rotation and revocation control, separate from the logical server.
- Choose Ledger for cryptographic proof that historical records have not been tampered with, presentable to external parties.
- Failed Microsoft Entra sign-ins never reach the database, so they appear only in Entra sign-in logs, not SQL audit logs.
Traps
- Dynamic data masking does not stop a privileged role reading plaintext; it only obscures results for nonprivileged users.
- TDE and customer-managed keys protect files at rest; they do nothing about who can query the live database.
- Eliminating SQL passwords with Entra authentication first requires designating a Microsoft Entra administrator on the server.