Configure Auditing for Azure SQL Database series - Part2

 

In the previous blog, we covered how to configure server level audit and database level audit for Azure SQL database with default settings. In this blog we will see how you can manage auditing using PowerShell cmdlets.  

 

PowerShell cmdlets to manage auditing for Azure SQL database: 

 

Get-AzSqlServerAudit  - Get Server Auditing Policy 

Get-AzSqlDatabaseAudit - Get Database Auditing Policy 

Set-AzSqlDatabaseAudit  - Create or Update Database Auditing Policy 

Set-AzSqlServerAudit  - Create or Update Server Auditing Policy  

Remove-AzSqlDatabaseAudit - Remove Database Auditing Policy 

Remove-AzSqlServerAudit  - Remove Server Auditing Policy 

 

Refer manage Azure SQL database auditing using Azur PowerShell documentation here for more details.  

 

Sometimes you might have a requirement to customize auditing for Azure SQL database to meet compliance or achieve parity with your on-premises servers or just audit specific details to meet business needs.  

 

Using PowerShell cmdlets, you can configure auditing to audit specific action groups and use predicate expressions to filter queries, logins, schemas, and databases.  

 

Before we modify the audit settings let us verify the existing server and database audit configuration  

 

• Review the existing configuration   

1. Set the context to your subscription   Set-AzContext -Subscription "xxx-xxxx-xxxx-xxxx" 
2. Check server audit configuration   Get-AzSqlServerAudit "resource group name " "servername"     

        

 

 You can see server audit is enabled with default action groups and target as log analytics workspace  

 

3. Check database audit configuration  

 Get-AzSqlDatabaseAudit "resource group name" "servername" "database name" 

 

  

 

Database audit is disabled, and you can see no action groups configured and all targets are in disabled state.  

 

Here we are discussing customizing audit settings based on a few sample scenarios and you can extend it further based on your requirements.  

 

• Scenario 1: Exclude a specific login from auditing.   

Set-AzSqlServerAudit -ResourceGroupName "test" -ServerName "test" -BlobStorageTargetState Enabled -StorageAccountResourceId "xxxxxx" -PredicateExpression "[server_principal_name]!= 'dba'" 

 

• Scenario 2: Exclude a login and specific database from auditing  

Set-AzSqlServerAudit -ResourceGroupName "test" -ServerName "test" -BlobStorageTargetState Enabled -StorageAccountResourceId "xxxxxx" -PredicateExpression "[server_principal_name] != ‘dbadmin’ and [database_name] != 'audit'" 

 

• Scenario 3: Exclude select statements from auditing   

Set-AzSqlServerAudit -ResourceGroupName "test" -ServerName "sravstestdb" -PredicateExpression "statement not like '[select ]%'" 

Set-AzSqlServerAudit -ResourceGroupName "test" -ServerName "sravstestdb" -PredicateExpression "statement <> 'exec sp_executesql N’SELECT%’'" 

 

• Scenario 4: Audit different action groups instead of default ones    

Set-AzSqlServerAudit -ResourceGroupName "test" -ServerName "test" -LogAnalyticsTargetState Enabled -WorkspaceResourceId "/subscriptions/fa58cf66-caaf-xxxxxxx-xxxxxxxxx/resourceGroups/test/providers/Microsoft.OperationalInsights/workspaces/test" -AuditActionGroup SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP","FAILED_DATABASE_AUTHENTICATION_GROUP","DATABASE_OBJECT_CHANGE_GROUP"  

 

• Scenario 5: use predicate expression to exclude transaction related actions from batch completed action group

       Set-AzSqlDatabaseAudit -ResourceGroupName "test" -ServerName "test" -LogAnalyticsTargetState Enabled -WorkspaceResourceId       "/subscriptions/fa58cf66-caaf-xxxxxxx-xxxxxxxxx/resourceGroups/test/providers/Microsoft.OperationalInsights/workspaces/test" -AuditActionGroup "Batch_completed_group" -PredicateExpression "additional_information not like '%<transaction_info>%"

 

• Scenario 6:  use predicate expression to exclude action from a given action group. the below example shows filtering RPC completed events from batch completed action group. to get action id, please refer Filter SQL Server Audit on action_id 

 Set-AzSqlServerAudit -ResourceGroupName "RESOURCE_GROUP" -ServerName "SERVER_NAME" -BlobStorageTargetState Enabled -StorageAccountResourceId "STORAGE_ACCOUNT_RESOURCE_ID" -PredicateExpression "action_id != 541934418"

   

 

Note: Both Set-AzSqlServerAudit and Set-AzSqlDatabaseAudit overwrites the existing configuration, so when you modify you need to add the default action groups if you still want to audit them.  

 

Review the server audit settings, you can see auditing is configured with different action groups and filters.  

 

 

 In this blog we have successfully configured server audit with the required audit action groups and filter expressions.