22 December 2018
22 December 2018

On Quickly Investigating a SQL Monitor Custom Security Alert

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

I recently wrote a series of article that described four SQL Monitor custom metrics, designed purely to monitor for the signs of intrusion, or other unauthorized database changes. These metrics allow you to implement separate strategies for:

These four metrics, combined, will provide evidence of an attack. It would also be possible to check for suspicious clauses in the SQL that has been executed, but that would take rather more CPU resources.

This article pulls together everything we’ve learned so far to provide a way of reporting on an intrusion. It will provide a ‘narrative’ description of all the changes that have been detected, allowing the DBA to investigate fully the sequence of unusual events. If you want to try it out, you’ll need to have created all the Extended Events sessions, and associated custom metrics, as described in each of the above four articles.

Why the need to be ready?

Imagine that SQL Monitor starts reporting some alarming custom security alerts.


How do you find out quickly what’s happened? Given the nature of the alerts, you may be facing an intrusion and a possible leakage of data, so you need to do things quickly, and under stress.

You have the alerts from SQL Monitor, but this will only tell you that suspicious things have happened. You now need to drill into the detail to find out what happened, and the order in which it happened. To do this, you need to look in different places, which will bring different data to the party. The default trace and Extended Events have rather different information. The logs will sometimes add information and so will SQL Audit, if you have that running. There will be application-based metrics too to add to the mix. You can often get information from cache, especially the queries. There will be other forensics, which vary between releases such as those in the Logs (SQL Server Forensics is a science that an average geek like me just marvels at, but at a distance).

With these SQL Monitor custom metrics in place, you can query the Extended Event and default trace data underling each one, as I demonstrated in the previous articles. What you really want though, when you are doing your quick, preliminary investigations, is not the deep details of each one, but the narrative of the changes made, in chronological sequence. It is just like the movies where the young geek, surrounded by stern-faced executives, goes clickety-clack on the keyboard, and immediately the truth is revealed: the scrolling screen flickers, lighting up the grim horror on their faces.

The strategy

Here, we’ll take the Extended events metrics we created and the one that we based on the default trace, and blend them together, just using one of the many columns returned, called action, which a string that tries to recount, in plain English, what the event or trace actually meant. Since the values are simple strings, it is easy to UNION them together, and then sort by the date column that provides the local time.

The procedures from which we get the data are slightly more tortuous than one might expect because I wanted each source to be an inline table function. This makes it much easier to UNION together the results and order then by the time and date in which they happened. This meant extracting the routines that fetched the ring-buffer information into variables at the start of the batch.

The functions

We start by creating the functions. A quick glance will tell you why we want to hide all that stuff in a function; it isn’t a pretty sight.