Product articles
SQL Monitor
SQL Server Security Monitoring
Spotting Unauthorized Configuration…

30 September 2018


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.

30 September 2018


Spotting Unauthorized Configuration Settings in SQL Server

If someone makes unauthorized changes to SQL Server configuration settings, it could compromise the availability, performance or security of your servers. Using using Extended Events, and a custom metric, in SQL Monitor, Phil Factor offers a way to get an immediate notification of such changes, and investigate their cause.

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.

An attacker of SQL Server likes to be able to change the SQL Server configuration settings. In an ideal world, you will have left everything open for the intruder, but generally, every DBA reduces the surface of attack as much as possible.

Why would the attacker want to change these settings? You might think there’s limited risk in, for example, enabling ‘cross database ownership chaining’, or setting certain databases to ‘Trustworthy’, for a small number of databases, but intruders can exploit some of these settings to enable features that gain access to objects in other databases, or even take control of the whole server. They can use them to ship your data off to a remote server.

If you’ve disabled these, and other sensitive settings, you might think that your SQL Server is well locked-down, but it is surprising many attackers feel that they aren’t really trying hard enough if they don’t manage to escalate their permissions sufficiently to gain ALTER SETTINGS server-level permission, at which point they can alter server and database configuration settings at will. The sysadmin and serveradmin fixed server roles already have this permission, and sometimes more staff than necessary, within the organisation, are members of these roles. You really need to know when a change happens, and who did it.

It may not be the sign of an attack when the configuration changes. More generally, it is disconcerting to discover how often configuration items can get changed: For example, I’ve experienced times when a developer has done a legitimate ad-hoc data extraction via xp_cmdshell and forgotten to lock down its use afterwards.

In this article, I’ll explain how to detect any changes in server or database configuration settings, using Extended Events, and a custom metric, in SQL Monitor. I’ll then refine the technique to report on unauthorized drift in the value of these settings, by storing the officially agreed values in an extended property and comparing them to the current values.

Sensitive Server and Database Configuration Settings

Certain Server configuration options only have a use in development. For a release candidate, these are generally closed off, to reduce the attack surface. Certain of these are particularly useful for an attacker, and so in a corporate setting your security specialist will generally check these settings before signing off a release.

  • Ad Hoc Distributed Queries – if you were to set this on, users can then query data and execute statements on external data sources. It can be used to remotely access and exploit vulnerabilities on remote SQL Server instances.
  • CLR Enabled – setting this on may be essential if you are running CLR but it introduces a risk, from both poorly-secured and malicious assemblies
  • Cross DB Ownership Chaining – this should never be turned on at the server level, but rather enabled for specific databases only, using the SET DB_CHAINING ON. By enabling this, any member of the db_owner role in a database can gain access to objects owned by a login in any other database.
  • Database Mail XPs – finding this enabled is ideal for the attacker because it allows them to exfiltrate data from the database server to a remote host, as an attachment to an email.
  • OLE Automation Procedures – this is used by attackers to execute functions within the server that hosts SQL Server, within the security context of SQL Server.
  • Remote Access – this can be used to launch a Denial-of-Service (DoS) attack on remote servers by off-loading query processing to a target.
  • Remote Admin Connections – this only needs to be enabled for SQL Server failover clusters;
  • Scan For Startup Procs – if this is enabled, SQL Server will automatically run all stored procedures that are configured to execute upon service start up. This allows an attacker a convenient way to maintain control of a server.
  • xp_cmdshell – this setting is one of the most popular for the attacker, because it allows the authenticated SQL Server user to execute any operating-system command shell commands and return results as rows within the SQL client. This would include being able to exfiltrate data out of SQL Server to a remote host

There are two other sensitive, security options, which are not in the group of server configuration settings:

  • Trustworthy – this is a database-level external-access option. It allows CLR assemblies or extended procedures to access objects in other databases, under certain circumstances. A user who is a member of the db_owner database role can exploit this to include themselves in the System Administrators server role, and so take control of the server. See ‘Careful with Trustworthy Setting
  • SA Account Status – this is a login setting. If you don’t need to use this, because Windows Authentication is available, then it should be disabled. An attacker may try to enable it, in order to do a brute force attack.

