MSSQL servers do not have email service and co-administrators enabled

Description

Enable Service and Co-administrators to receive security alerts from the SQL server. 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.

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) Enable Email service and co-administrators.

CLI Command

To enable each server's Email service and co-administrators for MSSQL, use the following command:

Set-AzureRmSqlServerThreatDetectionPolicy
-ResourceGroupName <resource group name> 
-ServerName <server name> 
-EmailAdmins $True

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_account_admins

resource "azurerm_mssql_server_security_alert_policy" "example" {
    ...
+   email_account_admins       = true
}