Skip to content

Enable auditing for Azure SQL DB server#

Security · SQL Database · Rule · 2020_06 · Important

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:

Azure Template snippet
{
  "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:

Azure Bicep snippet
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#

Azure CLI snippet
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#

Azure PowerShell snippet
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:

Comments