Francesco Cogno's blog

Where data hit programming languages. Hard. In the shin.

SQL Server intrusion detection using Azure Event Hub, Azure Stream Analytics and PowerBI

Intro

A couple of days ago I was asked this question:

As a DBA, what can Stream Analytics do for my on-premise SQL Server?

The answer is easy to come by: to handle the ERRORLOG. SQL Server has its own streaming log: it’s called ERRORLOG (yes, the capitalization is intended) and gives us a cartload of information. DBAs look for it when asked questions like: “what the heck happened? :smile:“. Since SQL Server 2005 it’s also the standard output of the login auditing feature https://technet.microsoft.com/en-us/library/ms175850.aspx. In a nutshell, SQL Server logs entries like

2015-05-13 11:15:57.25 Logon Login failed for user 'crack'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

The problem is, in order to detect malicious attacks we should be able to tolerate scattered failures. We don’t want to raise an alarm if a users mistypes a password once in a month. In other word, we need to detect failed login attempts in a custom timespan. If we detect, for example, 40 failed logins from a specific IP within 5 minutes we should definitely raise an alarm. Another example is to detect whenever there are 5 failed logins for the same account in 1 minute. For the purpose of this post we will implement the latter example; I’ll leave up to you to modify it in order to detect IP-based attacks.

What we need to do

We need to:

  1. Scan the ERRORLOG file and extract login entries.
  2. Store the entries in a persistent, scalable event store.
  3. Group those entries using a tumbling window of 1 minute. Pick only the failed logins and group them by login name.
  4. Identify the entries whose count is greater than 5.
  5. Create a dynamic dashboard to show the alert.

Azure to the rescue

Luckily most tasks are addressed by a specific Azure feature. We will use, respectively:

  1. Some custom C# code (will show in a bit).
  2. Azure Event Hub (http://azure.microsoft.com/en-us/services/event-hubs/).
  3. Azure Stream Analytics (http://azure.microsoft.com/en-us/services/stream-analytics/).
  4. Azure Stream Analytics.
  5. PowerBI (https://powerbi.microsoft.com/).

So what we’ll end up having is:

Let’s start creating the event hub collector.

Event hub

Create the event hub using the New… button in the Azure portal:

Make sure to create a consumer group for your event hub:

Create the event hub policy (it will allow our client to send data):

The dashboard should now contain the connection string. Copy it for later.

C# code

I will not dwell long here on the ERRORLOG scanner code. You can get it from github here https://github.com/MindFlavor/StreamFromErrorlog. In order for it to work on your environment make sure to configure the App.config file.

I will only highlight how to connect to Event Hub.

First you need an instance of EventHubClient class:

_eventHub = EventHubClient.CreateFromConnectionString(connectionString, eventHubName);

Here the connectionString is the one you got from the Azure portal before. The eventHubName is, well, the event hub name :smirk: .

Now to send an event you just have to call the Send method. I’ve picked JSON as message format:

string ser = JsonConvert.SerializeObject(le);

EventData ed = new EventData(Encoding.UTF8.GetBytes(ser))
{
    PartitionKey = le.Client
};

Very easy. In case you’re wondering, the PartitionKey is just a way to spread your messages in different partitions. The Azure framework will dynamically change the partitioning function based of the number of available partitions.

Stream Analytics

Now get back to the portal and create a new Stream Analytic job:

Input

Now add your event hub input:

You can download some sample data if you want to check it. Make sure to grab some as it will help during the query design phase:

Query

Remember we wanted to find the failed logins attempt on the same username in a 5 minute timespan. We just have to use the Tumbling Window function like this:

SELECT
    System.Timestamp as WindowEnd,
    login, COUNT(*)
INTO
    [elpowerbi]
FROM
    [errorloginput]
TIMESTAMP BY 
    EventTime
WHERE
    [failed] = 1
GROUP BY TUMBLINGWINDOW(mi, 5), login
HAVING COUNT(*) > 3

Make sure to specify in the FROM clause the input name you’ve chosen in the previous step. The INTO clause must match the output name (we haven’t created it yet).

If you click Test and supply the sample data you can make sure the query works as expected:

As you can see, in my sample data there are 4 failed attempt to authenticate as username crack.

Output

Add now an output and pick PowerBI as destination. Remember to name it as the INTO clause of your query. The dataset name will be the dataset created in PowerBI. Be warned: if a dataset with the same name exists in your PowerBI subscription your existing data will be lost.

When done start your stream analytics job. If everything goes as expected you will see the status of the job going to running (or idle when the data has been processed):

PowerBI

The last task is to create the dashboard in PowerBI. You will find there the newly created dataset (note: if there’s no data there will be no dataset :smile:):

Click on it and create some graphical representation of your liking. In my case I’ve created this dashboard:

Here I can clearly see that I had three different account failing to authenticate: crack, kkk and NT AUTHORITY\SYSTEM. This is just what we wanted. But! More magic! The PowerBI dashboard is completely automatic! In few moments the graphs updated themselves into this:

I can see in a glance that:

  • The kkk failed logins stopped happening. Since it happened just a few times I can infer it was a simply human error.
  • The crack failed logins keep happening at the same rate. This smells as machine generated and could be a brute force attack indeed :thumbsup:!.

Conclusion

This is just a sample but you can grasp the power of this solution. With just a simple query you can extract streaming information and visualize it with a dynamic dashboard. You can collect all your SQL Servers logs in the event hub and create an enterprise wide dashboard (with drill down, too). The events will shape themselves and animate your dashboard without having to write a single line of code.

Azure magic!

Happy coding,
Francesco Cogno