How to enable selective Auditing in Azure SQL?
Auditing can help you track database related event, and we can write them to blob storage. Auditing can be enabled using portal or via powershell.
Default Auditing would capture all 3 below events.
BATCH_COMPLETED_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP
Based on the workload, data captured in Audit logs would be quite huge and would cost high. So, you may further like to filter and capture only the data which is required for auditing purpose and can be achieved using powershell. Auditing can be configured for different types of action groups using PowerShell.
Blob Storage
Set-AzSqlServerAudit can be used to enable to selective auditing along with -AuditActionGroup and -Predicate Expressions.
Below is the sample Powershell command that would help you to enable selective auditing. Here, auditing is enabled only for batch completed and batch started group and using predicate expression to further filter the data only for customer table.
1) Connect-AzAccount -subscription SubscriptionID
2) Modify the command as needed and execute to enable selective auditing.
Set-AzSqlServerAudit -ResourceGroupName "ResourcegroupName" -ServerName "SQLLogicalServerName" -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/SubscriptionID/resourceGroups/ResourcegroupName/providers/Microsoft.Storage/storageAccounts/StorageAccountName" -AuditActionGroup "BATCH_STARTED_GROUP", "BATCH_COMPLETED_GROUP" -PredicateExpression "STATEMENT LIKE '%customers%'"
Once the selective Auditing is enabled, you can run the powershell to view the configuration. Below powershell would return the result from master DB. Server level auditing would default enable auditing for database with same configurations. So, there is no need to enable DB level auditing in specific if server level is already enabled.
3) Validate the audit configuration.
Using Powershell
Get-azsqlserveraudit -ResourceGroupName "ResourcegroupName" -ServerName "SQLLogicalServerName"
Using T-SQL
SELECT *
FROM sys.database_audit_specification_details sd
JOIN sys.database_audit_specifications s
ON s.database_specification_id = sd.database_specification_id
WHERE
s.is_state_enabled = 1
ORDER BY sd.audit_action_name
4) Validate the audit logs.
I have executed couple of command to capture data against multiple tables but could see data captures only for customer table as expected in audit logs.
Happy Learning!
Reference Link
Set-AzSqlServerAudit (Az.Sql) | Microsoft Docs
https://docs.microsoft.com/en-us/azure/azure-sql/database/auditing-overview
Microsoft
