Azure SQL server audit log retention is too low

sql_server_auditing_retention

Severity

low

Vendor

Azure

Resource

MSSQL server

Description

SQL servers with auditing enabled should have enough retention time.

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.

See Auditing for Azure SQL Database for more details.

We recommend you configure SQL server audit retention to be greater than 90 days. The minimum retention days could be configured in the minimumRetentionDays parameter.

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

1	SQL Server that has a too-low audit log retention.

Terraform

resource "azurerm_mssql_server" "sql_server" {
  name                         = var.mssql.name
  # ... same as before ...
}

resource "azurerm_mssql_server_extended_auditing_policy" "audit_policy" {
  server_id = azurerm_mssql_server.sql_server.id
  # ...
  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	Retention lower than required.

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",
              "retentionDays": 90,
            }
          }
        ]
      }
    }
  ]
}

1	SQL Server that has proper log retention settings.

Terraform

resource "azurerm_mssql_server" "sql_server" {
  name                         = var.mssql.name
  # ... same as before ...
}

resource "azurerm_mssql_server_extended_auditing_policy" "audit_policy" {
  server_id = azurerm_mssql_server.sql_server.id
  # ...
  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 # FIXED
}

Runtime

Azure Portal

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

Navigate to SQL servers and for each instance:
- Click Auditing.
- Select Storage Details.
- Set Retention (days) to greater than 90 days.
- Click OK and Save.

CLI Command

To set the retention policy for more than or equal to 90 days, for each server, use the following command:

$ Set-AzureRmSqlServerAuditing
-ResourceGroupName <resource group name>
-ServerName <server name>
-RetentionInDays <Number of Days to retain the audit logs, should be 90days minimum>

See Changes the auditing settings of an Azure SQL server.

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.