Can I stop a System Admin from enabling SQL Server xp_cmdshell?

Problem

We have an audit requirement to disable xp_cmdshell. However, I've read that a member of the sysadmin role can re-enable xp_cmdshell. Is there any way to prevent this from happening?

Solution

The short answer is no.

Let's look at some ways you might go about stopping a member of the sysadmin role.

SQL Server 2005 only - Surface Area Configuration

If you're using SQL Server 2005, you can use the Surface Area Configuration tool, but that only turns off the feature. It doesn't repeatedly enforce disabling the feature.

Since there is no repeated enforcement, and there is no alert to indicate that xp_cmdshell was turned on, this is easily bypassed.

SQL Server 2005 and above - SQL Server Log

When someone executes sp_configure, the event is capture in the SQL Server log.

However, note that it only tells us the SPID. Also, this can only inform us after the fact. Therefore, it can't prevent a sysadmin from enabling xp_cmdshell.

SQL Server 2005 and above - Default Trace

The default trace, if enabled, captures a lot of information. However, when we go to run a Schema Changes History report, we won't see the fact that xp_cmdshell has been enabled.

The problem is that the Schema Changes History report keys off object changes. The trace does a good job of capturing that. However, executing sp_configure doesn't. So the schema changes history report won't report that we have had this change.

If we look at the trace file(s) that power the Schema Changes History report, we will see entries for ERRORLOG. This corresponds with things being written to the SQL Server log. And sure enough, we can find the entry.

However, we're having to take extra steps beyond another way to get the data (simply looking at the SQL Server log itself). And again, we're not preventing, we're just reporting that the change has happened.

SQL Server 2008 and above - Policy Based Management

In SQL Server 2008 there's policy-based management, which can enforce the setting, but a member of the sysadmin fixed server role can always disable the policy.

Of course, a member of the syadmin role doesn't even have to rely on this. Policies are checked periodically. That means it's entirely possible for a person with such permissions to check how often the policy is being checked and then "time the attack."

Therefore, there's nothing stopping a member of that role from executing the simple:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

SQL Server 2008 and above - Server-level DDL Trigger

Given that we're talking about a T-SQL command, could we write a trigger to handle when sp_configure is run? It turns out we can, but only starting with SQL Server 2008. The appropriate DDL event is ALTER_INSTANCE, but SQL Server 2005 doesn't have it. SQL Server 2008 and above does, so if you're not running SQL Server 2005, this is a potential solution. As for the trigger, it's fairly straight-forward.

CREATE TRIGGER Stop_XP_CommandShell
ON ALL SERVER
FOR ALTER_INSTANCE
AS
BEGIN
  DECLARE @SQL NVARCHAR(4000);
  
  SET @SQL = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                                       'nvarchar(4000)'));
  
  IF (CHARINDEX('sp_configure', @SQL) > 0) AND (CHARINDEX('xp_cmdshell', @SQL) > 0)
  BEGIN
    RAISERROR('Attempt to enable xp_cmdshell detected. This operation is denied!', 16, 1);
    ROLLBACK;
  END;
END;

And when it is enabled, here's what anyone will get if they try to enable xp_cmdshell.

Of course, if you look closely at the error message, you'll note that it tells whoever executed sp_configure and xp_cmdshell that the transaction was rolled back in the trigger. Armed with this knowledge, a member of the sysadmin role can look for and then disable the trigger.

With the trigger disabled, the command can be run with impunity. What's worse, the person could re-enable the trigger, and therefore, you're not even aware that such a malicious act was done (unless you're looking at the SQL Server log).

Anything Else?

There are other tricks, but a member of the sysadmin server role is going to be able to bypass them, too. As a result, the best way to handle this is to minimize what the SQL Server service account can do. Here are some things to consider:

• Isolate the service account so it's only used for that one SQL Server instance.
• Run the service account with the minimal rights possible. Don't put it in the local Administrators group.
• Minimize the number of roles the server performs. If possible, dedicate it to the SQL Server instance.

Also, one thing to remember is that typically speaking, a member of the sysadmin role is going to be more interested in the data inside SQL Server than doing something outside of it. With the current versions of SQL Server, there is no stopping a member of that role from querying the data. That's a greater risk for most organizations.

Source collected from MSSQLTIPS.COm