Auditing for Azure SQL Database and Azure Synapse Analytics
In this we will learn about Auditing for Azure SQL Database and Azure Synapse Analytics with tracking database events and writing them to an audit log in your Azure storage account, Log Analytics workspace, or Event Hubs.
However, Auditing also:
- Firstly, helps you maintain regulatory compliance, understand database activity. And, also gain insight into discrepancies and anomalies that 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 in support.
- Thirdly, enabling auditing on a paused Azure Synapse is not in support. To enable auditing, resume Azure Synapse.
Define server-level vs. database-level auditing policy
An auditing policy can be defined for a specific database or as a default server policy in Azure (which hosts SQL Database or Azure Synapse):
- 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.
- Thirdly, 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.
Set up auditing for your server
The default auditing policy includes all actions and the following set of action groups, which will audit all the queries and stored procedures executed against the database, as well as successful and failed logins:
- Firstly, BATCH_COMPLETED_GROUP
- Secondly, SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
- Thirdly, FAILED_DATABASE_AUTHENTICATION_GROUP
However, you can configure auditing for different types of actions and action groups using PowerShell, as described in the Manage SQL Database auditing using Azure PowerShell section. 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 returned from an auditable action contain more than 4000 characters.
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.
- Then, if you prefer to enable auditing on the database level, switch Auditing to ON.
- Lastly, you have multiple options for configuring where audit logs will be written. 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 (Preview)
Auditing of Microsoft Support operations (Preview) for Azure SQL Server allows you to audit Microsoft support engineers’ operations when they need to access your server during a support request. The use of this capability, along with your auditing, enables more transparency into your workforce and allows for anomaly detection, trend visualization, and data loss prevention.
However, to enable Auditing of Microsoft Support operations (Preview) navigate to Auditing under the Security heading in your Azure SQL server pane, and switch Auditing of Microsoft support operations (Preview) to ON.
Further, to review the audit logs of Microsoft Support operations in your Log Analytics workspace, use the following query:
Kusto
AzureDiagnostics
| where Category == “DevOpsOperationsAudit”
Audit to storage destination
To configure writing audit logs to a storage account, select Storage and open Storage details. Select the Azure storage account where logs will be saved, and then select the retention period. Then click OK.
- However, the default value for retention period is 0 (unlimited retention). You can change this value by moving the Retention (Days) slider in Storage settings when configuring the storage account for auditing.
- And, if you change retention period from 0 (unlimited retention) to any other value, please note that retention will only apply to logs written after retention value was changed.
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.
However, 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.
And, 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 are aggregated in the account you chose during setup. You can explore audit logs by using a tool such as Azure Storage Explorer.
- Secondly, use the Azure portal. Open the relevant database. At the top of the database’s Auditing page, click View audit logs.
- Next, 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.
- 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.
- After all files to merge have been added, click OK to complete the merge operation.
- 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.
- Then , download log files from your Azure Storage blob container via the portal or by using a tool such as Azure Storage Explorer.
Reference: Microsoft Documentation