Server and Database Level Auditing for Azure SQL databases

Problem

As you know, database security and compliance should be an integral part of your database environment. If your database stores financial or healthcare data, you should do regular data audits. Auditing is also required for understanding database activities, analyzing database security violations and adherence to compliance regulations such as SOX and PCI.

In this tip, we explore the following topics:

• How do we enable auditing in an Azure SQL Database?
• How to configure server and database level auditing?
• Where is the audit data stored?
• Query auditing data.

Solution

Azure SQL Database audit is useful for tracking certain database events and storing them in your Azure storage account, Event Hubs or Log Analytics. In the following sections, we look at auditing for Azure SQL Database.

Server-level and Database-level Auditing Policy

• You can configure both server and database level auditing.
• Server-level auditing is applicable for all databases (including any newly created databases).
• If you enable database level auditing, the database could be audited twice – once using the server auditing and another using database-level auditing. Therefore, you should enable database auditing in the case where any specific requirements are not met using the server level auditing. Otherwise, you should try to avoid enabling database level auditing.

Azure SQL Database default auditing policy enables all actions from the following audit groups:

1. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP: captures successful database authentication activities.
2. BATCH_COMPLETED_GROUP: includes events that capture query (stored procedure\ transaction) completion. It audits the entire batch or stored procedure, including the results.
3. FAILED_DATABASE_AUTHENTICATION_GROUP: captures failed database authentication activities.

Azure SQL Database Environment Details

To configure the auditing, you need to have an active Azure SQL Database. You can follow earlier articles on MSSQLTips for reference to getting started with Azure SQL Database.

In this tip, I use the following environment:

• Server name: azuredemoinstance.database.windows.net
• Location: Central India
• Status: Online
• Database: labazuresql
• Current version: Microsoft SQL Azure (RTM) 12.0.2000.8

Enable Database-level Auditing for Azure SQL Database using Azure Portal

In your SQL database dashboard, navigate to the Security section and click on Auditing. By default, auditing is disabled, and you get a message stating – Server-level Auditing: Disabled.

Click ON to enable the options for Audit log destination. You need to choose at least one log destination – Storage, Log Analytics or Event Hub. The Log Analytics and Event Hub destinations are in the preview phase.

For this tip, we will use the Storage option, so check that option. It will require some additional configurations.

Click on Configure in the storage setting, select subscription, storage account, retention (days) and storage access key. If you do not have a storage account, you can create a new storage account from this page as well.  In my demo, I use storage account azureauditdatastorage, as shown below.

The zero value for Retention is for an unlimited retention period. You can move the slider to configure the retention period with a maximum up to 3285 days.

Note: If we modify the retention policy to a specific number of days, it applies to logs written after the modification. The previous logs with unlimited retention will remain preserved.

Review your auditing configurations as shown below and click Save.

You’ll get a success message once the audit configurations are complete.

Enable Server-level Auditing for Azure SQL Database using Azure Portal

As stated earlier, we can enable the auditing at both server and database level. In the previous section, we enabled database level auditing. Before we move forward, disable the database level auditing by setting to OFF as per recommended best practice, so we don’t audit twice.

Click on View server settings.

Configure the server level auditing in the same way using Azure Storage.

Save your configuration and it enables the server level auditing for Azure SQL Database.

View audit files in the Azure storage account

Once you have configured either server or database level auditing, go to your resource group and open the azure storage account.

In the blob container, you have an individual container for master and user databases.

Go to your database container, and it has a folder sqldbAutiding_ServerAudit_NoRetention. In this folder, you’ll find XEL files. These XEL files use the extended events audit mechanisms for storing audit data.

View Server or Database Audit Logs

Now, to view the server or database audit logs, click on View audit logs.

It opens a new page to view the Audit records. In the audit source, select the required Server audit or database audit.

By default, it shows the event type, principal time, event timestamp and action status.

Get Detailed Audit Information

