Azure SQL server send alerts to field value is not set

Description

Provide the email address where alerts will be sent when anomalous activities are detected on SQL servers. Providing the email address to receive alerts ensures that any detection of anomalous activities is reported as soon as possible, enabling early mitigation of any potential risk detected. We recommend you add an email address to the Send Alerts to field value for MSSQL servers.

Code Example

{
    "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"
            }
        }
    ]
}

Remediation

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

. Log in to the Azure Portal at https://portal.azure.com.

. Navigate to * SQL servers*.

. For each server instance: a) Click * Advanced Threat Protection*. + b) In * Send alerts to* enter email addresses as appropriate.

• CLI Command*

To set each server's * Send alerts to*, use the following command:

Set-AzureRmSqlServerThreatDetectionPolicy -ResourceGroupName & lt;resource group name> -ServerName & lt;server name> -NotificationRecipientsEmails "& lt;Recipient Email ID>"

=== Fix - Buildtime

ARM

• Resource: Microsoft.Sql/servers/databases

Rule Details

Field	Value
ID	IAC-0537
Severity	HIGH
IaC Type	arm
Frameworks	ARM, Terraform, Bicep, TerraformPlan
Checkov ID	CKV_AZURE_26

References

• Checkov Rule

---