Selective Auditing for Azure SQL Database
Published Mar 22 2022 11:16 PM 1,950 Views
Microsoft

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"

 

sakshigupta_0-1648011551719.png

 

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.

 

sakshigupta_1-1648011551723.png

 

Happy Learning!

 

Reference Link

 

Set-AzSqlServerAudit (Az.Sql) | Microsoft Docs

https://docs.microsoft.com/en-us/azure/azure-sql/database/auditing-overview

 

 

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