Azure SQL Security + Defender for Databases

Azure SQL security in one module

Simple explanation

Azure SQL covers Azure SQL Database (single DB + elastic pool, PaaS), Azure SQL Managed Instance (near-100% SQL Server compat, PaaS), and SQL Server on Azure VMs / Arc. Plus a Defender for Databases plan that also covers open-source databases (PostgreSQL, MySQL).

SC-500 testable layers:

Authentication — Microsoft Entra-only (preferred) vs mixed-mode with SQL auth users
Network — server firewall, VNet service endpoint, Private Endpoint, public endpoint disabled
Encryption — Transparent Data Encryption (TDE) on by default with platform-managed key, customer-managed TDE via Key Vault, Always Encrypted (column-level), dynamic data masking
Auditing — server-level and database-level audit policies sending to Log Analytics / Storage / Event Hub
Defender for Databases — detects SQL injection, anomalous queries, brute-force, suspicious access; per-database billing

Authentication: Entra-only is the SC-500 default

The right answer:

Set a Microsoft Entra admin on the logical server (a user, group, or service principal — group is best practice for survivability).
Set Entra-only authentication on the server to Enabled — disables SQL auth users.
Create contained users mapped to Entra principals via CREATE USER [<entra-name>] FROM EXTERNAL PROVIDER and grant minimum DB roles (db_datareader, db_datawriter).
Consuming apps authenticate using their managed identity via the Azure Identity SDK (DefaultAzureCredential) + connection string with Authentication=Active Directory Default;....

SQL auth users are blocked entirely once Entra-only is on. The result: every connection is attributable to an Entra principal, MFA / Conditional Access applies, no passwords in app config.

Network hardening

Azure SQL network settings — Private Endpoint + public access disabled is the SC-500 default
Network setting	What it does	When it's the right answer
Public network access: Disabled	Server reachable only via Private Endpoint	Production — combined with PE in consumer VNet
Private Endpoint	Private IP in customer VNet via `privatelink.database.windows.net`	Highest assurance
VNet service endpoint	Subnet-level allowance via `Microsoft.Sql` service endpoint + firewall rule on the server referencing the subnet	Middle ground where Private Endpoint is overkill
Server firewall — IP rules	Explicit public CIDR allowlist	Legacy compatibility; combine with other layers
'Allow Azure services and resources to access this server'	Allows ALL Azure first-party services on Microsoft IP space — broad and often misunderstood	Almost never the right exam answer; the trap setting

TDE, Always Encrypted, dynamic data masking

Azure SQL encryption + masking — TDE for at-rest, Always Encrypted for column-level, masking for display
Feature	What it encrypts/masks	Key custody
Transparent Data Encryption (TDE)	Data files at rest (storage layer); transparent to the application	Service-managed (platform key) or customer-managed (Key Vault) — `db_owner`+ controls
TDE with customer-managed key (BYOK)	Same as TDE; the wrapping key lives in customer Key Vault	Customer (Key Vault, optionally HSM-backed); rotation policy via KV
Always Encrypted	Specific columns — encrypted in the client driver before reaching the engine	Customer (Column Master Key in Key Vault); the engine never sees plaintext
Always Encrypted with secure enclaves	Same as Always Encrypted, plus comparison/range/LIKE operations supported in a TEE on the server	Customer (CMK in KV) + TEE attestation
Dynamic data masking	Display-time masking for non-privileged users; underlying data unchanged	N/A — no encryption; mask defined per column

The SC-500 exam pattern: when a scenario says “the application or DBA must NEVER see the plaintext”, the answer is Always Encrypted (the engine never sees plaintext either). When the scenario says “protect data at rest with customer keys”, the answer is TDE with customer-managed key. When the scenario says “the data should look masked to non-privileged users but unchanged underneath”, the answer is dynamic data masking.

Auditing

Azure SQL auditing logs events at the server or database level. Destinations:

Log Analytics workspace — Sentinel-ready; queryable with KQL via the SQLSecurityAuditEvents table
Storage account — long-term archive
Event Hub — real-time streaming to third-party SIEMs or custom processors

Set a server-level audit policy covering all DBs as the baseline; per-DB policies layer on for noisy DBs needing finer control. Audit immutability via Storage account immutability policies.

Defender for Databases

The plan covers Azure SQL DB, Managed Instance, SQL Server on VM/Arc, and PostgreSQL/MySQL flexible servers. What it detects:

SQL injection patterns
Anomalous client login (atypical user, location, application name)
Brute-force attempts on the SQL endpoint
Vulnerability assessment — Defender for SQL VA runs a periodic scan, surfacing misconfigurations and weak baselines
Data exfiltration patterns

Plan enabled per subscription or per database (granularity depends on database type). Alerts route to Defender for Cloud → Defender XDR → Sentinel.

Scenario: Esme hardens the bank’s core payments database

Entra-only authentication enabled on the logical server. Entra admin = Payments-DBAdmins group (PIM-eligible).
Network: public network access disabled. Private Endpoint in the payments VNet.
TDE with customer-managed key in HSM-backed Key Vault; annual rotation.
Always Encrypted on customers.card_number and customers.ssn columns; Column Master Key in HSM-backed Key Vault; the engine never sees plaintext.
Dynamic data masking on customers.email and customers.phone for non-privileged users.
Server-level audit policy to Log Analytics workspace shared with Sentinel.
Defender for Databases enabled. Vulnerability assessment scheduled weekly; alerts to Sentinel.

The auditor sees: every connection attributable, no SQL auth users exist, sensitive columns encrypted client-side, masking for non-privileged read paths, full audit trail in Sentinel, threat detection on.