Configure server and database audits

In this we will learn how to configure server and database audits.

Auditing for Azure SQL Database and Azure Synapse Analytics

Database events are tracked and written to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs via auditing for Azure SQL Database and Azure Synapse Analytics. Auditing also includes the following:

Firstly, helps you maintain regulatory compliance, understand database activity, and gain insight into discrepancies. And, this could indicate business concerns or suspected security violations.
Secondly, enables and facilitates adherence to compliance standards, although it doesn’t guarantee compliance.

Auditing limitations

Firstly, premium storage is currently not supported.
Secondly, hierarchical namespace for Azure Data Lake Storage Gen2 storage account is currently not supported.
Thirdly, enabling auditing on a paused Azure Synapse is not supported. To enable auditing, resume Azure Synapse.

Define server-level vs. database-level auditing policy

In Azure (which hosts SQL Database or Azure Synapse), an auditing policy may be established for an individual database or as a default server policy:

Firstly, a server policy applies to all existing and newly created databases on the server.
Secondly, if server auditing is enabled, it always applies to the database. The database will be audited, regardless of the database auditing settings.
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. In other words, the database is audited twice in parallel; once by the server policy and once by the database policy.

Set up auditing for your server

All activities and the following set of action groups are included in the default auditing policy, which audits all queries and stored procedures conducted against the database, as well as successful and failed logins:

Firstly, BATCH_COMPLETED_GROUP
Secondly, SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
Lastly, FAILED_DATABASE_AUTHENTICATION_GROUP

You should be aware that in an audit record, Azure SQL Database and Azure Synapse Audit save 4000 characters of data for character fields. When an auditable action’s statement or data sensitivity information values comprise more than 4000 characters. Furthermore, any data that exceeds 4000 characters will be truncated and will not be audited.

The following section describes 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, you can select the View server settings link on the database auditing page. You can then view or modify the server auditing settings. Server auditing policies apply to all existing and newly created databases on this server.
After that, if you prefer to enable auditing on the database level, switch Auditing to ON. If server auditing is enabled, 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. However, you can write logs to an Azure storage account, to a Log Analytics workspace for consumption by Azure Monitor logs (preview), or to event hub for consumption using event hub (preview).

Auditing of Microsoft Support operations

You may audit Microsoft support engineers’ activities when they need to access your server during a support request with Azure SQL Server’s auditing of Microsoft support operations. This functionality, together with your audits, offers more transparency in your workforce, as well as anomaly detection, trend visualization, and data loss prevention. To enable Auditing of Microsoft Support Operations (Preview), go to Auditing under Security in your Azure SQL server pane and turn Auditing of Microsoft Support Operations (Preview) on.

Use the following query to inspect the audit logs of Microsoft Support activities in your Log Analytics workspace:

Kusto
AzureDiagnostics
| where Category == “DevOpsOperationsAudit”

Audit to storage destination

Select Storage and open Storage details to set writing audit logs to a storage account. Then, choose the Azure storage account where the logs will be kept, and then the retention term. Then press OK. Finally, logs that have been kept for longer than the retention term are erased.

However, the retention period is set at 0 by default (unlimited retention). When establishing the storage account for auditing, you may alter this number by sliding the Retention (Days) slider under Storage settings.

Analyze audit logs and reports

If you chose to write audit logs to Azure Monitor logs:

Firstly, use the Azure portal. Open the relevant database. At the top of the database’s Auditing page, select View audit logs.
Then, you have two ways to view the logs:
- Clicking on Log Analytics at the top of the Audit records page will open the Logs view in Log Analytics workspace, where you can customize the time range and the search query.
- Clicking View dashboard at the top of the Audit records page will open a dashboard displaying audit logs info, where you can drill down into Security Insights, Access to Sensitive Data and more.
Alternatively, you can also access the audit logs from Log Analytics blade. Open your Log Analytics workspace and under General section, click Logs. You can start with a simple query, such as: search “SQLSecurityAuditEvents” to view the audit logs. From here, you can also use Azure Monitor logs to run advanced searches on your audit log data.

If you chose to write audit logs to Event Hub:

Firstly, to consume audit logs data from Event Hub, you will need to set up a stream to consume 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.

If you choose to save audit logs to an Azure storage account, you have numerous options for viewing them:

Firstly, audit logs are aggregated in the account you chose during setup. 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 named sqldbauditlogs.
Secondly, use the Azure portal. Open the relevant database. At the top of the database’s Auditing page, click View audit logs.

Audit records opens, from which you’ll be able to view the logs.

You can view specific dates by clicking Filter at the top of the Audit records page.
Next, you can switch between audit records by the server audit policy and the database audit policy by toggling Audit Source.
You can view only SQL injection related audit records by checking Show only audit records for SQL injections checkbox.

Thirdly, use the system function sys.fn_get_audit_file (T-SQL) to return the audit log data in tabular format.
Then, use Merge Audit Files in SQL Server Management Studio (starting with SSMS 17):
- From the SSMS menu, select File > Open > Merge Audit Files.
- Then, the Add Audit Files dialog box opens. Select one of the Add options to choose whether to merge audit files from a local disk or import them from Azure Storage. Here, you have to provide your Azure Storage details and account key.
- 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. 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.

Production practices

Auditing geo-replicated databases

When you activate auditing on the primary database in a geo-replicated database, the secondary database will have the same auditing policy. However, independent of the primary database, it is feasible to set up auditing on the secondary database by activating auditing on the secondary server.

Firstly, Server-level (recommended): Turn on auditing on both the primary server as well as the secondary server – 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 be configured from Primary database auditing settings.
- Auditing must be enabled on the primary database itself, not the server.
- After auditing is enabled on the primary database, it will also become enabled on the secondary database.