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
- Set the context to your subscription Set-AzContext -Subscription "xxx-xxxx-xxxx-xxxx"
- 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 "srsaluru-rg-india" -ServerName "sravstestdb" -PredicateExpression "statement not like '[select ]%'"
Set-AzSqlServerAudit -ResourceGroupName "srsaluru-rg-india" -ServerName "sravstestdb" -PredicateExpression "statement <> 'exec sp_executesql N’SELECT%’'"
- Scenario 4: Audit different action groups instead of default ones
Set-AzSqlServerAudit -ResourceGroupName "srsaluru-rg-india" -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"
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.
Posted at https://sl.advdat.com/3uDVlsYhttps://sl.advdat.com/3uDVlsY
