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 |
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?