Quick Track: Beginner’s Guide to Azure SQL- Auditing in Azure SQL

I know it’s been a while since I blogged. It’s been busy at work and I felt I had to take some time off. Firstly, I would like to apologize to the regular readers of this blog and I would like to thank some of them for remembering me and reaching out to me asking to write. Thanks for checking on how I was doing.

As a continuation of the Quick track series on beginner’s guide to Azure SQL, this post is about Auditing in Azure SQL.

As you all know how crucial it is to Audit activity on the Server for both prod and non-prod environments, turning on the auditing in Azure SQL is pretty simple and the results we see in the audit log are similar to the logs we see on-prem. The difference is where we save the audit data in Azure.

You can enable the auditing at the Server level and at the database level just like the way we can audit SQL Server on-prem. If you would like to enable audit at the Server level in Azure, it will automatically audit all the databases under that Server. If we allow the auditing at the server level (logical Server for Azure SQL Databases) and also at the database level, we might get double the amount of collected audit data as it contains the same data twice. Always chose the Storage account if you wanted to audit the data at the Server level. If you just want to collect the audit data on one or some databases only, you can disable the logical Server level audit and enable the Auditing at the database level.

If you want to specifically use a different storage method from the other audited databases or wanted to modify the default action policy groups to any specific databases, you can choose the database level auditing instead of the Server level auditing. By default, the login activity, queries, and stored procedures are audited.

The default audit policy for the Server and the Azure SQL databases includes

1. BATCH_COMPLETED_GROUP –
2. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
3. FAILED_DATABASE_AUTHENTICATION_GROUP

To enable the Auditing at the logical Server level- Go to the auditing blade on the left under the logical Server> Enable SQL Server Auditing> turn on.

Chose the audit log destination among the storage/Log analytics/Event Hub. You need to select the subscription and provide the log analytics workspace. If you already have one, select it or create one right here if you do not have one. If you need any kind of support from the Microsoft support team in the future, make sure to enable the option “Enable Auditing of Microsoft support operations” so they can have access to your logs when they work with you.

If you are choosing Server level auditing, then select the storage as destination and you will have to choose the authentication type: Managed identity (system-assigned and user-assigned managed identity is supported as per Microsoft). You can choose the retention period here as well. 0 represents unlimited retention. Changes made to the retention period will only effect the future collection of the Audit data. Any collected information while it was set to unlimited will be always retained.

To specifically enable the auditing at the database level, go to the database and select the auditing blade on the left side> Enable Azure SQL Auditing> Turn on

To view the audit logs of the database>go to the auditing blade on the left side>click auditing>click on view audit logs.

You can see the audit logs of the database. You will see the query where you can edit and filter the results.

If you would like to modify or view the audit policy through Powershell, there are some command lets you can use.  Source: Microsoft

Get-AzSqlServerAudit – Will give the Server level audit policy

Set-AzSqlServerAudit  – You can create or modify the Server audit policy

Remove-AzSqlServerAudit  – use to remove Server audit policy 

Get-AzSqlDatabaseAudit – Will give the database-level audit policy

Set-AzSqlDatabaseAudit  – You can create or modify the database audit policy

Remove-AzSqlDatabaseAudit – use to remove the database audit policy 

Here is an interesting question asked by a user on how to filter out specific login as it causes a huge amount of data. You can find the resolution here.

You can also check the audit logs from the home blade>monitor>logs. You can see the same audit information here.

For more examples on how to add or modify the auditing policy, here is the article from Microsoft you can follow.

Hope this blog post gave you an overview of auditing in Azure SQL.

Thanks for reading!