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


By:   |   Updated: 2013-07-03   |   Comments (20)   |   Related: More > Security

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.

SQL Server 2005 only - Surface Area Configuration

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.

SQL Server 2005 and above - 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.

SQL Server 2005 and above - Default Trace

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.

This corresponds with things being written to the SQL Server log.

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.

SQL Server 2008 and above - Policy Based Management

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.

SQL Server 2008 and above - Server-level DDL Trigger

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.

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.

Next Steps


Last Updated: 2013-07-03


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, August 28, 2018 - 1:11:07 PM - Alex Back To Top

 Brian,

"Therefore, it's not the DBA you have to worry about it, it's the attacker. If the attacker is running standard scripts and they don't think to check whether or not xp_cmdshell is enabled, the script fails."

You are betting on the attacker to not check this?


Saturday, September 06, 2014 - 3:36:48 AM - Orlando Colamatteo Back To Top

Thanks for the rundown Brian. Just because you cannot prevent all misuse or abuse of xp_cmdshell does not mean you should not put controls, policies and logging in place to deter malicious behavior and track malicious actions.

The only way I could conceive to prevent the use of xp_cmdshell was to actually go after the lower level effects of calling xp_cmdshell, namely the creation of a cmd.exe process in the OS. We can watch for cmd.exe instances that are parented by the sqlservr.exe process in Windows using WMI and can intercept and turn away the call to execute that code so it would not actually execute anything on the OS.I did a proof of concept on detecting the calls, but have not taken it any further and am confident even if I did get a working solution that an approach like that would not be supported by Microsoft should it cause trouble for some of their internal processes that may try to start a cmd.exe process from within SQL Server.

I would like it if the SQL Team would close the book on xp_cmdshell and remove it from the product completely however I think there are too many people addicted to it for it to be going away anytime soon. That said, they should at least offer us a way to omit it from our installations so it can't simply be enabled, used, and then disabled as you showed in the article. They started saying when SQL 2005 was released (when SQLCLR was introduced) that they will eventually remove our ability to create new Extended Stored Procedures (ESPs) but I haven't seen any real changes in that area in terms of them removing their own ESPs from the product.


Tuesday, January 07, 2014 - 2:39:25 PM - hari Back To Top

can we prevent enabling xp_cmdshell using policy based management?

and also the trigger just says access denied but it changes the value of xp_cmdshell it is not doing rollback when some one tries to enable xp_cmdshell...please let me know how it can be rolled back when someone tries to enable xp_cmdshell and also needs to log the same to sql server. Thank you.


Friday, September 13, 2013 - 11:43:46 PM - Jeff Moden Back To Top

As usual, another awesome article, Brian.  It's short, sweet, and to the point while covering many aspects.  You have no idea how much I appreciate the content of this article.  Although I certainly agree with the concept of "Least Privilege", I've found that disabling xp_CmdShell will do virtually nothing to protect a system from an attacker, either internal or external, from using it for the very reasons you state.  I've seen a couple of demonstrations where the attack software being used typically looks for all of the things you've cited (and more) and provides virtually no roadblock to an attacker.  At best, it provides only several milliseconds of delay to the attacker using xp_CmdShell.  Of course, even if xp_CmdShell were removed, they could always make a self-deleting job that used CmdExec or Powershell to do their dirty work.

Thank you again for the time you've spent in putting this very informative article together.  I very much appreciate it.


Wednesday, September 11, 2013 - 12:05:04 PM - K. Brian Kelley Back To Top

The article is titled that way because that's the way the question is often asked by auditors and in forums. Quite simply, if someone is searching for said article, it's titled that way so they can hopefully find it in order to be able to understand the limitations and report back, if necessary.


Wednesday, September 11, 2013 - 10:49:39 AM - the sqlist Back To Top

Oh, one more thing, the article title is:

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

which is impossible, and not needed if the least priviledged principle is applied and the xp_cmdshell stays disabled.


Wednesday, September 11, 2013 - 10:45:48 AM - the sqlist Back To Top

As someone said before. if the DBA account has been compromised the least worry is xp_cmdshell status. However, I agree with the least priviledged principle, in principle. :o)

 

Cheers!


Tuesday, September 10, 2013 - 9:09:57 AM - K. Brian Kelley Back To Top

sqlist, the reason we look to disable these things is due to the Principle of Least Privilege. If a sysadmin does not regularly need to run xp_cmdshell, it should be disabled. This principle isn't wrong. It has been tested time and time again. 

 

sqlist the thing to consider is that a DBA could have his/her account compromised. Most attacks into the enterprise look to do this via social engineering. Therefore, it's not the DBA you have to worry about it, it's the attacker. If the attacker is running standard scripts and they don't think to check whether or not xp_cmdshell is enabled, the script fails. That's what we want. 

 

Now, if it sysadmins are routinely using xp_cmdshell, then you don't disable. That's in keeping with the Principle of Least Privilege.

 

BTW, the Principle of Least Privilege is something we've talked about for years. For instance, here's how it's applied to Windows user accounts and why we have the UAC:

 

http://technet.microsoft.com/en-us/library/bb456992.aspx

 


Monday, September 09, 2013 - 1:17:44 PM - the sqlist Back To Top

