SQL Security Audit Logs in Azure Log Analytics Workspace
In today’s digital landscape, safeguarding data and maintaining regulatory compliance are paramount. Azure SQL Database stands out as a robust solution, with its comprehensive logging capabilities playing a crucial role. At the forefront are its Audit Logs—indispensable tools that not only ensure adherence to regulatory standards but also act as vigilant sentinels against potential security threats. By meticulously recording security activities, these logs empower organizations to trace and thwart any suspected malicious activities, providing peace of mind in an increasingly complex cyber world.
In this blog, we will discuss how to send your audit logs to a Log Analytics Workspace which provides a rich set of reports into the audits. The figure below provides an overview.
What is Azure Log Analytics?
Azure’s Log Analytics is a robust Azure service designed to enhance business intelligence by analyzing logs from various Azure services. Through its advanced capabilities, Log Analytics enables users to perform in-depth log analysis using sophisticated queries and interactive graphs. This functionality allows businesses to extract valuable data insights, optimize performance, and drive smarter decision-making. The costs depend on data ingestion volumes, pricing tiers, and selected solutions. Azure’s Log Analytics stands out as an essential tool for businesses seeking to improve their data analysis strategies, ensuring they maintain a competitive edge in an increasingly data-driven world.
How to set up Azure Log Analytics
Setting up Log Analytics for an Azure SQL Server involves two steps:
- Creating a Log Analytics workspace
- Enabling auditing on Azure SQL and its configuration for Log Analytics
To create a Log Analytics workspace go to the Log Analytics workspaces list and click on Create.
Here you select the Subscription and Resource Group and provide a name, and the region where this workspace will be created.
Once the Log Analytics workspace is created, you can view it in the list of Log Analytics workspaces.
How to configure an Azure SQL server
Next, we move on to configuring an Azure SQL Server so that audit logs are sent to the Log Analytics workspace created above.
Go to the Azure SQL Server in the Azure portal and click on Auditing in the left-hand navigation menu.
This page will provide options to enable auditing, and destinations to send audit logs to. Click on the “Enable Azure SQL Auditing” checkbox and select Log Analytics. In the dropdown select your subscription and a Log Analytics workspace. Select the same subscription in which you created the workspace in the earlier steps and next select the workspace from the dropdown. Click on the Save button once done and this will now send SQL Audit logs to Log Analytics.
To test if SQL Audit Logs are being sent to Logic Analytics workspace, try connecting to the Azure SQL Server using SSMS and enter a wrong username and password. This will throw you an error message and since the login is an Audit event it should have been captured in the log.
To view the audit event in the Log Analytics open the workspace click on logs and run the following query
The result of the above query will appear and you will see a list of authentication failures which are failed logins.
Configuring audit logs results in the following types of audit events being sent to the workspace:
- BATCH_COMPLETED_GROUP
- SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
- FAILED_DATABASE_AUTHENTICATION_GROUP
Once Log Analytics are being used on a regular basis the cost of the workspace can be viewed by opening the workspace in the portal and clicking on the “Usage and Estimated costs” link.
Logs within the workspace are saved into the “AzureDiagnostics” table and category is “SQLSecurityAuditEvents”. Click on Manage table here will provide information on how long the logs should be retained for interactive queries as well as for archival purposes.
In conclusion, setting up auditing for Azure SQL Server and configuring audit logs to be accessed through a Log Analytics workspace is crucial for robust performance monitoring and security management. Azure’s Log Analytics offers powerful tools for visualizing data, utilizing comprehensive queries, workbooks, and visuals to present insights that enhance decision-making. By leveraging these capabilities, businesses can effectively monitor their Azure SQL Server environments, ensuring optimal performance and fortified security. Embracing these strategies not only strengthens your Azure subscription management but also positions your organization to thrive in a data-driven landscape.
