Ensure Azure SQL server send alerts to field value is set

Error: Azure SQL server send alerts to field value is not set

Bridgecrew Policy ID: BC_AZR_GENERAL_7
Checkov Check ID: CKV_AZURE_26
Severity: HIGH

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.

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 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 <resource group name> 
-ServerName <server name> 
-NotificationRecipientsEmails "<Recipient Email ID>"

Fix - Buildtime

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: email_addresses
resource "azurerm_mssql_server_security_alert_policy" "example" {
    ...
+   email_addresses = ["[email protected]"]
}