Azure SQL Server threat detection alerts are not enabled for all threat types

sql_server_disabled_alerts

Severity

low

Vendor

Azure

Resource

MSSQL server

Description

SQL servers security policy enabled should not disable alerts.

Microsoft Defender for Azure SQL includes functions that can be used to discover and mitigate potential database vulnerabilities.

A vulnerability assessment service discovers, tracks, and helps you remediate potential database vulnerabilities. Assessment scans provide an overview of your SQL machines' security state, and details of any security findings.

When disabling some of the alerts you are assuming a great risk that could cause that the service gets compromised.

Examples

ARM

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2020-08-01-preview",
      "name": "bad", (1)
      "properties": {
        "state": "[parameters('transparentDataEncryption')]"
      },
      "resources": [
        {
          "type": "securityAlertPolicies",
          "apiVersion": "2022-05-01-preview",
          "name": "Default",
          "properties": {
            "state": "Enabled",
            "disabledAlerts": "All"
          }
        }
      ]
    }
  ]
}

1	SQL Server that has disabled all alerts.

Terraform

resource "azurerm_mssql_server_security_alert_policy" "my_policy" {
  resource_group_name        = azurerm_resource_group.example.name
  server_name                = azurerm_sql_server.example.name
  state                      = "Enabled"
  storage_endpoint           = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_access_key = azurerm_storage_account.example.primary_access_key
  disabled_alerts = ["Sql_Injection", "Data_Exfiltration"] # FLAW
  email_addresses = ["example@gmail.com"]
  email_account_admins = true
  retention_days = 20
}

Mitigation / Fix

Buildtime

ARM

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2020-08-01-preview",
      "name": "good", (1)
      "properties": {
        "state": "[parameters('transparentDataEncryption')]"
      },
      "resources": [
        {
          "type": "securityAlertPolicies",
          "apiVersion": "2022-05-01-preview",
          "name": "Default",
          "properties": {
            "state": "Enabled",
            "emailAddresses": "[variables('emailAddresses')]",
            "emailAccountAdmins": "Enabled"
          }
        }
      ]
    }
  ]
}

1	SQL Server that hasn’t disabled alerts.

Terraform

resource "azurerm_mssql_server_security_alert_policy" "my_policy" {
  resource_group_name        = azurerm_resource_group.example.name
  server_name                = azurerm_sql_server.example.name
  state                      = "Enabled"
  storage_endpoint           = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_access_key = azurerm_storage_account.example.primary_access_key
  disabled_alerts = [] # FIXED
  email_addresses = ["example@gmail.com"]
  email_account_admins = true
  retention_days = 20
}

Runtime

Azure Portal

To change the policy Log in to Azure Portal and then:

Navigate to SQL servers and for each instance:
- Click on Advanced Data Security.
- Navigate to Threat Detection Settings section.
- Set Threat Detection Types to All.

CLI Command

To set each server’s ExcludedDetectionTypes to None, use the following command:

$ Set-AzureRmSqlServerThreatDetectionPolicy
-ResourceGroupName <resource group name>
-ServerName <server name>
-ExcludedDetectionType "None"

See Set a threat detection policy on a server.