This article is essentially a combination of two articles I had read online. One made it easy to find the errors and the other made it easy to find the fix, but I wanted one place where I could find BOTH. What do you need in place in order to use xp_cmdshell? I warn you, there are obvious security risks and implications for going this route. (I’m not recommending usage of xp_cmdshell in general, and frankly have no clue how to use it, but you don’t need to know how to use it to enable it) We first need to think about what happens here, from an architectural level:
A user who has logged in to SQL Server executes xp_cmdshell. For this, SQL Server need to spawn a process in Windows. A process in Windows need to execute in a Windows user account. How does SQL Server know what Windows account is to be used? If the SQL Server login who is executing xp_cmdshell is sysadmin, then SQL Server will use the service account (it will not “pretend to be somebody else” or emulate another user). However, if the login is not sysadmin, then one would need to configure what Windows account to be used (using sp_xp_cmdshell_proxy_account). Note that this configuration is the same for all non-sysadmins. But there’s a little bit more to it than a single setting. Below is an outline of what needs to be done. Step 2 and 3 are only needed if the one who is to execute xp_cmdshell isn’t sysadmin. Note that the steps don’t have to be performed in the order listed below.
We need to allow usage of xp_cmdshell in general (on 2005 or 2008). Use “Surface Area Configuration” or sp_configure to perform this. We need to have a user in the master database which has execute permission on xp_cmdshell. If you are uncertain about the difference between logins and users, you should read up about it in BOL. We need to say what Windows account should be used when a non-sysadmin user is executing xp_cmdshell.
For users that are not members of the sysadmin role on the SQL Server instance, as mentioned above, you need to do the following actions to grant access to the xp_cmdshell extended stored procedure.
Below are the Errors thrown and the TSQL script correcting said error, that enables this:
(1) Enable the xp_cmdshell procedure
*Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.*
Fix:
|
1 2 3 |
EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE GO |
(2) Create a login for the non-sysadmin user that has public access to the master database
*Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.*
Fix:
|
1 2 3 |
USE master CREATE LOGIN RoseBush WITH PASSWORD = 'securepassword' CREATE USER RoseBush FROM LOGIN RoseBush |
(3) Grant EXEC permission on the xp_cmdshell stored procedure
*Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1 The EXECUTE permission was denied on the object ‘xp_cmdshell’, database ‘mssqlsystemresource’, schema ‘sys’.*
Fix:
|
1 |
GRANT EXECUTE ON xp_cmdshell TO RoseBush |
(4) Create a proxy account that xp_cmdshell will be run under using sp_xp_cmdshell_proxy_account
*Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the ‘##xp_cmdshell_proxy_account##’ credential exists and contains valid information.*
Fix:
|
1 |
EXEC sp_xp_cmdshell_proxy_account 'Domain\WindowsUserAccount','password' |
