• Redgate Monitor
• Security and compliance

On Quickly Investigating a SQL Monitor Custom Security Alert

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:

• Detecting SQL Injection attacks – the metric uses Extended Events to capture errors related to incorrect SQL syntax, attempts to access an invalid object, and so on
• Spotting unauthorized SQL Server configuration changes –using an Extended Event session to detect the informational error massages triggered by changes to security-sensitive configuration settings such as xp_cmdshell, Ad Hoc Distributed Queries and so on.
• Checking for database drift – using Extended Events to detect metadata changes, meaning someone added, altered, or dropped an object (other than in tempdb).
• Monitoring for changes in permissions, users, logins and roles – this custom metric mines details of these changes from the default trace.

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.

The batch

With all these in place, all we need to do is to decide on the time period we need to investigate and see what has been going on that might be suspicious. First, we pick up the ring buffers and then we execute the following code.

This produces a narrative of events that one wouldn’t hope to see in a production server. In our case, it looks as if someone has been up to no good.

The narrative, line by line

I generated all these changes using the NodeJS interface, and PowerShell ‘attack’ script, described in this article. Here, I’ll simply walk through the narrative of the changes, as reported above, and explain briefly what they mean.

That first line says:

 

That node application is running under my user ID. Via that interface I’ve simply submitted a value that isn’t an integer. It’s been accepted, SQL Server has tried to procedure it, and it’s failed with a 207 error, which was detected by our Suspicious Errors custom monitor. That is a vulnerability.

That next line…

 

The user has injected some SQL. OK, he or she didn’t do anything effective but didn’t have to because they can see the error and will know it successfully injected SQL. It won’t execute anything because there is a bracket in the filter condition for the first SQL Statement.

In the next line, we see that the attacker has added the bracket but has forgotten to comment out the original bracket, after the part of the code where he injected his attack code.

 

In the next line, the attacker comments-out that terminating bracket, and SQL Server now tries to execute the second query.

 

Eek. The attacker now has a way in, and the damage done will depend on how easily he can broaden the scope of the attack. Cast your eye down further down the narrative and it looks bad.

The next line, for example, shows that he got in and added a new principal.

 

Oh, my giddy aunt. Now on the next line …

 

He now has sysadmin access using a SQL Server login. Our custom monitor for changes to logins, roles users and permissions has detected it.

Oo-er. Now things get even scarier…

 

He’s enabled Ad Hoc Distributed Queries configuration setting. He is looking for ways of getting a large payload off the server! On the next line, he achieves this!

Now he is opening up xp_cmdShell!

 

He is now going to use xp_cmdShell for something. FTP? Our custom monitor for SQL Server configuration changes picks up this suspicious activity.

Next, the attacker is trying to create a temporary table. Why? The name #directorylisting is a clue. He’s probably trying to store and then retrieve all sorts of useful information you can get from executing OS commands and procedures.

 

That was a silly mistake. He didn’t need that error. He forgot the comment. He’s trying to use a global temporary table, but it isn’t working for him. Probably he is the only connection at this point and the connection is getting closed in the connection pool.

 

The next line tells us that he’s now using an ordinary table to do the job.

 

And the next line …

 

…proves that it was successful. Heaven only knows what information is in it, but it will be in the log. Our Database Drift custom metric alerted us to these changes, at least.

Now it looks as if our attacker has got everything he needs and is just tidying up.

 

Yes, he has injected a script that has gone ahead and tried to remove the traces of the attack. Luckily, we have the Extended Events session data to tell us.

So, it all happened very quickly. In this case, it was a scripted attack, which you can emulate using the NodeJs interface and PowerShell script described here. Real probing attacks can go on for hours, and you can see the early stages of a rash of syntax errors that are inevitable in the early stages of any attack.

Once you see the escalation to sysadmin privileges and those configuration changes, you know that the attacker has full control. You must now assume that the attacker has all your tables dumped out and dispatched to an FTP site somewhere in eastern Europe. In this case, every table from Adventureworks was first saved to the filesystem and then dispatched to a remote FTP site as soon as the attacker had opened up xp_cmdShell.

Conclusion

For anyone of a nervous or sensitive disposition, I must emphasize that this is a simulation, and in another article, we’ll go through the PowerShell script and application scenario that did this. You will see that the attacker very soon had access to what could, in reality, have been sensitive private information, and could have gone on to inflict a lot more damage. Even so, the entire AdventureWorks data was leaked.

The script we introduced in this article will give you clues to what has gone on, but it is only a pointer to what you need to drill down to. Its purpose is just to alert you to suspicious activity. It won’t give you the full narrative. You will need to know quickly what cached plans are there that will reveal the queries that were executed. That is another story!

Tools in this post

Redgate Monitor

Real-time SQL Server and PostgreSQL performance monitoring, with alerts and diagnostics

Find out more

SQL Toolbelt

The industry-standard tools for SQL Server development and deployment.

Find out more