How to detect server and database configuration changes

SQL Server Audit will track changes to these configuration and security settings, but I need something more lightweight. Since one or two of the database settings can pose security issues, as well as many of the server settings, I decided on a single metric that covered both.

I describe a very simple approach to monitoring changes in the server and database configuration settings, but if you are just concerned with being alerted when a change is being made, it is sufficient. It relies on the fact that if you change any of these settings, an informational system message is sent, at severity level 10, which will tell you when an important configuration change was made along with the ID of the session.

Messages with levels below 10 (currently they are all set to severity 0) are classed as purely informational messages. They are sent to the user whose session made a change, but they aren’t logged as errors. However, changes to database and server configuration settings trigger a Level 10 informational message, sometimes returned to applications as error-level 0, and some of these are logged as errors. This only happens with less than a quarter of these level 10 messages. Curiously, no messages of 11, 12, or 13 are logged as errors and only one of the severity level 14 and 15. Even some severe errors aren’t logged. Basically, your error logs aren’t telling you about everything that is going on, whereas Extended events will, if you want.

So, whenever a user executes sp_configure, to change a server configuration setting, a level 10 message is sent, with error number 15457. Similarly, a message with error number 5084 is triggered every time a user issues ALTER DATABASE…SET…, to change a database configuration setting.

Since these messages do appear in the error log, we can use SQL Server’s alerts. They are also easy to pick up in an Extended Events event session, by capturing the sqlserver.error_reported event. It comes as a relief to find that we only need to filter on two error numbers because Information messages, in general, are emitted from a working system at an alarming rate.

Using Extended Events to capture the two errors of interest is quicker than using the error log and gives you more information. It also allows you to add them to your security panel in SQL Monitor, as a custom metric. If you are quick, you’ll also see them in SQL Monitor’s display of the error log.

Setting up the extended events event session

To get this running, you need to create an Extended Events session. In this case, we can reuse the code from our last custom monitor, which I described when looking for SQL Injection-related errors, in the article ‘How to Detect SQL Injection Attacks using Extended Events and SQL Monitor‘. The same principle can be used here.

We’ll collect all events that deal with server and database configuration changes, but no others.

Listing 1

Once you have this in place, a ring buffer will store a list of configuration changes made since the session has started. We can then access the contents of the ring buffer for reporting, and the custom metric is a single SQL Query.

Detecting the config changes in SQL Monitor

For SQL Monitor, we only need to return an integer number that tells us how many configuration changes have happened in the past five minutes. Listing 2 shows the query to get that number from the ring buffer target of our ConfigurationItemsChanged event session.

Listing 2

Create this as a custom metric, in SQL Monitor, as described in my previous article (referenced above), configure it to collect data on master, and set it running to watch for changes! When errors are detected, the user can drill into the detail by looking in the error log or by using SQL to get more detailed information out of the ring buffer (I’ll show this query shortly).

Making some configuration changes

To check that all is well, we can twiddle some knobs and switches to change configuration settings (but only on a development server please!)

Listing 3

And this code turns them all off:

Listing 4

Viewing the metric and alerts

Here’s an analysis graph, plotting values for the configuration changed metric, while I was testing!

If it spots a change, and you’ve set it to fire an alert, you get this:

So, every time you make a config change, it will appear on the graph and fire an alert.

If you see something like this spike, which is just before the end at 9:45 AM, then you can drill down to see what is happening.

Retrieving the details of the configuration changes

Now we can see what happened and when, using a more detailed query on our collected event data. We get more information than is in the error log because we can specify extra parameters, such as the identity of the client application.

Listing 5

Which, in this case, gives the following:

Checking for unauthorized ‘drift’ in the configuration settings

We’ve done our monitoring the simple way so far, and it satisfies the basic requirement for alerting us about a change and gives us a baseline for how many changes are going on to the server and database configuration settings. The problem is that we get a false alert when the configuration is changed to what it should be, or if it is changed from 1 to 1 or 0 to 0. Also, if we somehow miss a change, we don’t have a lasting account that the configuration is not what the team agreed it should be.

What we really want to know is “Have the configuration settings deviated in an unauthorized, or uncontrolled, way from what went through the deployment pipeline?”. Beware that some database settings change for entirely benign system reasons so it is just useful to be alerted when they happen, so you can check.

All you need to do is just store a JSON document what the server configuration settings should be and compare them to their current values. You can, of course, add a table with the default/agreed settings but I prefer something less intrusive. I store them in a JSON table in an extended property. It won’t store a lot of data, but there is plenty of space in an extended property for this sort of task. Of course, if you are running an old version of SQL Server, then you’ll have to use a table to store this information or use XML instead of JSON.

Drift in server configuration using a ServerConfig extended property

For the server configuration settings, we’ll add an extended property, called ServerConfig, which registers what the server properties should be. This code takes the current settings from the configuration system table (sys.configurations).

Listing 6

Then, at any time, you can check to see the extent of deviation from the signed-off configuration.

Listing 7

To test it out, run Listing 6 to create ServerConfig, then Listing 3 to change some settings, then Listing 7, then finally Listing 4 to return all the settings to what they were.

Drift in a database’s configuration using a DBConfig extended property

Database configuration settings are stored at the database level, and again we grab the registered values from sys.databases, and pop them into JSON table in an extended property, DBConfig, in the same way as server settings. We will need to do it for separately for every database on which we want to check on for changes to configuration.

Listing 8

Let’s now make a dangerous change to the PhilFactor database settings.

Listing 9

Our SQL Monitor custom metric will immediately detect the problem and we’ll see a blip on the analysis graph for the metric, and an alert. We can then investigate the changes by running this SQL Query.

Listing 10

And see that we have a drift from what should be there.

Detecting drift in database settings across all databases

Listing 11 shows how to get a single report for all your databases. I would advise a complete check across all databases, because the ‘Trustworthy’ exploit can work, regardless of which database is set to be trustworthy.

Assuming you simply don’t create the DBConfig extended property on any database you don’t want to monitor, then the query won’t report drift for those databases. Neat, eh? This will report before and after configuration changes for all databases on which you’ve installed DBConfig.

Beware that your database compatibility version needs to be 130 or above, whatever version of SQL Server you are running; the code will run but return a syntax error involving OpenJson error for any databases of lower compatibility level.

Listing 11

And here’s the report:


We now have a solution now that gives us an alert when a server or database configuration changes on the server. When you notice activity, you can then see all the alterations, how it happened, when it happened and who did it. You can then check to see whether it has resulted in the server or database configuration drifting from what it should be.

As with my previous article on spotting intrusion, I’m showing you enough code to inspire your own ideas; Please don’t consider it a complete solution; intrusion detection is a moving target and you will be more than one step ahead of the villains.

Tools in this post

SQL Monitor

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

Find out more

  • –Jeff Moden

    Once again, xp_CmdShell unnecessarily takes it in the socks as a security risk because no one ever explains that the only people that can use it (provided that you haven’t made the fatal mistake of giving non-sys admins the privs to use it directly) are people in the sys_admin role or people who have the “CONTROL SERVER” permission (and you should never grant that directly). Oddly enough, those are the only people that can enable it or disable it, as well. What that means is that if it’s enabled, only people (like DBAs) that are members of the sys_admin role can use it… even if they’re an attacker that got in without sys_admin privs. It also means that even if you have it disabled, an attacker that DOES get in with sys_admin privs can not only enable it, but use it as well.

    The same goes for OPENROWSET. If a bad guy gets in as a member of the sys_admin role, all your lovely precautions about keeping both disabled will only provide a 3ms nearly unperceivable speed bump to the attacker’s software.

    Stop demonizing xp_CmdShell. For DBAs, it’s an incredible tool. It’s also an incredibly tool that users can use through properly written, highly controlled, and very safe stored procedures with them being able to execute xp_CmdShell directly themselves. Learn how to use xp_CmdShell properly, enable it, and start enjoying it’s benefits. At the same time, learn how to lock down your server because having a bad guy get in as a member of the sys_admin role is what your real concern should be. Disabling xp_CmdShell is like putting a thin veil over rotting meat. It’ll only make people feel better but the flies can still get at the meat and it still stinks.


  • Phil Factor

    Jeff, old friend, good to hear from you.

    The list hasn’t come out of my head, but is based on the penetration tests that are used by security people, and on their advice. I agree that almost all these features can be used perfectly safely on a production system without causing a security risk: but how to do that is, I think, a separate article, or articles. (I’m sure Tony would love you to write one). I can think of several examples where I’ve used OPENROWSET and xp_cmdshell in production settings, but with the necessary precautions.

    This article is purely approaching it from the angle of a DBA who is tasked with checking that the various settings that have been chosen are kept, and are those that are in the configuration management system. I list the sensitive ‘surface area management’ settings just to give an indication as to why it is important to do these checks. How the server and database settings are chosen for any particular database application is a matter of the IT team to decide, not the pundit.

    • –Jeff Moden

      There are no necessary precautions to be had unless you do the obvious stupid thing of giving non-DBAs privs to execute XP_CmdShell directly. If OLE Automation and xp_CmdShell are enabled, only those who are members of the sysadmin role or control server role (who on this good green Earth would do the latter???) can use it. Ironically, they’re the only ones that can enable them. Even with them enabled, an intruder can’t do squat with them unless they get in as someone with sysadmin privs. If they get in with such privs, they can enable and use either or both.
      As for the auditors, they need to learn something other than pre-SQL Server 2005 and security in general. Of all the data breaches you’ve heard about, how many have been attributed to xp_cmdShell or OLE Automation? None… at least I’ve not heard of any. Have you? They’ve all gotten in with elevated privs either by SQL Injection, having an insider, or plain ol’ wire sniffing and using a box of GPUs to hack the passwords (saw a demo of that last one… 64 GPUs running so fast that one actually did catch on fire but it only took them 10 minutes to hack a 12 byte password).
      My recommendation is that if you’re really concerned about xp_CmdShell and OLE Automation, then you SHOULD enable both and let it scare you bad enough to properly lock down your server instead of thinking you’re safe by putting a thin veil over a table full of rotting meat. 😉

      • Phil Factor

        Here is a well-articulated example of the sort of attack that can land the attacker with SysAdmin privileges.
        I’m told by security experts that the escalation of privileges isn’t that hard if there is an error in setting up SQL Server. There are a number of penetration tests that demonstrate this, and which should be in every DBAs toolkit. However, in this series of articles, the debate about how they get the Sysadmin privileges is off-topic. The point of monitoring is, in my view, to gain a sense of what is going on with a production system. There are characteristic errors that you will see in any SQL Injection attack, and a range of other symptoms. An attack is rarely ‘quiet’, even if the attacker has SysAdmin rights. The fewer the rights and the more difficult the interface, the ‘noisier’ the attack. I’m keen on working towards understanding what that ‘noise’ consists of and how to measure it.

You may also like

  • Article

    The State of SQL Server Monitoring 2018

    Over 600 technology professionals who work in organizations that use SQL Server recently responded to our survey to discover the current state of SQL Server monitoring. We asked people across a range of sectors, in organizations of every size around the globe, about how they monitor SQL Server, the technologies they work with, and what

  • Article

    How to Detect SQL Injection Attacks using Extended Events and SQL Monitor

    Phil Factor shows how to monitor for the errors indicative of a possible SQL Injection attack on one of your SQL Server databases, using a SQL Monitor custom metric that uses diagnostic data from Extended Events.

  • Webinar

    SQL Monitor Overview

    SQL Server Monitoring Specialist James King takes you from getting a simple overview of your entire SQL Server estate to alerting and diagnosis of SQL Server problems.

  • Article

    The importance of monitoring your SQL Servers

    Without a monitoring system in place, DBAs can find they never have the time to implement new processes because they’re bogged down with too many daily checks, and other painstaking investigatory tasks. On top of this, they’re continually firefighting problems that are surfacing instead of being able to spend time finding and fixing the root

  • Forums

    SQL Monitor Forum

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