The Azure SQL Auditing feature is used to track database events and writes them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.
For those who are interested on how to configure Auditing on Azure SQL Database either on server-level or database-level please visit this Configure Auditing for Azure SQL Database series - Part 1 and Configure Auditing for Azure SQL Database series - Part 2
For those who are looking into enable Auditing for Azure SQL Database on a specific table as this option is not available on Azure Portal as of now. we don`t have any estimate date when this option is available on Azure Portal, but they can use the following PowerShell script that will help you enable Auditing on a specific table in Azure SQL Database.
In script I had given example table as (MyTable) targeting a table named MyTable in the DBO schema and want to Audit SELECT and INSERT actions.
# Define variables
$resourceGroup = "YourResourceGroup"
$serverName = "your-sql-server-name"
$databaseName = "your-database-name"
$storageAccount = "yourstorageaccount"
$tableName = "MyTable"
$schemaName = "dbo"
# Login to Azure
Connect-AzAccount
# Enable auditing at the database level
Set-AzSqlDatabaseAuditing `
-ResourceGroupName $resourceGroup `
-ServerName $serverName `
-DatabaseName $databaseName `
-StorageAccountName $storageAccount `
-AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP" `
-AuditAction "SELECT ON $schemaName.$tableName BY public", "INSERT ON $schemaName.$tableName BY public" `
-State Enabled
REST API - Manage Auditing Using APIs - Azure SQL Database & Azure Synapse Analytics | Microsoft Learn To enable auditing on a specific table in Azure SQL Database using the REST API, you can use the Create or Update Database Extended Auditing Policy endpoint. This allows you to define fine-grained auditing rules, including actions on specific tables.
URL
Reference Request Body:
{
"properties": {
"state": "Enabled",
"storageEndpoint": "https://.blob.core.windows.net/",
"storageAccountAccessKey": "",
"retentionDays": 90,
"auditActionsAndGroups": [
"SELECT ON dbo.MyTable BY public",
"INSERT ON dbo.MyTable BY public"
],
"isStorageSecondaryKeyInUse": false
}
}
Parameters:
- auditActionsAndGroups: This is where you specify the exact actions and the table. You can include SELECT, INSERT, UPDATE, DELETE, etc.
- storageEndpoint: The Azure Blob Storage endpoint where audit logs will be stored.
- retentionDays: Number of days to retain logs.
- state: Must be "Enabled" to activate auditing.
az sql db audit-policy update -g ResourceGroupName -s Servername -n DatabaseName --state Enabled --bsts Enabled --storage-key "" --storage-endpoint https://StorageAccount.blob.core.windows.net/ --actions FAILED_DATABASE_AUTHENTICATION_GROUP 'UPDATE ON dbo.MyTable BY public'
sample output
{
"auditActionsAndGroups": [
"FAILED_DATABASE_AUTHENTICATION_GROUP",
"UPDATE ON dbo.MyTable BY public"
],
"id": "/subscriptions/xxxxx-xxxxx-xxxxxx-xxxxx-xxxxxx/resourceGroups/ResourceGroupName/providers/Microsoft.Sql/servers/ServerName/databases/DatabaseName/auditingSettings/Default",
"isAzureMonitorTargetEnabled": true,
"isManagedIdentityInUse": false,
"isStorageSecondaryKeyInUse": false,
"kind": null,
"name": "Default",
"queueDelayMs": null,
"resourceGroup": "ResourceGroupName",
"retentionDays": 10,
"state": "Enabled",
"storageAccountAccessKey": null,
"storageAccountSubscriptionId": "xxxx-xxxxx-xxxx-xxxxx-xxxxxxx",
"storageEndpoint": https://StorageAccount.blob.core.windows.net/,
"type": "Microsoft.Sql/servers/databases/auditingSettings"
}
For more information
Configure Auditing for Azure SQL Database series - Part 1
Configure Auditing for Azure SQL Database series - Part 2
Manage Auditing Using APIs - Azure SQL Database & Azure Synapse Analytics | Microsoft Learn
Microsoft