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

30 September 2018

4 Comments

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

4 Comments

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.

IF EXISTS --if the session already exists, then delete it. We are assuming you've changed something
    (
    SELECT * FROM sys.server_event_sessions
      WHERE server_event_sessions.name = 'ConfigurationItemsChanged'
    )
    DROP EVENT SESSION ConfigurationItemsChanged ON SERVER;
  GO
  CREATE EVENT SESSION ConfigurationItemsChanged -- the name of the session 
  ON SERVER
    ADD EVENT sqlserver.error_reported --just the one event
      (ACTION
         (
         sqlserver.client_app_name, sqlserver.client_connection_id,
         sqlserver.database_name, sqlserver.nt_username, sqlserver.sql_text,
         sqlserver.username --all these are useful for tracking an error
         )
    WHERE (([error_number]=(15457)) OR ([error_number]=(5084)))
      )
    ADD TARGET package0.ring_buffer --we will rwrite it to a ring buffer targwet only
      (SET max_memory = (4096))
  WITH
    (
    MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = on
    );
  GO
  ALTER EVENT SESSION ConfigurationItemsChanged ON SERVER STATE = START;
  GO

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.

SELECT Count(*) AS ErrorCount
    FROM sys.dm_xe_session_targets AS xet
      INNER JOIN sys.dm_xe_sessions AS xes
        ON xes.address = xet.event_session_address
      CROSS APPLY
      (SELECT Cast(xet.target_data AS XML)) AS target_data_xml(xml)
      CROSS APPLY target_data_xml.xml.nodes('//RingBufferTarget/event') AS xed(event_data)
    WHERE xes.name = 'ConfigurationItemsChanged'
      AND xet.target_name = 'ring_buffer'
      AND DateDiff
           (MINUTE, 
           Convert(DATETIME2,
              SwitchOffset(
              Convert(DATETIMEOFFSET, xed.event_data.value('(@timestamp)[1]', 'datetime2')),
              DateName(TzOffset, SysDateTimeOffset())
                          )
              ), 
           GetDate()) < 5;

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!)

/* this code turns ON some of the configuration items with security issues */
   EXEC sp_configure 'show advanced options',1
   reconfigure
  GO
  EXEC sp_configure 'CLR Enabled',1
  EXEC sp_configure 'Ad Hoc Distributed Queries',1 
  EXEC sp_configure 'Cross DB Ownership Chaining',1
  EXEC sp_configure 'Database Mail XPs',1
  EXEC sp_configure 'Ole Automation Procedures',1 
  EXEC sp_configure 'Remote ACCESS',1 
  EXEC sp_configure 'Remote Admin Connections',1 
  EXEC sp_configure 'Scan for Startup Procs',1 
  RECONFIGURE -- To update the currently configured value for advanced options.
  GO

Listing 3

And this code turns them all off:

/* this code turns OFF some of the configuration items with security issues */
  EXEC sp_configure 'show advanced options',1
   reconfigure
  GO
  EXEC sp_configure 'Ad Hoc Distributed Queries',0 
  EXEC sp_configure 'CLR ENABLED',0
  EXEC sp_configure 'Cross DB Ownership Chaining',0
  EXEC sp_configure 'Database Mail XPs',0
  EXEC sp_configure 'Ole Automation Procedures',0 
  EXEC sp_configure 'Remote ACCESS',0
  EXEC sp_configure 'Remote Admin Connections',0 
  EXEC sp_configure 'Scan for Startup Procs',0
  EXEC sp_configure 'show advanced options',0
  RECONFIGURE-- To update the currently configured value for advanced options.
  GO

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.

