SQL servers do not enable data security policy

sql_server_data_security_policy

Severity

low

Vendor

Azure

Resource

MSSQL server

Description

SQL servers should have security policy enabled.

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.

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

1	SQL Server that hasn’t a security policy enabled.

Terraform

resource "azurerm_sql_server" "sql_server" {
  name = "mysqlserver"
  # ... more properties
}

resource "azurerm_mssql_server_security_alert_policy" "alert_policy" {
  server_name         = azurerm_sql_server.sql_server.name
  resource_group_name = "group"
  state               = "Disabled" (1)
  retention_days      = 20
}

1	FLAW, security alert policy is not enabled.

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

1	SQL Server that has a security policy enabled.

Terraform

resource "azurerm_sql_server" "sql_server" {
  name = "mysqlserver"
  # ... more properties
}

resource "azurerm_mssql_server_security_alert_policy" "alert_policy" {
  server_name          = azurerm_sql_server.sql_server.name
  resource_group_name  = "group"
  state                = "Enabled" # 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 Security Center under Security.
- Click on Enable Azure Defender for SQL.