Database Security
TDE · CMK · Always Encrypted · Masking · RLS · Ledger · Auditing
Practice questions for this topic →
Pick the control
| Control | Protects against | Notes |
|---|---|---|
| TDE (service-managed) | Stolen files and backups at rest | On by default for new Azure SQL databases; a restore, copy or geo-replica of an encrypted source is encrypted, an unencrypted source's targets aren't by default |
| TDE with customer-managed key | Key custody, revocation, separation of duties | Key in Key Vault (FIPS 140-2 Level 2) or Managed HSM (Level 3); server-level protector, database-level key also available |
| Always Encrypted | DBAs and cloud operators seeing plaintext | Client-side; keys never exposed to the Database Engine |
| Dynamic data masking | Non-privileged users seeing full values in results | Not encryption; privileged users see data |
| Row-level security | Users seeing other tenants' rows | Predicate-based filtering |
| Ledger | Undetected tampering | Cryptographic tamper-evidence |
| Auditing | Who did what | To Log Analytics, Storage, Event Hubs |
| Defender for SQL | SQL injection, anomalous access, vulnerabilities | Threat detection + vulnerability assessment |
| Entra-only authentication | SQL passwords | Set an Entra admin, disable SQL auth |
Always Encrypted in detail
| Topic | Detail |
|---|---|
| Two keys | Column encryption key encrypts the data; column master key (in a trusted key store: Azure Key Vault, HSM or Windows certificate store) encrypts the column encryption key |
| In the database | Only key metadata and the encrypted values of the column encryption keys; never plaintext keys |
| Without secure enclaves | No sorting or filtering on encrypted data, except point lookups with deterministic encryption |
| With secure enclaves | Adds pattern matching, comparison operators and in-place encryption inside a protected memory area |
Exam Tips
Tick each tip once you can answer it without looking.
“DBAs must never see credit card numbers in plaintext” → Always Encrypted. Trap: TDE doesn't hide data from DBAs; masking doesn't stop privileged users.
“Support staff should see only last 4 digits of SSN” → Dynamic data masking.
“Encrypt data at rest with keys the company controls and can revoke” → TDE with customer-managed key (Key Vault/Managed HSM).
“Each tenant sees only its own rows in a shared table” → Row-level security.
“Prove to auditors records weren't altered” → Ledger tables.
“Detect SQL injection attempts” → Microsoft Defender for SQL.
“Eliminate SQL authentication passwords” → Microsoft Entra-only authentication (managed identities for apps).
“Key Vault holding the TDE protector must not lose the key” → Enable soft delete + purge protection.
“Always Encrypted queries without secure enclaves” → Only point lookups with deterministic encryption; no sorting or filtering otherwise. Trap: Secure enclaves add pattern matching, comparison operators and in-place encryption.
“Where Always Encrypted keys live” → Column master key in a trusted store (Key Vault, HSM or Windows certificate store); the database holds only key metadata and encrypted column keys. Trap: The column encryption key encrypts data; the column master key encrypts that key.
“Restored or copied database from an unencrypted source” → Not encrypted by default; targets inherit the source's TDE state. Trap: New Azure SQL databases are encrypted by default with service-managed TDE.