Modify SQL Audit for Azure SQL Database

At SQL Server Live last November, I demonstrated enabling SQL Audit for Azure SQL Database. During the class discussion I explained you must use Powershell to modify SQL Audit for Azure SQL Database. So, that’s my post today, showing you how it is done.

By default, SQL Audit for Azure SQL Database will enable the following:

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP
BATCH_COMPLETED_GROUP

If you want to alter that list, you must use Powershell. There is no GUI available. (If you connect to Azure SQL Database with SQL Server Management Studio v18.4 you will notice there is no option for Audit Specifications. I believe this should be possible at some point, so feel free to go upvote this suggestion.)

Using Set-AzSqlServerAudit

Let’s look at how to enable the DATABASE_PERMISSION_CHANGE_GROUP audit action group. I chose that Action Group for two reasons. First, it’s part of the list I recommend for anyone using SQL Audit along with Security Event Manager. Second, because I was curious to track activity for granting UNMASK when using Dynamic Data Masking.

Here’s some sample code that I used to add the DATABASE_PERMISSION_CHANGE_GROUP audit action group:

Set-AzSqlServerAudit -ResourceGroupName RGname -ServerName Server -AuditActionGroup SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP, BATCH_COMPLETED_GROUP, DATABASE_PERMISSION_CHANGE_GROUP

You can then use Get-AzSqlServerAudit to verify the change:

Viewing Audit Logs with Log Analytics

To test the activity is captured, I grant and revoke UNMASK to a user. I’m pushing the audit logs to Log Analytics, which returns the rows as expected:

From there we can build rules and alerts as needed.

Summary

I have been an advocate of SQL Audit for years. I was happy to see it added to Azure a while back. However, to modify SQL Audit for Azure SQL Database you must use Powershell. I’m hopeful Microsoft will get this functionality into SSMS at some point in the near future.

Together with Karen López, we will be delivering a full training day at SQL Konferenz in March. The title of our session is Advanced Data Protection: Security and Privacy Assessments in SQL Server. The above is a sample of the updated content Karen and I will be sharing. If you are in or around Darmstadt on the 3rd of March, we’d love to see you in our class.

REFERENCES:

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver15
https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqlserveraudit?view=azps-3.4.0
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver15