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

sql_server_disabled_admin_email_alerts

Severity

low

Vendor

Azure

Resource

MSSQL server

Description

SQL servers security policy enabled should email alerts to administrators.

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.

By enabling service and co-administrators to receive security alerts from the SQL server you can ensure that any detection of anomalous activities is reported as soon as possible, enabling early mitigation of any potential risk detected.

See Security Alerts Policy Properties for more details.

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

json

1	Email account admins is disabled for the SQL Server alert policy.

Terraform

resource "azurerm_mssql_server_security_alert_policy" "bad" { (1)
  resource_group_name        = azurerm_resource_group.example.name
  server_name                = azurerm_sql_server.example.name
  state                      = "Enabled"
  storage_endpoint           = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_access_key = azurerm_storage_account.example.primary_access_key
  disabled_alerts = []
  retention_days = 20
}

1	No email_account_admins 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"
          }
        }
      ]
    }
  ]
}

json

1	Email account admins is enabled for the SQL Server alert policy.

Terraform

resource "azurerm_mssql_server_security_alert_policy" "good" {
  resource_group_name        = azurerm_resource_group.example.name
  server_name                = azurerm_sql_server.example.name
  state                      = "Enabled"
  storage_endpoint           = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_access_key = azurerm_storage_account.example.primary_access_key
  disabled_alerts = []
  email_addresses = ["example@gmail.com"]
  email_account_admins = true # FIXED
  retention_days = 20
}

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

CLI Coommand

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

See Set a threat detection policy on a server.