Ensure that 'Send Alerts To' is enabled for MSSQL servers

sql_server_disabled_email_alerts

Severity

low

Vendor

Azure

Resource

MSSQL server

Description

SQL servers security policy enabled should email 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.

Once an Email Address is provided then alerts will be sent when anomalous activities are detected on SQL servers.

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

1	Email addresses are not provided to the SQL Server alert policy.

Terraform

resource "azurerm_mssql_server_security_alert_policy" "bad" {
  # ... no email_addresses set ...
}

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	Email addresses are provided to the SQL Server alert policy.

Terraform

resource "azurerm_mssql_server_security_alert_policy" "bad" {
  # FIXED
  email_addresses = [ "security@myorg.com" ]
}

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.