6.2 Database Auditing

Role of Auditing on SC-500

The blueprint calls out Configure database auditing for Azure SQL Database and Azure SQL Managed Instance. Auditing answers: Who did what, on which database, from where, and when? It supports regulatory evidence, insider-threat review, and incident response. It is not the same as Advanced Threat Protection, which raises alerts on suspicious patterns—though both may appear together in secure SQL designs.

---

Azure SQL Database and Synapse Auditing Overview

Auditing for Azure SQL Database (and Azure Synapse Analytics SQL pools where supported) tracks database events and writes audit records to:

You can enable multiple destinations. Auditing helps retain trails, report activity, and analyze discrepancies—Microsoft notes it facilitates compliance programs but does not by itself “guarantee” compliance certification.

Performance caveat: Auditing is optimized for database availability. Under extreme load, not every marked event may be recorded. Design critical audits with that resilience trade-off in mind and validate with Defender alerts and platform metrics.

---

Server-Level vs Database-Level Auditing

Practical guidance

• Enabling server-level auditing is usually enough for “audit everything on this server.”
• Enabling both server and database auditing for the same events can duplicate records and inflate cost—LinkedIn/field reports and Microsoft large-OLTP guidance warn about volume.
• For many busy databases, Microsoft recommends considering database-level auditing with reviewed action groups so each DB has its own log folder and queries stay manageable, instead of one enormous shared stream.

Server auditing architecture notes (exam-relevant)

Microsoft re-architected server auditing for Azure SQL Database for reliability alignment with SQL Server/MI patterns:

• Server audit logs in storage may consolidate under a master folder rather than per-database folder names (older layouts differed).
• Read-only replica logs may also land in the master folder; filter with replica-related columns when investigating.
• Viewing certain audit data can require VIEW DATABASE SECURITY AUDIT (and related permissions) in the user database.

You do not need every internal detail memorized, but you should know: scope (server vs database), destinations, and that dual enablement multiplies volume.

---

What Events Are Captured?

Auditing uses action groups (categories of events). Default configurations commonly emphasize:

Additional groups exist for permission changes, schema object access, and other security-relevant operations depending on product surface. For Azure Synapse SQL pools, support may be limited to default audit action groups only—another exam nuance.

Important capture nuances

• Statement length: Audit fields such as statement and data sensitivity information may truncate around 4,000 characters—very long batches are only partially recorded.
• Temp objects: tempdb/temporary table auditing is constrained; source table activity is the reliable trail for many insert-via-temp patterns.
• Microsoft Entra failed logins: As noted in platform security, failed Entra authentications may not appear in SQL audit the same way SQL password failures do; check Microsoft Entra sign-in logs.
• Successful Entra and SQL logins that establish a database session are auditable when the right groups are enabled.

---

Retention and Immutable Storage

Secure the log store independently: storage firewalls/private endpoints, RBAC (Storage Blob Data Contributor for writers such as managed identity scenarios), and separate readers for auditors vs DBAs.

Storage behind VNet/firewall: Prefer general-purpose v2 accounts for audit write patterns Microsoft documents. Authentication mode to storage must match account settings (access keys vs Microsoft Entra / managed identity). If the SQL server has outbound restrictions, allowlist the storage FQDN and re-save auditing configuration after network changes.

---

Azure SQL Managed Instance Auditing

Managed Instance auditing follows SQL Server Audit more closely than the Database portal experience:

1. To Azure Storage (URL): Create a private container, establish a credential (historically SAS-based for the container URL), CREATE SERVER AUDIT ... TO URL (PATH=..., RETENTION_DAYS=...), create server or database audit specifications, then ALTER SERVER AUDIT ... WITH (STATE = ON).
2. To Log Analytics / Event Hubs: Enable diagnostic settings on the MI for SQLSecurityAuditEvents (and DevOps/support operation logs if used), create CREATE SERVER AUDIT ... TO EXTERNAL_MONITOR, specifications, then enable the audit.
3. Microsoft Support operations auditing: Optional separate server audit that records Microsoft support engineer operations during support cases—enable deliberately; enabling the checkbox on an existing audit can overwrite intent so only support ops are logged.

Differences vs SQL Server on-prem exist (URL targets, EXTERNAL_MONITOR, cloud identity patterns). For SC-500, remember: MI = SQL Audit objects + diagnostics, Database = auditing blade with Storage/LA/EH destinations, both produce investigation evidence.

---

Using Audit Logs for Investigations

Typical investigation flow

1. Trigger: Defender for SQL alert, user report, or compliance sampling request.
2. Scope time window and identity: UPN, client IP, application name, database name.
3. Query the right store:
   • Log Analytics: search SQLSecurityAuditEvents / AzureDiagnostics audit categories; filter by LogicalServerName, database_name, client_ip, principal.
   • Storage: browse container hierarchy or sys.fn_get_audit_file against blob paths.
   • Event Hubs: downstream SIEM correlation with Entra and NSG/Firewall logs.
4. Correlate: Pair SQL batches with Entra sign-ins, Key Vault access (if secrets used), and app logs.
5. Contain: Disable principal, rotate credentials, tighten firewall/private access, revoke roles.
6. Preserve evidence: Export relevant audit ranges; verify retention will not age them out mid-case.

Example investigation questions audit can answer

Scenario: SOC receives a SQL injection alert from Defender for SQL on sqldb-orders. They open Log Analytics for the server’s audit diagnostic, filter the database and the 15-minute window, find repeated batches with tautology patterns from an app managed identity—and also interactive batches from a human Entra user who should only use the portal. Outcome: fix the app parameterization, revoke the human’s elevated role, confirm server audit retention ≥ 90 days for the regulator, and keep Defender enabled.

SC-500 Exam Traps (Auditing)

• Auditing ≠ threat protection; one records, one detects.
• Server vs database scope: know coverage and duplication risk.
• Destinations are Storage, Log Analytics, Event Hubs—not “only Activity Log.”
• MI configuration is not identical to Database portal toggles.
• Failed Entra logins may require Entra logs, not only SQL audit.
• Immutable storage for audit needs append write allowance.