Product articles SQL Monitor SQL Server Security Monitoring
On Quickly Investigating a SQL Monitor…

On Quickly Investigating a SQL Monitor Custom Security Alert

Phil Factor offers a clever way to report on a SQL Server intrusion, with a query that shows a full narrative description of all the security-related changes that have been detected by a set of SQL Monitor custom metrics.

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.

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:

PhilFactor had error “Invalid column name ‘silly’.” (207) with query “select * from Sales.Customer where (customerId = silly)” using the “node-mssql” application


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…

PhilFactor had error “Incorrect syntax near ‘;’.” (102) with query “select * from Sales.Customer where (customerId = 4;select silly)” using the “node-mssql” application


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.

PhilFactor had error “Incorrect syntax near ‘)’.” (102) with query “select * from Sales.Customer where (customerId = 4);select silly)” using the “node-mssql” application


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

‘PhilFactor had error “Invalid column name ‘silly’.” (207) with query “select * from Sales.Customer where (customerId = 4);select silly –)” using the “node-mssql”


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.

User PhilFactor added MSSecurityMtr to AdventureWorks2016 using the “node-mssql” application


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

User PhilFactor added MSSecurityMtr to object sysadmin using the “node-mssql” application.


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…

PhilFactor changed Configuration option ‘show advanced options’ from 1 to 1. (15457) with query “select * from Sales.Customer where (customerId = 1 and 1=2); use AdventureWorks2016 execute sp_configure ‘show advanced options’,1; reconfigure with override; execute sp_configure ‘Ad Hoc Distributed Queries’,1; reconfigure with override; –)” using the “node-mssql” application


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!

PhilFactor changed Configuration option ‘Ad Hoc Distributed Queries’ from 0 to 1. (15457)

Now he is opening up xp_cmdShell!

PhilFactor changed Configuration option ‘show advanced options’ from 1 to 1. (15457) with query “select * from Sales.Customer where (customerId = 1 and 1=2); use AdventureWorks2016 execute sp_configure ‘show advanced options’,1; reconfigure with override; execute sp_configure ‘xp_cmdshell’, 1; reconfigure with override; –)”


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.

PhilFactor had error “Incorrect syntax near ‘)’.” (102) with query “select * from Sales.Customer where (customerId = 1 and 1=2); create table #directoryListing )” using the “node-mssql” application.


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.

PhilFactor had error “Invalid object name ‘#directoryListing’.” (208) with query “select * from Sales.Customer where (customerId = 1 and 1=2) union all SELECT NULL,NULL,NULL,NULL,TheLine,NULL,NULL FROM #directoryListing–)” using the “node-mssql” application


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

User PhilFactor Created User Table AdventureWorks2016.MS_Temp267 using the “node-mssql” application


And the next line …

User PhilFactor Deleted User Table AdventureWorks2016.MS_Temp267 using the “node-mssql” application


…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.

PhilFactor changed Configuration option ‘show advanced options’ from 1 to 1. (15457) with query “select * from Sales.Customer where (customerId = 1 and 1=2); use AdventureWorks2016 execute sp_configure ‘show advanced options’,1; reconfigure with override; Execute xp_cmdshell ‘del D:\DataBackups\*.* /Q’ Execute xp_cmdshell ‘rmdir D:\DataBackups’ execute sp_configure ‘Ad Hoc Distributed Queries’,0; execute sp_configure ‘xp_cmdshell’, 0; reconfigure with override; –)” using the “node-mssql” application.


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.


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!