Compliance Controls & Advanced Security
Security Feature Architecture Map
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:
- Azure SQL scans columns using built-in patterns (SSN, credit card, email, etc.)
- Recommends classifications → you accept or customize
- Labels are stored as extended properties on columns
- Integrated with auditing — queries on classified data are logged with sensitivity labels
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
Full coverage — enable order, key hierarchy, attestation, in-place encryption, troubleshooting — lives on the dedicated Always Encrypted with VBS Enclaves page. Don't memorize it twice.
One-line decision: "Need LIKE / range / ORDER BY on encrypted columns" → AE with VBS enclaves. Standard AE = equality only.
Database Ledger
Provides tamper-proof audit trail using cryptographic hashing. Proves data hasn't been modified.
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.
Ledger setup — enable order
Digest storage must be immutable (Blob with time-based retention policy or Azure Confidential Ledger). If the destination is writable by an admin, the tamper-proof guarantee is broken — the auditor will reject it.
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 |
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.
CDC enable order (SQL MI / SQL Server)
You must enable CDC on the database first (sp_cdc_enable_db) before enabling on any table. Without it, sp_cdc_enable_table fails. On SQL Server (not Azure), SQL Agent must be running — the capture job won't start otherwise.
Change Tracking enable order (lighter‑weight)
SQL Auditing — enable order
If you turn on auditing before the server identity has write access to the destination, audits are silently dropped — no error in the portal. Always grant the role first.
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 |
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.
Anti-Patterns
- "Defender for SQL only on production." Fine for cost control — but dev/test where developers keep prod-like data is exactly where data exfil happens. Score the risk per-environment, not just per-tier.
- "Vulnerability Assessment baseline = clean = secure." VA flags misconfigs. "Baseline approved" only means you accepted the current state, not that the state is secure. Re-baseline whenever schema or roles change.
- "Data Classification = data protection." Discovery + labeling only. It does NOT enforce access. Pair labels with Auditing (
SENSITIVITY_CLASSIFICATIONcolumn in audit logs) and DDM/RLS to act on the labels. - "Auditing to Storage is enough." Storage retention is cheap; querying it at scale is painful. Pair Storage with Log Analytics (or Event Hub → SIEM) so SOC can KQL across all DBs.
- "Compliance Manager score = compliance." It's a posture indicator, not an audit. The auditor wants evidence — audit logs, change records, key rotation history. CM scores guide the gap, not the certificate.
- "Disable Defender alerts that are noisy." Tune them, don't disable. Disabled alerts come back during audits as un-evidenced risk acceptances.
Defender for SQL on Azure SQL DB and on SQL Server (via Arc) are billed separately. Same product family, two SKUs. A 200-server estate that's 50 % PaaS, 50 % VM is two line items in the invoice — plan budget accordingly.
Migration Between Compliance Postures
| From → To | Path | Cost |
|---|---|---|
| No Defender → Defender for SQL | Toggle plan in Defender for Cloud per scope | Per-server billing; gain VA + ATP + alerts |
| Defender on Azure SQL DB → also on SQL VM via Arc | Arc-enable SQL VMs + enable Defender plan | Two SKUs to track; unified portal view |
| Storage-only auditing → Storage + Log Analytics | Add LA destination at server level | Adds ingest cost; gain KQL + Sentinel ready |
| Manual VA review → VA + Express Configuration | Toggle Express config; baselines auto-applied | Easier ops; less granular per-finding tuning |
| Email alerts → Sentinel rules | Connect Defender for SQL to Sentinel; build analytics rules | Adds Sentinel cost; gain orchestration + playbooks |
| Defender for SQL alone → Defender for SQL + Defender for Endpoint | Enable MDE on host VMs; correlation appears | Doubles cost; gains host-level forensics |
| No data classification → Data Classification + auto-labeling | Auto-label via VA; review labels quarterly | Free feature; ops cost = label review |
| Per-server config → Defender plan via Azure Policy | Assign policy at MG scope | Drift prevention; standardized posture |
Most expensive moves: adopting Sentinel on top (orchestration tier) and rolling out Defender for Endpoint alongside (host-level posture).
Real Scenarios
- Regulated bank, SOC 2 + PCI → Defender for SQL + VA Express + Auditing to LA + Sentinel rules + automated playbook to disable suspicious logins. Driver: detect + respond. Trade-off: Defender + LA + Sentinel = three line items.
- Hospital with PHI → Defender for SQL + Data Classification with PHI labels + Auditing to Storage (cold) and LA (hot 30d) + Always Encrypted on labeled columns. Driver: HIPAA + breach detection. Trade-off: complexity in alert tuning.
- Mid-market SaaS, cost-conscious → Defender for SQL on prod only + Auditing to Storage + quarterly VA review. Driver: 80 % posture for ~20 % cost. Trade-off: dev/test gaps must be compensated by network controls.
- Government tenant, key custody mandate → Defender for SQL + BYOK TDE + Auditing to LA in same region as workload + audit log retention 7y in immutable storage. Driver: regulator audit-ability. Trade-off: cross-region restrictions on the LA workspace.
- Compliance Manager dashboard → evidence collection → Defender + Audit + Policy assignments scored in CM as evidence sources. Driver: pre-audit readiness. Trade-off: ongoing curation; not zero-effort.