Use the code bellow:

 

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

 

-- To update the currently configured value for advanced options.

RECONFIGURE

 

-- To enable/disable the feature.

EXEC sp_configure 'xp_cmdshell', 1/* -- to enable/disable uncomment/comment this line

EXEC sp_configure 'xp_cmdshell', 0--*/

 

 

-- To update the currently configured value for this feature.

RECONFIGURE

 

-- To test if enabled/disabled

exec xp_cmdshell 'dir c:'

 

Monday, September 09, 2013 - 11:26:34 AM - CG Back To Top

I am fairly new to XP_cmdshell, but had to turn it on for some nightly jobs.  I was unsuccessful in having my stored procedures turn XP_cmdshell on in the beginning and then off again in the end; so I have had to leave it enabled fulltime.  If Andrew Smith, or someone else, could share how they got that (on, then off at the end) to work, I would be grateful.


Monday, September 09, 2013 - 9:43:57 AM - the sqlist Back To Top

I don't understand the need to "prevent" an admin to enable xp_cmdshell sp. If he is an admin then he was entrusted to do what is necessary to keep the server and databses in working conditions and safe at the same time. By the function's nature the admin SHOULD have the ability to enable disable features on a SQL server. xp_cmdshell has been created by Microsoft with a purpose and a very good one for that matter. It is a feature that SHOULD be use when needed and if makes things easier for administration purpose. All this debacle with enabeling xp_cmdshell being a security risk and wrong is... well, wrong. 


Thursday, August 22, 2013 - 5:08:02 PM - Rafael Back To Top

I am in agreement with Andrew that is how it works in real life, also if you grant somebody Sysadmin rights it should mean that this person is technically savvy, knows what is doing and can be trust with this kind of function for the business. Everthing else is just a false sense of security.

 


Wednesday, August 21, 2013 - 3:59:55 PM - Andrew Smith Back To Top

I am not sure I would want to disable the xp_cmdshell totally. When I need to use it in a stored procedure or job, I use sp_configure to turn it on, then turn it off again as soon as I'm done. Vendors for the most part will decline to change their code that uses it and often state it as a pre-requsite to installing their software, as well as dbo in the database(s) and sometimes even "sa" in the instance. Even when you bring up this issue to the IT Security Department, they ofter grant a waiver to the vendor (because management forces them to) to be allowed to use this feature. Management is very reluctant to get tough with vendors. Also, the software acquisition process is marketing based and not engineering based, meaning no RFQ and no operational constraints, requirements, etc.


Thursday, July 11, 2013 - 11:50:34 AM - TimothyAWiseman Back To Top

You make great points, as always.

Along with reducing the permissions the accounts have you can secure different accounts differently.  For instance many places give their administrators more than one account, one with the admin privileges and the other for any and all uses that do not require admin priveleges. 

Also, in some cases it is possible to keep the sensitive data out of computers that are connected to the internet at all.  This is not always possible, and will not really help against an insider threat, but it can help stop certain types of outside attack and require the attacker to gain more direct access than would be otherwise necessary.


Thursday, July 11, 2013 - 9:02:55 AM - K. Brian Kelley Back To Top

I agree with that sentiment, but people change. All it takes is for a formerly trusted DBA to get the wrong end of the court system on alimony and suddenly you have a potential insider threat.

Also, accounts get compromised. The security landscape has changed. In the security community we used to pay lip service to the idea of, "It's not if they'll get in, but when." We typically figured that our kung fu was better than theirs so even though we said such a thing, we believed we could keep them out. Now we know it's impossible. With spear phishing and pass the hash attacks [or pass the pass(word)], attackers are going to get in. So we're now about reducing the permissions accounts have, trying to detect when an attacker gets in, and trying to get them out as soon as we can. 


Wednesday, July 10, 2013 - 6:57:22 PM - TimothyAWiseman Back To Top

Great article, it had some interesting techniques to make it harder to enable xp_cmdshell.

But as Aaron said in his comment, don't give sysadmin rights to someone you don't trust.


Monday, July 08, 2013 - 11:20:16 PM - Aaron Bertrand Back To Top

Nice post. The moral is simple and nothing new: if you don't tust your sysadmin, don't give them sysadmin. :-)


Wednesday, July 03, 2013 - 11:53:24 AM - K. Brian Kelley Back To Top

It can be captured via policy based management. However, ultimately that runs through SQL Agent meaning it is scheduled. As a result, a member of the sysadmin role can time the checks and beat them. Also, a sysadmin can simply disable the policy check. That's what's discussed in the section: 

SQL Server 2008 and above - Policy Based Management

 

 


Wednesday, July 03, 2013 - 10:46:21 AM - Vera Back To Top

I thought you could capture this with policy based management. Raise an alert when it is enabled. Or am I wrong?


Wednesday, July 03, 2013 - 7:34:25 AM - Ramesh Back To Top

Thanks for the article. One of the good part of this article is server-level DDL trigger. Probably I will modify this DDL trigger to log all the entries having Xp_CMDshell, then notify SW vendor (third party tools) to make changes in their application.

Also I have created equivalent CLR procedure to replace Xp_CMDshell and asked my application development team to use CLR procedure.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools