Auditing for Azure SQL Database

Go back to AZ-500 Tutorials

In this tutorial, we will learn and understand about auditing for Azure SQL Database and Azure Synapse Analytics. Then, we will track database events and write them to an audit log in Azure storage account, Log Analytics workspace, or Event Hubs.

Overview

SQL Database auditing can be useful in:

Firstly, retaining an audit trail of selected events. Moreover, you can define categories of database actions to be audited.
Secondly, reporting on database activity. That is to say, you can use pre-configured reports and a dashboard for getting started quickly with activity and event reporting.
Thirdly, analyzing reports. Moreover, you can find suspicious events, unusual activity, and trends.

Defining server-level vs. database-level auditing policy

An auditing policy is for a specific database or as a default server policy in Azure:

Firstly, a server policy applies to all existing and newly created databases on the server.
Secondly, if server auditing is enabled, then it always applies to the database.
Lastly, enabling auditing on the database, in addition to enabling it on the server, does not override or change any of the settings of the server auditing. Both audits will exist side by side.

Setting up auditing for your server

The default auditing policy contains all actions and the set of action groups, which will audit all the queries and procedures executed against the database, as well as successful and failed logins:

BATCH_COMPLETED_GROUP
SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP

However, Azure SQL Database and Azure Synapse Audit stores 4000 characters of data for character fields in an audit record. And, when the statement or the data_sensitivity_information values returns from an auditable action contain more than 4000 characters. Any data beyond the first 4000 characters truncate and will not audit. The following section explains the configuration of auditing using the Azure portal.

Firstly, go to the Azure portal.
Secondly, navigate to Auditing under the Security heading in your SQL database or SQL server pane.
Thirdly, if you prefer to set up a server auditing policy, then you can select the View server settings link on the database auditing page. Moreover, you can then view or modify the server auditing settings.
Fourthly, if you prefer to enable auditing on the database level, then switch Auditing to ON. However, if server auditing enables, then the database-configured audit will exist side-by-side with the server audit.
Lastly, you have multiple options for configuring where audit logs will be written. Moreover, you can write logs to an Azure storage account, to a Log Analytics workspace for consumption by Azure Monitor logs or to event hub for consumption using event hub.

Auditing to storage destination

For configuring writing audit logs to a storage account, select Storage and open Storage details. After that, select the Azure storage account where logs will be saved, and then select the retention period. Then click OK. Logs older than the retention period are deleted.

However, the default value for retention period is 0 (unlimited retention). So, you can change this value by moving the Retention (Days) slider in Storage settings when configuring the storage account for auditing.

Auditing for storage destination — **Image Source: Microsoft**

Auditing to Log Analytics destination

For configuring writing audit logs to a Log Analytics workspace, select Log Analytics (Preview) and open Log Analytics details. After that, select or create the Log Analytics workspace where logs will be written and then click OK.

Auditing for log analytics — **Image Source: Microsoft**

Audit to Event Hub destination

For configuring writing audit logs to an event hub, select Event Hub (Preview) and open Event Hub details. After that, select the event hub where logs will be written and then click OK.

Analyzing audit logs and reports

If you select to write auditing logs for Azure Monitor logs:

Firstly, use the Azure portal. Then, open the relevant database. After that, at the top of the database’s Auditing page, select View audit logs.
Then, you have two ways to view the logs:

Firstly, clicking on Log Analytics at the top of the Audit records page will open the Logs view in Log Analytics workspace. There you can customize the time range and the search query.
Secondly, clicking the View dashboard at the top of the Audit records page will open a dashboard displaying audit logs info. There you can drill down into Security Insights, Access to Sensitive Data and more.

Side by side, you can also access the audit logs from the Log Analytics blade. For this, open your Log Analytics workspace and under the General section, click Logs. Then, you can start with a simple query, such as: search “SQLSecurityAuditEvents” for viewing the audit logs.

However, if you chose to write audit logs to Event Hub:

Firstly, for consuming audit logs data from Event Hub, you will need to set up a stream for consuming events and write them to a target.
Secondly, audit logs in Event Hub are captured in the body of Apache Avro events and stored using JSON formatting with UTF-8 encoding. However, for reading the audit logs, you can use Avro Tools or similar tools that process this format.

Next, if you chose to write audit logs to an Azure storage account, there are several methods you can use to view the logs:

Firstly, audit logs aggregates in the account you chose during setup. Moreover, you can explore audit logs by using a tool such as Azure Storage Explorer. In Azure storage, auditing logs are saved as a collection of blob files within a container that is sqldbauditlogs.
Secondly, use the Azure portal. After that, open the relevant database. Then, at the top of the database’s Auditing page, click View audit logs.
Thirdly, use the system function sys.fn_get_audit_file (T-SQL) for returning the audit log data in tabular format.
Then, use Merge Audit Files in SQL Server Management Studio (starting with SSMS 17):

Firstly, from the SSMS menu, select File > Open > Merge Audit Files.
Then, the Add Audit Files dialog box opens. And there, select one of the Add options for choosing whether to merge audit files from a local disk or import them from Azure Storage.
Thirdly, after all files to merge have been added, click OK to complete the merge operation.
Lastly, the merged file opens in SSMS, where you can view and analyze it, as well as export it to an XEL or CSV file, or to a table.

After that, use Power BI. Then, you can view and analyze audit log data in Power BI.
Lastly, download log files from your Azure Storage blob container via the portal or by using a tool such as Azure Storage Explorer.

Auditing geo-replicated databases

With geo-replicated databases, when you enable auditing on the primary database then the secondary database will have an identical auditing policy. However, it is also possible to set up auditing on the secondary database by enabling auditing on the secondary server, independently from the primary database.

Firstly, server-level: This turns on auditing on both the primary server as well as the secondary server. However, the primary and secondary databases will each be audited independently based on their respective server-level policy.
Secondly, database-level: database-level auditing for secondary databases can only configure from Primary database auditing settings. For this, the auditing is enabled on the primary database itself, not the server. And, after enabling the auditing on the primary database, it will also become enabled on the secondary database.

Reference: Microsoft Documentation