You can click on an individual record to get detailed information. For example, I tried to create a table in the labazuresql database. The object already exists; therefore, the query failed to execute. Now, click on the failed action. It displays the application, principal name, client IP, additional information along with the SQL query.

It shows the following additional information for my case. If you look carefully, it is the message published in SSMS when you execute the query.

<batch_information><failure_reason>Err 2714, Level 16, Server azuredemoinstanceThere is already an object named ‘temp’ in the database.</failure_reason></batch_information>

You can scroll down to check the query that we tried to execute for creating an object named temp.

Additionally, you can use Run in Query Editor to view the audit records.

Click on the Run in Query Editor and it opens the integrated database query editor. Specify your credentials in the SQL server authentication and connect.

It displays the SQL query to fetch records from the audit log files stored in the Azure storage. The SQL query uses sys.fn_get_audit_file() function for data retrieval.

The benefit of the SQL query is that you can customize it as per your requirement. For example, if we require to capture records satisfying special conditions or attributes, you can modify the query and execute it. As shown above, by default, it displays the top 100 records based on the event time in the descending order.

For example, by default, the query returns both successful and failed audit events. Suppose, we want to extract only failed events, you can modify the query, and it returns the output as shown below.

You can extract the output in a JSON, CSV or XML format. Click on the Export data as an option and choose the required format.

For example, I exported the result in the JSON format, and it gives my audit data, as shown below.

Use Azure PowerShell cmdlet for auditing in Azure SQL Database

We can use Azure PowerShell cmdlets for configuring and managing the auditing as well. Before we go further, you should refer to the following articles and be familiar with Azure PowerShell.

• Introduction to Azure PowerShell Modules for the SQL Server DBA Part 1
• Introduction to Azure PowerShell Modules for the SQL Server DBA Part 2
• Introduction to Azure PowerShell Modules for the SQL Server DBA Part 3

For a server level audit, we use Set-AzSqlServerAudit cmdlet. It requires the following arguments.

• Resource Group Name
• SQL Server Name
• BlobStorageTargetState
• StorageAccountResourceID

You can get these arguments values from the Azure Portal. For the StorageAccountResourceID, combine the subscription id, resource group and blob container.

Set-AzSqlServerAudit -ResourceGroupName "azuresqldemo" -ServerName "azuredemoinstance" -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/c6eb5552-7748-4d57-82bb-4c52c1c8f87e/resourceGroups/azuresqldemo/providers/Microsoft.Storage/storageAccounts/azuresqlauditdatastorage"

Execute the script and it enables the server level Azure SQL Auditing.

Use Filter predicate for audit data filtering

Usually, DBAs do not audit SELECT statements since they occur quite frequently and you may get lots of data that makes it challenging to analyze the audit records. We can use the argument PredicateExpression for configuring advanced auditing for Azure SQL Database.

In the below command, we use the -PredicateExpression argument and specify the condition to capture records that do not have SELECT statements.

Set-AzSqlServerAudit -ResourceGroupName "azuresqldemo" -ServerName "azuredemoinstance" -BlobStorageTargetState Enabled -StorageAccountResourceId "/subscriptions/c6eb5552-7748-4d57-82bb-4c52c1c8f87e/resourceGroups/azuresqldemo/providers/Microsoft.Storage/storageAccounts/azuresqlauditdatastorage" -PredicateExpression "statement <> 'Select  1'"

It enables the server level auditing with your modified condition. For demo purposes, I performed insert and update commands and viewed the audit data. Here, you get an update and insert statements. The select @@SPID comes as well because it is not selecting data from a table.

To disable the server level audit, we can specify the disabled value in the BlobStorageTargetState argument.

Set-AzSqlServerAudit -ResourceGroupName "azuresqldemo" -ServerName "azuredemoinstance" -BlobStorageTargetState Disabled

You can refresh the Azure portal for SQL database to confirm that your server-level audit is now disabled.

Next Steps

• Evaluate your audit requirements and configure server or database level audits for Azure SQL Database.
• You should avoid configuring both server and database audits unless you have specific requirements.
• Refer to these other SQL Server Azure Tips.