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

Description

Enabling all Threat Detection Types protects against SQL injection, database vulnerabilities, and any other anomalous activities. We recommend you enable all types of threat detection on SQL servers.

Fix - Runtime

Azure Portal

To change the policy using the Azure Portal, follow these steps:

1. Log in to the Azure Portal at https://portal.azure.com.
2. Navigate to SQL servers.
3. For each server instance:
   a) Click Advanced Data Security.
   b) Navigate to Threat Detection Settings section.
   c) 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"

Fix - Buildtime

ARM

• Resource: Microsoft.Sql/servers/databases

{
    "type": "Microsoft.Sql/servers/databases",
    "apiVersion": "2020-08-01-preview",
    "name": "[variables('dbName')]",
    "location": "[parameters('location')]",
    "sku": {
        "name": "[parameters('sku')]"
    },
    "kind": "v12.0,user",
    "properties": {
        "collation": "SQL_Latin1_General_CP1_CI_AS",
        "maxSizeBytes": "[mul(parameters('maxSizeMB'), 1048576)]",
        "catalogCollation": "SQL_Latin1_General_CP1_CI_AS",
        "zoneRedundant": false,
        "readScale": "Disabled",
        "storageAccountType": "GRS"
    },
    "resources": [
        {
            "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
            "apiVersion": "2014-04-01",
            "name": "[concat(variables('dbName'), '/current')]",
            "location": "[parameters('location')]",
            "dependsOn": [
                "[resourceId('Microsoft.Sql/servers/databases', parameters('serverName'), parameters('databaseName'))]"
            ],
            "properties": {
+               "state": "Enabled",
+               "disabledAlerts": "",
                "emailAddresses": "[variables('emailAddresses')[copyIndex()]]",
                "emailAccountAdmins": "Enabled"
            }
        }
    ]
}

Terraform

• Resource: azurerm_mssql_server_security_alert_policy
• Argument: disabled_alerts

resource "azurerm_mssql_server_security_alert_policy" "example" {
    ...
+   disabled_alerts = []
    }