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#
Azure Template snippet
{
"comments": "Create or update an Azure SQL logical server.",
"type": "Microsoft.Sql/servers",
"apiVersion": "2019-06-01-preview",
"name": "[parameters('serverName')]",
"location": "[parameters('location')]",
"tags": "[parameters('tags')]",
"kind": "v12.0",
"identity": {
"type": "SystemAssigned"
},
"properties": {
"administratorLogin": "[parameters('adminUsername')]",
"version": "12.0",
"publicNetworkAccess": "[if(parameters('allowPublicAccess'), 'Enabled', 'Disabled')]",
"administratorLoginPassword": "[parameters('adminPassword')]",
"minimalTLSVersion": "1.2"
},
"resources": [
{
"type": "Microsoft.Sql/servers/auditingPolicies",
"apiVersion": "2014-04-01",
"name": "[concat(parameters('serverName'), '/Default')]",
"location": "[parameters('location')]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
],
"properties": {
"auditingState": "Enabled"
}
},
{
"type": "Microsoft.Sql/servers/auditingSettings",
"apiVersion": "2017-03-01-preview",
"name": "[concat(parameters('serverName'), '/Default')]",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', parameters('serverName'))]"
],
"properties": {
"state": "Enabled",
"retentionDays": 7,
"auditActionsAndGroups": [
"SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
"FAILED_DATABASE_AUTHENTICATION_GROUP",
"BATCH_COMPLETED_GROUP"
],
"storageAccountSubscriptionId": "[split(parameters('securityStorageAccountId'), '/')[2]]",
"isStorageSecondaryKeyInUse": false,
"isAzureMonitorTargetEnabled": false,
"storageEndpoint": "[reference(parameters('securityStorageAccountId'),'2019-06-01').primaryendpoints.blob]",
"storageAccountAccessKey": "[listKeys(parameters('securityStorageAccountId'),'2019-06-01').keys[0].value]"
}
}
]
}
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:
2022-12-03