Azure SQL Advanced Threat Protection

Azure SQL Advanced Threat Protection (ATP) is a security intelligence layer built into Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It continuously monitors database activity for anomalous behaviors that may indicate potential threats, such as SQL injection, brute force attacks, or privilege escalation. ATP provides a unified view of security alerts in Azure Security Center, enabling security teams to respond quickly.

ATP is part of the Azure Defender for SQL plan, which also includes vulnerability assessment and data discovery & classification. On the AZ-500 exam, ATP is tested under objective 2.5: Implement security for data at rest, data in transit, and data in use. Specifically, you need to know how to enable ATP, interpret alerts, and integrate with Azure Sentinel.

ATP uses machine learning models trained on Microsoft's vast telemetry from millions of databases. These models establish a baseline of normal activity for each database, including typical login patterns, query execution frequencies, and data access volumes. When activity deviates from the baseline, ATP generates an alert.

The detection engine operates at the database engine level, analyzing SQL audit logs in real time. It looks for patterns such as: - SQL injection attempts: Queries that contain suspicious characters or patterns, like OR '1'='1', or attempts to modify SQL statements. - Brute force attacks: Multiple failed login attempts from different IP addresses or rapid succession. - Unusual access from unfamiliar locations: Logins from geographic regions not normally seen. - Unusual data exfiltration: Queries that retrieve large volumes of data, especially if the user typically accesses small amounts. - Privilege escalation attempts: Users trying to execute commands that require higher privileges.

ATP does not block queries by default; it only alerts. However, you can configure automated responses using Azure Logic Apps or Azure Automation runbooks to block the source IP or kill the session.

- SQL Injection - Brute force (SQL authentication) - Unusual login location - Unusual data exfiltration - Potentially unsafe action - Alert severity: Each alert has a severity level (Low, Medium, High) based on the risk. - Integration: Alerts appear in Azure Security Center and can be streamed to Azure Sentinel via continuous export. - Retention: Alerts are retained for 90 days in the Azure portal.

- In Azure portal, navigate to your SQL server or managed instance. - Under 'Security', click 'Microsoft Defender for Cloud'. - Toggle 'Microsoft Defender for SQL' to On. - Alternatively, use Azure CLI:

az sql server update --name <server-name> --resource-group <rg> --enable-advanced-data-security true

ATP works closely with: - Azure SQL Auditing: ATP relies on audit logs to analyze activity. Auditing must be enabled at the server level. If auditing is disabled, ATP cannot function properly. - Azure Security Center: All ATP alerts are surfaced here. Security Center provides a unified dashboard for all security alerts across services. - Azure Sentinel: You can connect ATP alerts to Sentinel for advanced correlation and incident response. - Vulnerability Assessment: Part of the same Defender for SQL package, it scans for database misconfigurations and vulnerabilities.

ATP has minimal performance overhead because it processes audit logs asynchronously. The machine learning models run on separate infrastructure, not on the database server. However, enabling auditing itself can add up to 15% overhead on write-heavy workloads. Use Azure Monitor metrics to monitor audit write latency.

Enable ATP with Azure PowerShell:

Set-AzSqlServerAdvancedThreatProtectionSettings -ResourceGroupName "myrg" -ServerName "myserver" -Enabled True

List alerts with Azure CLI:

az security alert list --query "[?contains(properties.compromisedEntity, 'myserver')]"

ATP uses unsupervised learning to establish baselines. For example, it tracks the average number of queries per hour from each user. If a user suddenly executes 10,000 queries in one hour, ATP flags it as unusual. Similarly, it tracks login success/failure ratios. A sudden spike in failures from a new IP triggers a brute force alert.

The detection models are updated regularly by Microsoft's security research team. Customers cannot customize the models, but they can tune alert sensitivity via the Azure portal (low, medium, high).

A large e-commerce company uses Azure SQL Database to store customer profiles and order history. They enable ATP to detect SQL injection attacks that attempt to steal credit card numbers. In production, ATP is configured with high sensitivity to catch even subtle injection attempts. The security team receives alerts in Azure Security Center and uses a Logic App to automatically block the attacker's IP for 24 hours. One challenge is that legitimate complex queries (e.g., dynamic search filters) can trigger false positives. To reduce noise, they whitelist certain application IPs and tune alert sensitivity. The performance impact is negligible because auditing logs are stored in a separate storage account. However, they had to increase the audit log retention to 180 days for compliance, which increased storage costs.

A healthcare organization with Azure SQL Managed Instance uses ATP to monitor privileged users (DBAs) for unusual activity. They have a zero-trust policy where any anomalous query by a DBA is treated as a potential compromise. ATP alerts on unusual data access patterns, such as a DBA querying patient records outside of normal hours. They integrate ATP alerts with Azure Sentinel, which correlates with Azure Active Directory sign-in logs. When a DBA logs in from an unexpected location and then runs a bulk export query, Sentinel creates a high-severity incident. The incident triggers an automated runbook that disables the user's account temporarily. They also use ATP's vulnerability assessment to regularly scan for misconfigurations. A common issue is that ATP alerts for 'unusual data exfiltration' when the DBA runs legitimate maintenance scripts; they had to create exclusion rules for known maintenance windows.