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.
xp_cmdshell?Our application team has created a trigger which kicks off another invoice process and they need xp_cmdshell for that.
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.
