Security Controls Comparison
TDE, Always Encrypted, RLS, DDM, Auditing — five features, five different problems. This page makes it clear what each one does, what it doesn't do, and when to use them together.
For detailed configuration, see Encryption & Network Security and Authentication & Authorization.
The Matrix
| Feature | What It Protects | Encryption? | Who Can Still See Data? | Azure SQL DB | MI | VM | Exam Weight |
|---|---|---|---|---|---|---|---|
| TDE | Data at rest (files, backups) | ✅ AES-256 | Anyone with DB access (decrypted transparently) | ✅ ON by default | ✅ | ✅ | 🎯 High |
| Always Encrypted | Specific columns (in use) | ✅ Client-side | Not even the DBA or SQL engine | ✅ | ✅ | ✅ | 🎯 High |
| RLS | Row-level access | ❌ | Only rows matching your predicate | ✅ | ✅ | ✅ | 🎯 High |
| DDM | Column display (masking) | ❌ | Users with UNMASK permission | ✅ | ✅ | ✅ | 🎯 Medium |
| Auditing | Activity tracking (who did what) | ❌ | Auditors reviewing logs | ✅ | ✅ | ✅ | 🎯 High |
What Problem Does Each Solve?
| Threat / Problem | Feature | How It Helps |
|---|---|---|
| Stolen database files or backups | TDE | Files are encrypted — useless without the key |
| DBA shouldn't see salary/SSN data | Always Encrypted | Data encrypted before reaching SQL engine — DBA sees ciphertext |
| Tenant A shouldn't see Tenant B's data | RLS | Filter predicate hides rows per user/tenant |
| Support staff shouldn't see full credit card numbers | DDM | Shows XXXX-XXXX-XXXX-4321 instead of full number |
| "Who deleted those records?" | Auditing | Logs every query, login, schema change to storage/Log Analytics |
| Compliance requirement for encryption at rest | TDE | Satisfies encryption-at-rest checkbox |
| Compliance requirement for column-level encryption | Always Encrypted | Column never exists in plaintext on server |
| GDPR "right to know who accessed my data" | Auditing | Full access trail per user per query |
Where Does Encryption Happen?
| Feature | Encrypted Where? | Decrypted Where? | Key Location |
|---|---|---|---|
| TDE | Storage layer (data files, log, backups) | SQL engine (transparent to queries) | Server certificate or Azure Key Vault |
| Always Encrypted | Client app (before sending to SQL) | Client app (after receiving from SQL) | Client-side key store (Key Vault, cert store) |
| TLS 1.2 | Network (in transit) | SQL engine / client | Certificate on server |
🎯 Exam Focus
The critical distinction: TDE encrypts data at rest — anyone who can query the database sees plaintext (decryption is transparent). Always Encrypted encrypts data in use — even the DBA and SQL engine see only ciphertext. TDE protects stolen files. Always Encrypted protects from privileged users.
Who Can Still See the Data?
| Role | TDE | Always Encrypted | RLS | DDM |
|---|---|---|---|---|
| DBA / sysadmin | ✅ Sees plaintext | ❌ Sees ciphertext | ✅ Sees all rows (unless filtered) | ✅ Sees unmasked |
| App with correct keys | ✅ | ✅ Sees plaintext | Depends on predicate | ✅ Sees unmasked |
| App without keys | ✅ | ❌ Ciphertext only | Depends on predicate | Sees masked |
| User with UNMASK | ✅ | ❌ | Depends on predicate | ✅ Sees unmasked |
| User without UNMASK | ✅ | ❌ | Filtered rows only | Sees masked |
| Someone with stolen .mdf file | ❌ Encrypted | ❌ Encrypted | N/A | N/A |
| Azure platform admin | ❌ (with BYOK) | ❌ | N/A | N/A |
⚠️ Watch Out
DDM is not security — it's obfuscation. Users with SELECT permission and UNMASK can see full data. Users can also infer values through WHERE clauses (e.g., WHERE SSN = '123-45-6789'). DDM prevents casual viewing, not determined access.
Configuration Complexity
| Feature | Setup Effort | Ongoing Maintenance | Impact on Queries |
|---|---|---|---|
| TDE | 1 min (ON by default in Azure) | None (auto key rotation with service-managed) | Zero — completely transparent |
| Always Encrypted | High (client driver changes, key management) | Key rotation requires planning | Can't use WHERE, JOIN, ORDER BY on encrypted columns (deterministic exception) |
| RLS | Medium (write predicate function + policy) | Update predicate when tenant model changes | Slight overhead on every query |
| DDM | Low (ALTER TABLE ADD MASKING) | Minimal — masks are declarative | Zero overhead |
| Auditing | Low (enable in portal) | Storage management, log review | Minimal overhead (< 5%) |
Always Encrypted: Deterministic vs Randomized
| Encryption Type | Allows Equality Checks? | Allows Range/LIKE? | Use For |
|---|---|---|---|
| Deterministic | ✅ WHERE col = @value | ❌ | Columns you need to search (SSN, email) |
| Randomized | ❌ | ❌ | Columns you only display (salary, notes) |
🎯 Exam Focus
Deterministic Always Encrypted allows equality comparisons (=, IN, GROUP BY, JOIN) because the same plaintext always produces the same ciphertext. Randomized doesn't allow any server-side operations — it's more secure but less functional.
Common Combinations
| Combination | What It Achieves |
|---|---|
| TDE + Auditing | Standard compliance baseline — encryption at rest + full audit trail |
| TDE + RLS | Multi-tenant isolation with encrypted storage |
| TDE + Always Encrypted | At-rest encryption + column-level protection from privileged users |
| RLS + DDM | Row filtering + column masking for defense-in-depth |
| TDE + RLS + DDM + Auditing | Full security stack for regulated industries |
| Always Encrypted + Auditing | Sensitive column protection + activity tracking |
🏢 Real-World DBA Note
Most production deployments use: TDE (on by default) + Auditing (compliance requirement) + RLS (if multi-tenant). Always Encrypted is used selectively for the most sensitive columns (SSN, payment data) because of its query limitations.
Common Exam Traps
| Trap | Reality |
|---|---|
| "TDE protects data from DBAs" | ❌ TDE is transparent — DBAs see plaintext. Use Always Encrypted to protect from DBAs. |
| "DDM prevents data access" | ❌ DDM only prevents casual viewing. UNMASK permission or inference attacks bypass it. |
| "Always Encrypted works with all queries" | ❌ Only deterministic encryption allows equality checks. No LIKE, no range queries, no ORDER BY. |
| "RLS protects data at rest" | ❌ RLS is a query-time filter. Someone with file access bypasses it entirely. Combine with TDE. |
| "Auditing prevents attacks" | ❌ Auditing detects and logs — it doesn't prevent. It's for accountability, not access control. |
| "TDE needs to be turned on manually in Azure SQL DB" | ❌ TDE is ON by default in Azure SQL DB. You only need to act if using BYOK (customer-managed key). |
Decision Guide
| "I need to..." | Use This |
|---|---|
| Encrypt the entire database at rest | TDE (already on) |
| Prevent the DBA from reading salary data | Always Encrypted |
| Show different data to different tenants | RLS |
| Hide credit card digits from support staff | DDM |
| Track who accessed what and when | Auditing |
| Use my own encryption key (not Microsoft's) | TDE with BYOK via Key Vault |
| Protect data in transit | TLS 1.2 (always on) |
| Satisfy PCI-DSS requirements | TDE + Always Encrypted + Auditing + RLS |
Anti-Patterns
- "TDE is enough for compliance." TDE protects files-at-rest, not the live query path. Auditors ask for who saw the data, which is Auditing, not TDE. PCI/HIPAA fail with TDE-only.
- "Always Encrypted on every PII column." Randomized AE breaks
WHERE,ORDER BY,JOIN,LIKE, indexes. Apps that don't know the column key get cryptic conversion errors at runtime. AE is for the handful of columns the DBA must not see, not a blanket policy. - "DDM = data protection." DDM is a presentation mask. Any user with
UNMASKor withSELECTand aWHEREpredicate can infer values. Treat DDM as UI-layer convenience, not a control plane. - "RLS for multi-tenant isolation, no other layer needed." RLS is a query-time
WHEREinjection. A user withdb_owneror direct file access bypasses it. Pair with permission boundaries and per-tenant credentials. - "Turn on Auditing later when we need it." Audit logs are not retroactive. The breach window is exactly the unaudited window. Default-on Auditing to Storage costs cents/month — always cheaper than a forensic gap.
- "BYOK because it's more secure than service-managed TDE." BYOK adds operational risk: rotating, revoking, or losing the Key Vault key takes the database offline. Use BYOK only when required by policy, not as a default upgrade.
⚠️ Watch Out
Always Encrypted with Secure Enclaves (VBS) is the only way to do range queries / pattern matching on encrypted columns. Plain Always Encrypted does not support these and never will. If a question allows WHERE col LIKE '%x%' on encrypted data, the answer is enclaves, not classic AE.
Migration Between Controls
| From → To | Path | Cost |
|---|---|---|
| Service-managed TDE → BYOK (Key Vault) | Add CMK, switch protector | Online; rotation now your responsibility |
| BYOK → Service-managed TDE | Switch protector back to service-managed | Online; reduces operational burden |
| Plaintext column → Always Encrypted (deterministic) | Drop indexes incompatible with AE; encrypt via SSMS / Set-SqlColumnEncryption | Application code must use AE-aware driver; offline window during column rewrite |
| Always Encrypted (deterministic) → Randomized | Re-encrypt column with new type | Loses equality search; query rewrites required |
| Always Encrypted → Always Encrypted with Enclaves | Move to DC-series VM / SQL DB BC; configure attestation | Adds attestation infra + VBS-capable hardware |
| App-level encryption → Always Encrypted | Decrypt + re-encrypt under AE keys | One-shot bulk operation; app driver swap |
| DDM → RLS for true row isolation | Add security predicate; remove DDM | Functional change — users now see fewer rows, not masked rows |
| No auditing → Auditing to Log Analytics | Enable Diagnostic Setting | Pennies/GB; gain KQL queryability |
Most moves are online. The expensive ones are plaintext → AE (rewrite + index drop) and classic AE → Enclaves (infra change).
Real Scenarios
- Healthcare app, HIPAA audit → TDE (default) + Auditing to Log Analytics + RLS for clinic isolation + Always Encrypted on SSN column. Driver: separation of duties — DBA can't read SSN. Trade-off: SSN column unsearchable.
- Multi-tenant SaaS, 1,000 tenants in one DB → RLS + per-tenant connection users + DDM on PII for support staff. Driver: tenant queries naturally filtered. Trade-off: complex security predicate, tested under load.
- Financial app needing range queries on encrypted amounts → Always Encrypted with Secure Enclaves on BC tier. Driver: only enclave path supports
WHERE amount BETWEEN. Trade-off: BC tier cost + DC-series compute. - Legacy app, no driver upgrade possible → TDE + DDM + Auditing (no AE). Driver: can't change client. Trade-off: DBA still sees plaintext — mitigate via just-in-time admin access (PIM).
- Net-new greenfield app → Service-managed TDE + Auditing + Microsoft Entra-only auth + Private Endpoint. Driver: max security with min ops. Trade-off accepted: defer BYOK until policy demands it.
Flashcards
What does TDE protect against?
Click to reveal answer
Stolen database files and backups. TDE encrypts data at rest with AES-256. It does NOT protect against authorized users querying the database — decryption is transparent.
1 / 8
Quiz
A company needs to ensure that even their DBAs cannot read employee salary data stored in Azure SQL Database. Which feature should they use?