Enable auditing for Azure SQL DB server

March 21, 2025 ยท View on GitHub

SYNOPSIS

Enable auditing for Azure SQL logical server.

DESCRIPTION

Auditing for Azure SQL Database tracks database events and writes them to an audit log. Data collected from auditing can be used to help find suspicious events, unusual activity, and trends.

When managing security events at scale, it is important to have a centralized location to store and analyze security data. A security information and event management (SIEM) system to consolidate security data in a central location. Once the security data is in a central location it can be correlated across various services. Security orchestration, automation, and response (SOAR) tools can be used to automate responses to security events.

Microsoft Sentinel is a scalable, cloud-native solution that provides:

  • Security information and event management (SIEM).
  • Security orchestration, automation, and response (SOAR).

RECOMMENDATION

Consider enabling auditing for each SQL Database logical server and review reports on a regular basis. Also consider enforcing this setting using Azure Policy.

EXAMPLES

Configure with Azure template

To deploy logical servers that pass this rule:

  • Define a Microsoft.Sql/servers/auditingSettings sub-resource with each logical server.
  • Set the properties.state property to Enabled for the Microsoft.Sql/servers/auditingSettings sub-resource.

For example:

{
  "type": "Microsoft.Sql/servers/auditingSettings",
  "apiVersion": "2022-08-01-preview",
  "name": "[format('{0}/{1}', parameters('name'), 'default')]",
  "properties": {
    "isAzureMonitorTargetEnabled": true,
    "state": "Enabled",
    "retentionDays": 7,
    "auditActionsAndGroups": [
      "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
      "FAILED_DATABASE_AUTHENTICATION_GROUP",
      "BATCH_COMPLETED_GROUP"
    ]
  },
  "dependsOn": [
    "server"
  ]
}

Configure with Bicep

To deploy logical servers that pass this rule:

  • Define a Microsoft.Sql/servers/auditingSettings sub-resource with each logical server.
  • Set the properties.state property to Enabled for the Microsoft.Sql/servers/auditingSettings sub-resource.

For example:

resource server 'Microsoft.Sql/servers@2022-11-01-preview' = {
  name: name
  location: location
  identity: {
    type: 'SystemAssigned'
  }
  properties: {
    publicNetworkAccess: 'Disabled'
    minimalTlsVersion: '1.2'
    administrators: {
      azureADOnlyAuthentication: true
      administratorType: 'ActiveDirectory'
      login: adminLogin
      principalType: 'Group'
      sid: adminPrincipalId
      tenantId: tenant().tenantId
    }
  }
}

resource sqlAuditSettings 'Microsoft.Sql/servers/auditingSettings@2022-08-01-preview' = {
  name: 'default'
  parent: server
  properties: {
    isAzureMonitorTargetEnabled: true
    state: 'Enabled'
    retentionDays: 7
    auditActionsAndGroups: [
      'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP'
      'FAILED_DATABASE_AUTHENTICATION_GROUP'
      'BATCH_COMPLETED_GROUP'
    ]
  }
}

Configure with Azure CLI

az sql server audit-policy update -g '<resource_group>' -n '<server_name>' --state Enabled --bsts Enabled --storage-account '<storage_account_name>'

Configure with Azure PowerShell


Set-AzSqlServerAudit -ResourceGroupName '<resource_group>' -ServerName '<server_name>' -BlobStorageTargetState Enabled -StorageAccountResourceId '<storage_resource_id>'

Configure with Azure Policy

To address this issue at runtime use the following policies: