Enable auditing for Azure SQL DB server#
Security · SQL Database · 2020_06
Enable auditing for Azure SQL logical server.
Description#
Auditing for Azure SQL Database tracks database events and writes them to an audit log. Audit logs help you find suspicious events, unusual activity, and trends.
Recommendation#
Consider enabling auditing for each SQL Database logical server and review reports on a regular basis.
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 toEnabled
for theMicrosoft.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 toEnabled
for theMicrosoft.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>'
Links#
Last update:
2023-07-26