Azure SQL Auditing and Advanced Threat Protection

Azure SQL Auditing tracks database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs. It helps maintain regulatory compliance, understand database activity, and detect anomalies that might indicate security breaches. The audit log captures events such as successful and failed logins, data modifications, schema changes, and more.

When you enable auditing on an Azure SQL Database or Managed Instance, the database engine creates a server-level audit policy that monitors events according to the configured audit action groups. These action groups are predefined sets of actions, like SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP or DATABASE_OPERATION_GROUP. Each database event is evaluated against the audit policy. If the event matches an included action group, it is written to the audit destination. The audit records include details like event time, action ID, session ID, server principal name, database name, and the T-SQL statement executed.

To enable auditing via Azure Portal: 1. Navigate to your Azure SQL Database or Managed Instance. 2. Under 'Security', select 'Auditing'. 3. Set 'Auditing' to 'ON'. 4. Choose destination: Storage account (specify account and retention), Log Analytics (workspace), or Event Hub. 5. Select audit action groups (default includes all groups). 6. Save.

To verify, you can query the audit logs. For storage, download .xel files and open with SQL Server Management Studio (SSMS) or use sys.fn_get_audit_file function:

SELECT * FROM sys.fn_get_audit_file('https://mystorage.blob.core.windows.net/sqldbauditlogs/MyServer/MyDatabase/', DEFAULT, DEFAULT);

For Log Analytics, use KQL queries like:

AzureDiagnostics
| where OperationName == 'AuditEvent'
| project TimeGenerated, server_instance_name_s, database_name_s, action_id_s, session_server_principal_name_s, statement_s

Azure Policy can enforce auditing on all SQL databases via built-in policies like 'Auditing on SQL server should be enabled'. Microsoft Defender for Cloud uses audit logs to detect threats and integrate with Advanced Threat Protection.

Azure SQL Advanced Threat Protection (ATP) is a security intelligence layer that monitors database activity for anomalous behaviors and provides alerts on potential threats. It is part of Microsoft Defender for Cloud and is available for Azure SQL Database, Managed Instance, and Synapse SQL. ATP uses machine learning and behavioral analysis to detect unusual access patterns, SQL injection attempts, brute-force attacks, and privilege escalation.

ATP continuously analyzes audit logs and telemetry from the database engine. It builds a baseline of normal activity for each database, including typical logins, query patterns, and data access. When deviations occur—like a sudden spike in failed logins from a new IP, or a user accessing a sensitive table at an unusual time—ATP generates a security alert. The detection uses multiple models: - SQL injection detection: Analyzes query strings for patterns like 'OR 1=1' or stacked queries. - Brute-force detection: Monitors failed login attempts from the same IP within a short window (e.g., 15 failed attempts in 5 minutes). - Anomalous location access: Compares login source IPs against geo-location baselines. - Unusual data access patterns: Detects when a user downloads large volumes of data or accesses tables they rarely use.

ATP is enabled at the server level (for all databases on that server) or individually. In Azure Portal: 1. Go to your SQL server. 2. Under 'Security', select 'Microsoft Defender for Cloud'. 3. Enable 'Microsoft Defender for SQL' (which includes ATP and vulnerability assessment). 4. Configure email recipients for alerts.

Alternatively, use PowerShell:

Set-AzSqlServerThreatDetectionPolicy -ResourceGroupName 'rg' -ServerName 'myserver' -NotificationRecipientsEmails 'admin@contoso.com' -EmailAdmins $True -ExcludedDetectionType @()

ATP requires auditing to be enabled because it analyzes audit logs. Without auditing, ATP cannot generate alerts. The two services are complementary: auditing provides forensic records, while ATP provides real-time threat detection.

A financial services company must comply with PCI DSS, which requires logging all database access and changes. They enable Azure SQL Auditing on all production SQL servers with a retention of 365 days, storing logs in a Geo-Redundant Storage (GRS) account for disaster recovery. They also enable ATP to detect SQL injection attempts, which are common attack vectors. In production, they found that auditing to Log Analytics was more efficient for compliance reporting because they could query logs with KQL and create dashboards. A common misconfiguration is forgetting to set retention, causing logs to accumulate indefinitely and incurring high storage costs. They set retention to 365 days exactly, as required by PCI DSS.

An e-commerce platform uses Azure SQL Database for its product catalog and user data. They enable ATP to detect brute-force attacks on user accounts. When ATP detects 15 failed logins from a single IP within 5 minutes, it triggers a Logic App that adds the IP to a deny list in the SQL server firewall. This automated response reduces the risk of account takeover. They also audit all successful logins to a Log Analytics workspace for forensic analysis. A performance consideration: high-traffic databases generate millions of audit events per day, so they use Log Analytics with a retention of 90 days and archive older logs to cold storage. Without proper sizing, the Log Analytics ingestion costs can spike.

A large enterprise with hundreds of SQL databases uses Azure Policy to enforce auditing and ATP across all subscriptions. They assign the built-in policy 'Auditing on SQL server should be enabled' and 'Microsoft Defender for SQL should be enabled on your SQL servers'. Non-compliant servers are automatically remediated. They also use Azure Sentinel to correlate ATP alerts with other security events. A common pitfall is that policy assignments at the management group level may conflict with deny assignments, causing deployment failures. They use policy exemptions for specific test environments.