Server and Database Level Auditing for Azure SQL databases


By:   |   Updated: 2021-02-10   |   Comments   |   Related: More > Azure


3 Ways to move databases to the cloud and back, which way is best for you?

Free MSSQLTips Webinar: 3 Ways to move databases to the cloud and back, which way is best for you?

Let's explore the different mechanisms offered for SQL Server data migration back and forth between on-premises and the cloud solutions.


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
Azure SQL Database sample DB

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.

Server-level auditing

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.

Log Analytics and Event hub destinations

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

Storage 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.

Audit retention 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.

Storage settings

Review your auditing configurations as shown below and click Save.

Save the configurations

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

Success message

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.

Enable Auditing

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

Configure the server level auditing

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

database audit

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.

View audit files in the Azure storage account

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.

database container

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

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.

event type, principal time

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.

get detailed information

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.

additional information and query

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

Run in Query editor

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.

Connect to SQL database

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.

SQL query for audit records

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.

Modify SQL query

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.

JSON, CSV or XML format

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

result in the JSON format

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.

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"
Azure PowerShell cmdlet

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

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'"
Use Filter predicate

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.

update and insert statements

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

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

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

disable the server level audit
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.


Last Updated: 2021-02-10


get scripts

next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources



Comments For This Article





download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Process Blob Files Automatically using an Azure Function with Blob Trigger

Azure SQL Cross Database Query

Reading and Writing data in Azure Data Lake Storage Gen 2 with Azure Databricks














get free sql tips
agree to terms