Azure SQL server auditing is disabled

sql_server_auditing_enabled

Severity

low

Vendor

Azure

Resource

MSSQL server

Description

SQL servers should have auditing enabled.

The Azure platform allows a SQL server to be created as a service.

Auditing tracks database events and writes them to an audit log in the Azure storage account. It also helps to maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.

By enabling auditing at the server level, all existing and future created databases on the SQL server instance will be audited.

See Auditing for Azure SQL Database 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",
      "apiVersion": "2019-06-01-preview",
      "location": "[parameters('location')]",
      "name": "bad" (1)
    }
  ]
}

1	SQL Server that has not auditing enabled.

Terraform

resource "azurerm_mssql_server" "sql_server" {
  name = var.mssql.name
  # ... more properties ...
}

resource "azurerm_mssql_server_extended_auditing_policy" "audit_policy" {
  server_id = azurerm_mssql_server.sql_server.id (1)
  # ...
  storage_endpoint           = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_access_key = azurerm_storage_account.example.primary_access_key
  storage_account_access_key_is_secondary = true
  retention_in_days          = 7 (1)
}

1	FLAW, too-low retention time.

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",
      "apiVersion": "2019-06-01-preview",
      "location": "[parameters('location')]",
      "name": "good", (1)
      "properties": {
        "resources": [
          {
            "type": "auditingSettings",
            "apiVersion": "2019-06-01-preview",
            "name": "DefaultAuditingSettings",
            "properties": {
              "state": "Enabled"
            }
          }
        ]
      }
    }
  ]
}

1	SQL Server that has auditing enabled.

Terraform

resource "azurerm_mssql_server" "sql_server" {
  name = var.mssql.name
  # ... more properties ...
}

resource "azurerm_mssql_server_extended_auditing_policy" "audit_policy" {
  server_id = azurerm_mssql_server.sql_server.id (1)
  # ...
  storage_endpoint           = azurerm_storage_account.example.primary_blob_endpoint
  storage_account_access_key = azurerm_storage_account.example.primary_access_key
  storage_account_access_key_is_secondary = true
  retention_in_days          = 90 (1)
}

1	FIXED, enough retention time.

Runtime

Azure Portal

To change the policy Log in to Azure Portal and then:

Navigate to SQL servers and for each instance:
- Click Auditing and set to On.

CLI Command

To get the list of all SQL Servers, use the following command:

$ Get-AzureRmSqlServer

See Return information about SQL Database servers..

To enable auditing for each Server, use the following command:

$ Set-AzureRmSqlServerAuditingPolicy
-ResourceGroupName <resource group name>
-ServerName <server name>
-AuditType <audit type>
-StorageAccountName <storage account name>

See Change the auditing policy of a SQL Database server.