Azure Log Analytics provides several features to keep a SQL Database secure. It helps identify anomalous or even malicious behavior and lets you create alerts. This article presents a step-by-step guide to implement and leverage this new functionality.

Properly monitoring your system is the foundation of its perceived and actual reliability. With the feature to send database auditing logs to Azure Log Analytics, it’s easier than ever to create reliable monitoring and alerting for databases. But how do we set up monitoring and alerting after collecting all our security data? What data is important to monitor and how can you conduct useful and efficient monitoring? This article aims to answer these questions, so let’s dive in!

All templates used in this article can be found in my GitHub repo.

Auditing in the Azure portal – a quick recap

Before starting to set up our monitoring and alerting, let’s quickly revisit some database auditing essentials described in the previous blog post.

SQL Databases generate events each time a query is executed, or when a principal tries to login. These events - called auditing logs - can and should be collected as a security effort. They help database admins to monitor their systems, create baselines for normal activity, and signal suspicious or even malicious events.

You can turn on auditing in the Azure portal ‘auditing’ blade or configure it in your Azure Resource Manager templates. The latter is my preferred way of working. It helps to consistently provision secure databases.

Audit action groups

The Azure portal gives you a hint of the events that are being logged by turning on auditing. Just hover your mouse over the ‘I’ symbol on the ‘auditing’ blade. The message shows that events from the following audit action groups are included:

1. BATCH_COMPLETED_GROUP

2. SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP

3. FAILED_DATABASE_AUTHENTICATION_GROUP

In addition, other interesting audit action groups exist. An interesting audit action group that we’ll be implementing later is ‘USER_CHANGE_PASSWORD_GROUP’. By enabling this action group, we’ll receive an event each time a user resets a password.

The ‘BATCH_COMPLETED_GROUP’ group logs all completed T-SQL statements and is a default setting. This results in having a complete and detailed auditing log but also generates high volumes of data that can drown out the import events. Therefore, we need a way to cut through the noise and show us the events that truly matter.

Cutting through the noise with Azure Log Analytics

With auditing enabled and configured, database events like authentications or query completion will be forwarded asynchronously to our Log Analytics workspace. These events are collected in a table called ‘AzureDiagnostics’ and can be queried using the Kusto query language.

The Kusto language lets you search for specific events, summarize results and can even generate visualizations of the data. It is therefore a very important tool in finding and selecting auditing events we need to act upon.

The visualization of trends in our data helps us establish a baseline of ‘normal’ or ‘safe’ database activities. For example: the number of daily password rotations or the number of failed database authentications.

Once we get a feeling for our baseline activity, we can start creating specific searches. For instance, statements that grant or remove permissions issued by a non-privileged principal. Or an irregular number of password changes. Or DML statements issued by a non-privileged principal on a production environment.

Once you’ve determined which occurrences are irregular or even harmful, you can use Log Analytics to fire alerts to notify an operator.

Putting it all into practice

Setting up the environment and auditing events

Let’s start with setting up a database environment we can play with. To keep this simple, we’ll focus on the database auditing settings, placing the server auditing settings out of scope for this article.

We’ll start with an ARM template based on my previous blog post. This will deploy a SQL Server, SQL Database and Log Analytics workspace for us. Along with these resources, the template also deploys ‘auditingSettings’ and ‘diagnosticSettings’ resources that forward our auditing logs to our Log Analytics workspace. Notice that the ‘USER_CHANGE_PASSWORD_GROUP’ action group has been added, and the default ‘BATCH_COMPLETED_GROUP’ is left out. This will limit the number of generated events to login attempts and password changes.

Let’s execute T-SQL statements against the database to create auditing events. The test statements include ‘normal’ or ‘safe’ operations like creation of users and password rotations by the admin account. A second session with the database is opened with a different user. In this session a situation is simulated where a user tries to change passwords of other users. This will cause the ‘RESET PASSWORD’ event to fire and pop up in our Log Analytics workspace.

Creating trend analysis

In Log Analytics, we can visualize the ‘RESET PASSWORD’ event over time using the Kusto query language. This helps to spot irregular activity easily.

Some useful Kusto language elements to help us with our analysis are ‘bin()’ and ‘render’. The bin() function helps to display our count of events in time buckets. And the ‘render’ operator helps visualize our data into an easy-to-interpret chart.

As you can see in the screenshot below, there’s some concerning activity occurring at 1:10 PM (UTC). This might be worth investigating. A user other than the admin user is causing the ‘RESET PASSWORD’ event to fire.

Creating specific searches

After discovering trend deviations using data visualization, our next step is to delve for more details. We can use the insights we’ve just gained from looking at the trend to create a new query. We’ll filter the data based on ‘action_name_s’, ‘server_principal_name_s’, ‘statement_s’ and the ‘TimeGenerated’ attributes.

A useful Kusto language element to zoom in on a specific time interval is the ‘between()’ operator. It accepts a datetime range, which we can base on our findings from the trend analysis. In this case we’ll limit our results by using ‘TimeGenerated between(datetime("2020-03-22 13:10") .. datetime("2020-03-22 13:15"))’. Our result set shows that a SQL user named ‘TEST_USER_02’ has issued many ‘ALTER USER’ statements to change passwords.

Our results also give us the host name and IP address of the machine used to issue these statements. These will prove to be very useful in further investigating this issue.

Creating an alert

Events like unwarranted password changes should be signaled as soon as they occur. We need a more robust and faster mechanism than performing periodic trend analysis. For this, we can use the Azure Monitor alerts. These alerts can be based on a Log Analytics query.

To create an alert, we’ll adjust our last query by removing hardcoded values from the time filter, replacing them with dynamic content. In this case we’ll use ‘| where TimeGenerated > ago(1h)’. This will return all password changes issued by non-privileged account from the passed hour.

Click the button ‘New alert rule’ above the query syntax. And in the next blade, click on the hyperlink text in the ‘Condition box’. Make sure to configure your alert that you check every 60 minutes if an event occurred in the last 60 minutes. Set the threshold value to 0.

If you already have an Action Group, you can use this to alert an operator. But if you do not already have one, you can create a new one. Fill in a name for the action group, a short name, the resource group into which you deploy the action group and select an action type. In my case, I’ll send myself an email. Click ‘ok’ when you are done.

Fill in an ‘Alert rule name’, indicate its severity and make sure to activate it upon creation. Click ‘Create alert rule’ once you are done.

Automating it

Once your Kusto queries and alerts are defined, you can automate their creation so you can provision them across your database landscape. In order to automate their creation, we’ll need to include two new resources into our template:

• Microsoft.Insights/actionGroups
• Microsoft.Insights/scheduledqueryrules

The ‘actionGroups’ resource will deploy our ‘db-ops’ action group with an instruction to email an operator.

The ‘scheduledqueryrules’ resource includes our newly created Kusto query, schedule and threshold value. The resource also includes a reference to our action group – causing the alert to be sent by email if the threshold value has been reached.

You can find a finished ARM template example here.

Rounding up

Azure Log Analytics makes databases safer by offering functionality to do trend and in depth-analysis on our auditing data. First define your baseline and after that define suspicious and malicious activities. Next, the auditing queries can easily be transformed into alerts. This makes sure that operators are notified once unsafe events start to occur so they can take necessary actions. The entire deployment process can be automated, making sure you provision safe and auditable databases across the board.