Skip to content

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>'
Last update: 2022-12-03

Comments