Compliance Controls & Advanced Security

Security Feature Architecture Map

Security Feature Map

💾

TDE (At Rest)

Encrypts entire database files + backups with AES-256. Always on by default.

🔐

Always Encrypted (In Use)

Client-side column encryption. DB engine never sees plaintext.

🎭

Dynamic Data Masking

Masks sensitive data in query output. Presentation-layer only.

🔒

Row-Level Security

Filter predicate controls which rows users can access per tenant.

📝

Data Classification

Discovers and labels sensitive columns. Integrates with auditing.

🛡️

Defender for SQL

Vulnerability assessment + threat protection (SQL injection, anomalies).

Data Classification

Discovers, labels, and protects sensitive data columns.

Label	Example Data	Sensitivity
Public	Product names, categories	Low
General	Employee names, department	Medium
Confidential	Email addresses, phone numbers	High
Highly Confidential	SSN, credit card, medical records	Critical

How it works:

1. Azure SQL scans columns using built-in patterns (SSN, credit card, email, etc.)
2. Recommends classifications → you accept or customize
3. Labels are stored as extended properties on columns
4. Integrated with auditing — queries on classified data are logged with sensitivity labels

🎯 Exam Focus

Data Classification is informational — it does NOT enforce access control. It works with Auditing to track who accessed sensitive data. For enforcement, combine with RLS (row filtering) or Dynamic Data Masking (column masking).

Always Encrypted with VBS Enclaves

Standard Always Encrypted has limitations (no LIKE, no range queries). VBS Enclaves solve this.

Always Encrypted with VBS Enclaves

🔐

Standard Always Encrypted

Only equality queries (=). No LIKE, no range, no ORDER BY on encrypted columns.

🛡️

AE with VBS Enclaves

Supports LIKE, range (>, <, BETWEEN), ORDER BY, GROUP BY, in-place encryption inside secure enclave.

Capability	Standard AE	AE with Enclaves
Equality (=)	✅ (deterministic)	✅
LIKE / pattern match	❌	✅
Range (>, <, BETWEEN)	❌	✅
ORDER BY / GROUP BY	❌	✅
In-place encryption	❌ (requires client)	✅ (in enclave)
Index creation on encrypted	❌	✅

🎯 Exam Focus

If the exam says "encrypt columns but still need LIKE or range queries" → Always Encrypted with VBS Enclaves. Standard AE only supports equality comparisons.

Database Ledger

Provides tamper-proof audit trail using cryptographic hashing. Proves data hasn't been modified.

Database Ledger — Tamper-Proof Audit

📝

DML Operation

INSERT, UPDATE, or DELETE on ledger table

Change is recorded alongside data

🔗

Cryptographic Hash

Each transaction gets a SHA-256 hash

Hash chain links to previous transaction

📜

Digest Storage

Hash digest stored externally (Azure Blob, ACL)

Cannot be modified by anyone

✅

Verification

sp_verify_database_ledger validates integrity

Proves data has not been tampered with

Two types:

• Updatable ledger tables — normal DML allowed, but all changes are cryptographically recorded
• Append-only ledger tables — INSERT only (no UPDATE/DELETE)

Use case: Financial records, regulatory compliance, legal evidence where you must prove data integrity.

🎯 Exam Focus

Ledger is NOT encryption — it's integrity verification. It proves data hasn't been tampered with after the fact. For encryption → TDE or Always Encrypted. For access control → RLS. For tamper-proof audit → Ledger.

Change Tracking vs Change Data Capture

Feature	Change Tracking (CT)	Change Data Capture (CDC)
What it tells you	Which rows changed (PK only)	What changed (old + new values)
Performance impact	Low	Medium
Storage	Minimal (PK + version)	Higher (full change records)
Cleanup	Automatic	Automatic (configurable retention)
Async	Synchronous	Asynchronous (reads log)
Available on	SQL DB, MI, VM	MI, VM (SQL DB: limited)
Best for	Sync scenarios (Data Sync)	ETL, auditing, replication

🎯 Exam Focus

CT = "did this row change?" (lightweight, for sync). CDC = "what exactly changed and when?" (detailed, for ETL/audit). The exam tests whether you can choose the right one based on requirements.

Microsoft Purview Integration

Purview provides unified data governance across your entire Azure estate.

Purview Feature	What It Does for SQL
Data Map	Auto-discovers and catalogs SQL databases, schemas, tables
Data Classification	Scans and labels sensitive data in SQL databases
Data Lineage	Tracks data flow from source → transforms → SQL
Access Policies	Enforce access governance from Purview (preview)

Microsoft Defender for SQL — Deep Dive

Feature	What It Detects
Vulnerability Assessment	Weak passwords, excessive permissions, unencrypted sensitive columns, missing TDE
Advanced Threat Protection	SQL injection attempts, anomalous access patterns, brute-force login, data exfiltration
Security Alerts	Real-time alerts to email, Security Center, SIEM

Microsoft Defender for SQL

🔍

Vulnerability Assessment

Scans for weak passwords, excessive permissions, unencrypted columns, missing TDE

🚨

Advanced Threat Protection

Detects SQL injection, anomalous access, brute-force logins, data exfiltration

📊

Security Alerts

Real-time alerts to email, Security Center, and SIEM integration

🎯 Exam Focus

Defender for SQL = Vulnerability Assessment + Advanced Threat Protection in one package. If the exam asks about "detecting SQL injection" → ATP. "Finding misconfigured databases" → Vulnerability Assessment. "Both" → Defender for SQL.

---

Flashcards

🧠 Smart order (weak cards first)

What does Data Classification do?

Click to reveal answer

Discovers and labels sensitive data columns (SSN, credit card, etc.) using built-in patterns. Labels integrate with Auditing to track access to sensitive data. Does NOT enforce access control.

1 / 6

---

Quiz

Q1/3

0 correct

You need to encrypt a phone number column but still allow LIKE '%555%' queries. What feature do you use?