SQL Server Advanced Threat Protection Disabled: Why It Matters and How to Fix It

Advanced Threat Protection (ATP) is one of those features that sits quietly in the background until the day you actually need it. When a SQL Server has it disabled, you lose your early warning system for the most common database attacks. This check fires whenever an Azure SQL logical server is found with ATP turned off, and it is worth taking seriously because the gap it leaves is exactly the kind of thing attackers count on.

What this check detects

The sql_nothreatdetection check inspects each Azure SQL Server (the logical server resource, not individual databases) and verifies that Advanced Threat Protection is set to Enabled. If the setting is Disabled, the check fails.

Advanced Threat Protection is part of Microsoft Defender for SQL. Once enabled at the server level, it continuously analyzes activity against your databases and raises alerts for behavior that looks malicious or anomalous. That includes:

• SQL injection and potential SQL injection vulnerabilities
• Access from unusual locations or unfamiliar Azure data centers
• Access from a principal with unusual characteristics, such as a new application or service identity
• Brute force attempts against SQL credentials
• Anomalous data exfiltration, like an unusually large volume of rows being extracted

Why it matters

SQL databases are where the valuable data lives: customer records, payment details, internal business logic. They are also one of the most heavily targeted surfaces in any environment. SQL injection has been near the top of the OWASP risk list for over a decade, and it has not gone away because the underlying mistakes (string-concatenated queries, over-permissioned database users) keep getting reintroduced into new applications.

Without ATP, those attacks happen silently. Consider a realistic scenario:

• An application has a search endpoint vulnerable to SQL injection.
• An attacker probes it, confirms the vulnerability, and starts enumerating tables.
• Over a few hours they extract a customer table containing email addresses and hashed passwords.

With ATP enabled, the unusual query patterns and the spike in extracted rows would have generated alerts the moment the probing started, giving your team a chance to respond while the attacker was still mapping the schema. With it disabled, the first sign of trouble is often a breach disclosure or a dump appearing for sale.

There is also a compliance angle. Frameworks like PCI DSS, SOC 2, and ISO 27001 expect some form of database activity monitoring and threat detection. An auditor who finds ATP disabled across your SQL estate will flag it, and remediating it under audit pressure is far less pleasant than doing it now.

How to fix it

You can enable ATP through the Azure Portal, the CLI, PowerShell, or infrastructure as code. Pick whichever matches how you manage the rest of your environment.

Azure Portal

1. Open the SQL server resource (not the database).
2. Under Security, select Microsoft Defender for Cloud.
3. Click Enable Microsoft Defender for SQL.
4. Configure the alert recipients and the storage account used for vulnerability assessment scans.

Azure CLI

Enable Advanced Threat Protection on the server:

az sql server advanced-threat-protection-setting update \
  --resource-group my-resource-group \
  --name my-sql-server \
  --state Enabled

Verify the setting took effect:

az sql server advanced-threat-protection-setting show \
  --resource-group my-resource-group \
  --name my-sql-server \
  --query "state" \
  --output tsv

ATP alerts are most useful when someone actually receives them. Configure the security alert policy so notifications go to your security team and account admins:

az sql server threat-policy update \
  --resource-group my-resource-group \
  --server my-sql-server \
  --state Enabled \
  --email-addresses [email protected] \
  --email-account-admins Enabled \
  --storage-account mystorageaccount

az sql server list --query "[].{name:name, rg:resourceGroup}" -o tsv | \
while read name rg; do
  state=$(az sql server advanced-threat-protection-setting show \
    -g "$rg" -n "$name" --query state -o tsv)
  echo "$name ($rg): $state"
done

PowerShell

Update-AzSqlServerAdvancedThreatProtectionSetting `
  -ResourceGroupName "my-resource-group" `
  -ServerName "my-sql-server"

Terraform

If you manage SQL servers with Terraform, the threat protection setting lives in the security alert policy resource. Codifying it means the configuration cannot quietly drift back to disabled:

resource "azurerm_mssql_server_security_alert_policy" "example" {
  resource_group_name        = azurerm_resource_group.example.name
  server_name                = azurerm_mssql_server.example.name
  state                      = "Enabled"
  email_account_admins       = true
  email_addresses            = ["[email protected]"]
  storage_endpoint           = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_access_key = azurerm_storage_account.example.primary_access_key
  retention_days             = 30
}

How to prevent it from happening again

Fixing one server is easy. Making sure the next one ships with ATP enabled is the harder, more valuable problem. Three layers work well together.

1. Enforce with Azure Policy

Azure ships built-in policy definitions for exactly this. Assign the policy "Advanced Threat Protection should be enabled on Azure SQL Database servers" in DeployIfNotExists mode so noncompliant servers are automatically remediated, or use Audit mode first if you want to see the blast radius before enforcing.

az policy assignment create \
  --name "enforce-sql-atp" \
  --display-name "Enable ATP on Azure SQL servers" \
  --policy "abfb7388-5bf4-4ad7-ba99-2cd2f41cebb9" \
  --scope "/subscriptions/00000000-0000-0000-0000-000000000000" \
  --location eastus \
  --mi-system-assigned \
  --role Contributor

2. Gate it in CI/CD

If your SQL servers are defined in Terraform or Bicep, add a check to your pipeline that rejects any plan defining a SQL server without an enabled security alert policy. Tools like tfsec, Checkov, and terraform plan parsing can catch this before merge:

checkov -d ./infra --check CKV_AZURE_25 --compact

3. Monitor continuously

Policy and CI/CD cover new and managed resources, but manual changes and resources created outside your pipelines still slip through. A continuous scan, like the Lensix sql_nothreatdetection check, catches drift across your whole estate regardless of how a server was created, and surfaces it before an auditor or attacker does.

Best practices

Threat detection is one control in a layered approach to SQL security. Pair it with the rest:

• Enable auditing alongside ATP so that when an alert fires, you have the query-level audit trail to investigate it.
• Turn on vulnerability assessment scans and act on the findings. ATP tells you about attacks in progress; vulnerability assessment tells you about the weaknesses that make those attacks possible.
• Use Microsoft Entra authentication rather than SQL logins where you can, so credential attacks have a smaller surface and access is governed by your central identity controls.
• Lock down network access with private endpoints or firewall rules. ATP is far more meaningful when the only traffic reaching your database comes from known sources.
• Route alerts somewhere a human reads. Sending ATP emails to an unmonitored distribution list is the same as not having them. Pipe them into your SIEM or incident channel.
• Apply least privilege to database users. Even if an injection attack succeeds, a tightly scoped application account limits how much an attacker can reach.

Enabling ATP takes one command. The harder part, building the policy and monitoring around it so it stays enabled, is what turns a one-time fix into durable protection. Get both in place and you close one of the quietest gaps in a SQL deployment.