All advanced threat protection types should be enabled in SQL managed instance advanced data security settings

What does the recommendation "All advanced threat protection types should be enabled in SQL managed instance advanced data security settings" check?

Flags an Azure SQL Managed Instance where Advanced Threat Protection, part of Microsoft Defender for SQL, is not switched fully on, so the instance is not covering the complete set of detection types. Advanced Threat Protection on a managed instance is a single setting rather than a per-type toggle, and when enabled it covers all four alert categories: potential SQL injection, access from an unusual location or data centre, access from an unfamiliar principal or potentially harmful application, and brute force SQL credentials. The control checks only that this protection is active. It does not verify that the Microsoft Defender for SQL plan itself is enabled across the subscription, that an audit storage account or notification email is configured, or that the underlying databases are audited. Those are separate recommendations.

Why does "All advanced threat protection types should be enabled in SQL managed instance advanced data security settings" matter?

Leaving Advanced Threat Protection off is a common way teams quietly create blind spots: someone skips it to stop pager noise during a migration and never re-enables it. A managed instance often holds the most sensitive data an organisation runs, and these alerts are frequently the only signal that a credential has been stolen or an injection string is probing the database. With protection off, the matching attack proceeds unobserved, so a breach is discovered weeks later through data appearing for sale rather than in time to contain it. Keeping it on costs nothing extra once Defender for SQL is enabled and preserves full detection coverage.

How do I fix "All advanced threat protection types should be enabled in SQL managed instance advanced data security settings"?

1. Confirm Microsoft Defender for SQL is enabled, then turn Advanced Threat Protection on for the managed instance so the full detection set is active: 'az sql mi advanced-threat-protection-setting update -g <rg> -n <managed-instance> --state Enabled'. This command exposes only --state, so there is no per-alert-type flag to set; enabling it covers all four alert types at once.
2. Make the detections actionable in the portal: open the managed instance's Microsoft Defender for Cloud blade, select Configure, choose a storage account on the Server settings page for the audit records, and add your contact details to the subscription's Defender for Cloud email settings so triggered alerts reach a human and the audit records are retained.
3. Assign the built-in policy to audit drift across the estate by display name rather than a hardcoded GUID: pid=$(az policy definition list --query "[?displayName=='All advanced threat protection types should be enabled in SQL managed instance advanced data security settings'].name" -o tsv); az policy assignment create --name sqlmi-atp-all-types --policy "$pid" --scope <subscription-or-mg-scope>.

# 1. Enable Advanced Threat Protection on every logical server (detection only, no blocking).
for row in $(az sql server list --query "[].{n:name,g:resourceGroup}" -o tsv | tr '\t' ','); do
  n=$(echo "$row" | cut -d',' -f1); g=$(echo "$row" | cut -d',' -f2)
  az sql server advanced-threat-protection-setting update -g "$g" -n "$n" --state Enabled
  echo "$n: advanced threat protection enabled"
done

# 2. Same for every SQL Managed Instance.
for row in $(az sql mi list --query "[].{n:name,g:resourceGroup}" -o tsv | tr '\t' ','); do
  n=$(echo "$row" | cut -d',' -f1); g=$(echo "$row" | cut -d',' -f2)
  az sql mi advanced-threat-protection-setting update -g "$g" -n "$n" --state Enabled
done

# 3. Turn on the Defender for Azure SQL plan for the whole subscription so new
#    databases are covered automatically (this is what carries the per-resource cost).
az security pricing create -n SqlServers --tier Standard

# 4. Enforce at scale via the supported built-in policy (looked up by display name,
#    never a hardcoded GUID). Do NOT assign the deprecated 'set to All' policy.
pid=$(az policy definition list \
  --query "[?displayName=='Configure Azure Defender to be enabled on SQL servers and SQL Managed Instances'].name" -o tsv)
az policy assignment create \
  --name enable-defender-sql \
  --policy "$pid" \
  --scope "/subscriptions/<subscription-id>"