4

In one of our production servers, we need to enable xp_cmdshell for some app functionality.

With our current security measures, it is disabled in all instances and needs to be enabled for just one specific SQL Server.

  1. How bad is it to enable xp_cmdshell?
  2. Is it really a big thing to worry about?
  3. Any specific measure that can be taken to enable it and still limit its use?

Our application team has created a trigger which kicks off another invoice process and they need xp_cmdshell for that.

  • Take a look here: security.stackexchange.com/questions/2722/… – SQL_Hacker Aug 29 '17 at 14:41
  • Why is xp_cmdshell needed for app functionality? Can't the command be invoked directly from the application code? That would be more secure. – Dan Guzman Aug 29 '17 at 14:51
  • @SQL_Hacker : Good link. But have gone through a lot of blogs before posting my questions. Thank you. – Ramakant Dadhichi Aug 29 '17 at 14:52
  • @DanGuzman : Application team has created a trigger which kicks off another invoice process and they need xp_cmdshell for that. – Ramakant Dadhichi Aug 29 '17 at 14:54
  • 3
    @RamakantDadhichi, xp_cmdshell is not needed with the trigger design. The trigger could instead insert into a Service Broker queue which triggers the external process upon receipt of the message. Note that all the work done in the trigger is done synchronously as part of the trigger transaction. – Dan Guzman Aug 29 '17 at 14:59
5

According to the Microsoft Docs for xp_cmdshell:

xp_cmdshell Proxy Account

When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

Thus, if you attempt to execute xp_cmdshell as a non-sysadmin user, assuming you have not configured the proxy account, it will fail. This means only sysadmins can run it. Do you trust your sysadmins?

Once xp_cmdshell is enabled, the shell it spawns has the same security as the account used to run SQL Server itself (or the proxy account, if configured). If that account has full permissions on the local machine, then the shell created by xp_cmdshell will have full permissions on the local machine. Same thing if the SQL Server Service Account is a domain admin (which I would never suggest). Following the principle of least-privilege should mean SQL Server has the minimum rights it needs to function, and xp_cmdshell will have those same set of rights.

If you configure xp_cmdshell to use the proxy account, then the shell spawned by it will have the rights of that account, which can be locked down. However, any sysadmin can modify the proxy account settings, so ensuring you limit who has sysadmin rights is extremely important.

You've mentioned that your application team is wanting to use xp_cmdshell inside a trigger. This is going to really limit the performance of your system, and you really should re-think that design. If, for instance, xp_cmdshell returns an error, the insert into your table will fail. If the call to xp_cmdshell takes several seconds to complete, the rows affected by the insert will be locked during that time, and may cause other sessions to be blocked.

As @DanGuzman pointed out, you should instead use a service broker queue to provide an asynchronous guaranteed message delivery mechanism that won't interrupt the insertion of rows into the affected table. Service Broker queues can be consumed by apps outside of SQL Server in a responsive, reliable, and scalable manner.

  • Woah, you're completely bypassing the ability to set a proxy account for xp_cmdshell here, which can provide rights that are far more locked down than any service account. – Nic Aug 29 '17 at 14:50
  • I did mention that you can use a proxy account, however I've made that clearer. – Max Vernon Aug 29 '17 at 14:54
  • Using the xp_cmdshell well explained.On the trigger part I will have to check with the App Vendor.Thank for your valuable input on this. – Ramakant Dadhichi Aug 29 '17 at 15:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.