Selective Auditing for Azure SQL Database
Published Mar 22 2022 11:16 PM 2,936 Views

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.






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



  FROM sys.database_audit_specification_details sd

  JOIN sys.database_audit_specifications s

  ON s.database_specification_id = sd.database_specification_id


  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



Version history
Last update:
‎Mar 22 2022 10:09 PM
Updated by: