• Redgate Monitor
• Security and compliance

Spotting Unauthorized Configuration Settings in SQL Server

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:

Conclusion

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

Redgate Monitor

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

Find out more