/* now see what we have caught */
  DECLARE @Target_Data XML =
            (
            SELECT TOP 1 Cast(xet.target_data AS XML) AS targetdata
              FROM sys.dm_xe_session_targets AS xet
                INNER JOIN sys.dm_xe_sessions AS xes
                  ON xes.address = xet.event_session_address
              WHERE xes.name = 'ConfigurationItemsChanged'
                AND xet.target_name = 'ring_buffer'
            );
  SELECT 
  CONVERT(datetime2,
          SwitchOffset(CONVERT(datetimeoffset,xed.event_data.value('(@timestamp)[1]', 'datetime2')),
          DateName(TzOffset, SYSDATETIMEOFFSET()))) AS datetime_local,
  xed.event_data.value('(data[@name="error_number"]/value)[1]', 'int') AS [Error_Number],
  xed.event_data.value('(data[@name="severity"]/value)[1]', 'int') AS Severity,
  xed.event_data.value('(data[@name="message"]/value)[1]', 'varchar(255)') AS [Message],
  xed.event_data.value('(action[@name="username"]/value)[1]', 'varchar(255)') AS UserName,
  xed.event_data.value('(action[@name="nt_username"]/value)[1]', 'varchar(255)') AS NT_Username,
  xed.event_data.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQL_Text,
  xed.event_data.value('(action[@name="database_name"]/value)[1]', 'varchar(255)') AS [Database_Name],
  xed.event_data.value('(action[@name="client_connection_id"]/value)[1]', 'varchar(255)') AS client_conn,
  xed.event_data.value('(action[@name="client_app_name"]/value)[1]', 'varchar(255)') AS client_app_name
  FROM @Target_Data.nodes('//RingBufferTarget/event') AS xed (event_data)

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

