L7. Securing Azure SQL: Auditing, Platform Controls & Defender for Databases

Platform-Level Security Configurations

Azure SQL Database and Azure SQL Managed Instance share several platform-enforced protections:

• Transparent Data Encryption (TDE): encrypts the data and log files at rest automatically, enabled by default, using either a Microsoft-managed key or a customer-managed key in Key Vault for organizations needing control over key rotation and revocation
• Always Encrypted: goes further than TDE by encrypting specific sensitive columns *client-side*, so even a database administrator with full access to the server never sees plaintext values, only ciphertext
• Dynamic Data Masking: obscures sensitive column data (like a full credit card number) from non-privileged query results in real time, without altering the stored data, useful for limiting exposure to support staff or reporting users who need query access but not the raw value
• Row-Level Security: restricts which rows a given user can see or modify, enforced by the database engine itself rather than relying on every application query to filter correctly

Authentication: Enforcing Microsoft Entra ID-Only

Azure SQL supports both SQL authentication (username/password local to the database) and Microsoft Entra ID authentication. Entra-only authentication can be enforced at the server level, disabling SQL authentication entirely, so every connection is tied to a centrally managed identity subject to Conditional Access, MFA, and PIM, rather than a separate local credential that lives outside the identity governance model entirely.

Network Protection

• Firewall rules at the server or database level restrict connecting IP ranges
• Private endpoints remove public endpoint exposure entirely, matching the same private-link pattern used for storage and Key Vault
• Microsoft Defender for SQL (part of Defender for Databases) adds Advanced Threat Protection: vulnerability assessment scans for risky configurations and missing patches, and threat detection alerts on suspicious activity like potential SQL injection or brute-force login attempts

Configuring Auditing

Azure SQL auditing tracks database events (logins, schema changes, query execution depending on configured categories) and writes them to one or more destinations:

• Azure Storage account (long-term retention, lowest cost)
• Log Analytics workspace (enables KQL querying and Sentinel integration)
• Event Hub (for streaming to a third-party SIEM)

Audit logs are what satisfy the "who accessed or changed this data, and when" requirement common to compliance frameworks like PCI DSS or SOC 2, and are typically configured at the server level so every database inherits the same policy automatically.

Defender for Databases Coverage

Defender for Databases protects more than just Azure SQL:

Each plan is enabled independently per database service in Defender for Cloud's workload protection settings, and each surfaces its own tailored vulnerability assessments and threat alerts specific to that engine's attack patterns. Exam tip: A question describing "a DBA should never be able to view real credit card numbers even though they administer the server" points to Always Encrypted, not TDE (which protects data at rest but is transparent to anyone with query access) and not Dynamic Data Masking (which only masks in query *results*, not from a privileged principal who can bypass masking).