Blog Post

Azure Database Support Blog
2 MIN READ

Selective Auditing for Azure SQL Database

sakshigupta's avatar
sakshigupta
Icon for Microsoft rankMicrosoft
Mar 23, 2022

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

 

 

Updated Mar 23, 2022
Version 1.0
No CommentsBe the first to comment