USE master
  IF NOT EXISTS --if the record of the settings doesn't exist ....
   (SELECT * FROM ::fn_listextendedproperty
      (N'ServerConfig', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
   )
   BEGIN --if no record
   -- all we can do is insert initial configuration data
   -- so we create a JSON document that includes the date and time of insertion.
   DECLARE @ThisConfig SQL_VARIANT =
    (SELECT Convert(VARCHAR(7500),
      (SELECT Json_Query(g.r)AS r FROM
       (SELECT Convert(VARCHAR(7500),(SELECT f.date, f.configuration
         FROM
          (VALUES
             (GetDate(), -- put the current date and time in there
      --and the current configuration settings as a json array
             (SELECT cfg.configuration_id AS id, cfg.value AS v
                FROM sys.configurations cfg FOR JSON AUTO)--and the array
             )
          )f(date,configuration) FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER))
       )g(r) FOR JSON AUTO
     ))
   )--now write it into the extended property
   EXEC sp_addextendedproperty @name = N'ServerConfig',@value = @ThisConfig
  END

Listing 6

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

USE master
  SELECT  id, Convert(BIGINT,old.value) AS CorrectValue, 
          Convert(BIGINT,live.value) AS CurrentValue, 
          live.name, 
          live.description+ CASE WHEN is_advanced=1 THEN ' (advanced)' ELSE '' end  AS description
        FROM OpenJson((SELECT TOP 1 Convert(VARCHAR(MAX),value)
        FROM ::fn_listextendedproperty 
           ('ServerConfig', DEFAult, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT))) AS f --base array
        OUTER APPLY OpenJson(f.Value)r --value of the R variable
        OUTER APPLY OpenJson(r.Value)s --separate objects within r variable
        OUTER APPLY OpenJson(s.Value ) WITH (id INT '$.id',value INT '$.v') old
        INNER JOIN sys.configurations live
          ON old.id=live.configuration_id
        WHERE old.value<>live.value
          AND s.[Key]='configuration' -- there is also the date object.
          AND f.[Key] =0

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.

USE PhilFactor
  IF NOT EXISTS --if the record of the settings doesn't exist ....
    (SELECT * FROM ::fn_listextendedproperty 
       (N'DBConfig', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
    )
    BEGIN --if no record
    -- all we can do is insert initial configuration data
    -- so we create a JSON document that includes the date and time of insertion.
    DECLARE @ThisConfig VARCHAR(7500) =
      (SELECT  json_query((SELECT * FROM sys.databases WHERE name LIKE Db_Name() FOR JSON AUTO)))
    --now write it into the extended property
    EXEC sp_addextendedproperty @name = N'DBConfig',@value = @ThisConfig 
    END

Listing 8

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

ALTER DATABASE philFactor
  SET trustworthy on;  
  GO

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.

SELECT [then].[key],[then].[value] AS Should_Be,[now].[value]AS [is] FROM 
  OpenJson((SELECT Json_Query(Convert(VARCHAR(max),
                 ( SELECT value FROM::fn_listextendedproperty(
                 N'DBConfig', 
                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT))
                              ))
            ))OriginalArray
     OUTER APPLY OpenJson(OriginalArray.Value)[then]
  LEFT OUTER JOIN 
    OpenJson ((SELECT * FROM sys.databases 
                 WHERE name LIKE Db_Name() FOR JSON AUTO))CurrentArray
     OUTER APPLY OpenJson(CurrentArray.Value)[now]
  ON  [now].[Key]=[then].[Key]  
  WHERE [then].[value]<>[now].[value]

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.

DECLARE @DriftedSettings TABLE 
    ([KEY] sysname, ShouldBe VARCHAR(400), [Is] VARCHAR(400), [Database] sysname);
  INSERT INTO @DriftedSettings ([KEY], ShouldBe, [Is], [Database])
  EXEC sys.sp_MSforeachdb 'USE ? 
  SELECT [then].[key],[then].[value] AS Should_Be,[now].[value]AS [is] ,
  Db_Name() AS [Database]
  FROM 
  OpenJson((SELECT Json_Query(Convert(VARCHAR(max),
                 ( SELECT value FROM::fn_listextendedproperty(
             N''DBConfig'', 
                 DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT))
                              ))
            ))OriginalArray
     OUTER APPLY OpenJson(OriginalArray.Value)[then]
  LEFT OUTER JOIN 
    OpenJson ((SELECT * FROM sys.databases 
                 WHERE name LIKE Db_Name() FOR JSON AUTO))CurrentArray
     OUTER APPLY OpenJson(CurrentArray.Value)[now]
  ON  [now].[Key]=[then].[Key]  
  WHERE [then].[value]<>[now].[value]
  ';
  SELECT drift.[KEY], drift.ShouldBe, drift.[Is], drift.[Database]
    FROM @DriftedSettings AS drift;

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

SQL Monitor

SQL Monitor is a SQL server monitoring tool that transforms the way you look at your database. It cuts your daily check to minutes, with a web-based overview of all your SQL Servers.

Find out more

Share this post.

  • –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

        Jeff,
        Here is a well-articulated example of the sort of attack that can land the attacker with SysAdmin privileges.
        https://blog.netspi.com/get-sql-server-sysadmin-privileges-local-admin-powerupsql/
        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 value of monitoring at BMW

    A while ago, James King of Redgate spoke to Tony Madonna, the Microsoft Platform Lead and SQL Enterprise Architect at BMW about the growing SQL Server estate at the car manufacturer, and how and why monitoring such a large estate is so important.  The conversation was recorded and the video above is on the

  • Webinar

    How to monitor hybrid and cloud-based SQL Server estates

    As organizations move to hybrid or cloud estates, the perception is that monitoring those servers gets more complicated, particularly if you have different servers on different platforms. In this webinar we show you that actually it is quite easy to keep track of your databases and servers, regardless of where they are hosted, thanks to SQL Monitor.

  • Community event

    SQLSaturday Johannesburg

    SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorship. Please register soon as seating is limited, and let friends and colleagues know about the event. Redgate will be there as one of the

  • Article

    Monitoring and Troubleshooting Deadlocks with SQL Monitor

    Tony Davis demonstrates a deadlock incident, as detected in SQL Monitor, and shows how we can find the cause quickly, using the Extended Events deadlock graph provided in the alert details, plus details of the sessions and queries that were running at the time it occurred.