xp_cmdshell is Microsoft extend store procedure. It will spawn the cmd shell under sql server process to let user to run any command or script under windows host. By default it is disable on the sql server.
Lets see how that works, Below code will ping the loop back interface and sleep for 500 secs
From procexp, you can see sql server has spawn the child process cmd and it is running the same credential as SQL server account.
By default, only sysadmin can use xp_cmdshell. As alternative, we can create the sp_xp_cmdshell_proxy_account to allow user to run it.
When I tried the sp_xp_cmdshell_proxy_account, I got below error
Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '0'.
So I tried to look around the error message, the dm_os_ring_buffer usually store the os feed back information.
unfortunately, this time it does not tell us something we do not already know. ( Msg 15137, Level 16, State 1)
It is because the UAC policy on my Win7 cause the issue. To resolve the issue. Just run the SSMS under administrator mode.
To test the proxy account, we need to grant the user permission to use xp_cmdshell too, otherwise you will get the error:
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'.
Below example use execute as to test the permission
Here is the syntax to go grant the regular user to use xp_cmdshell through proxy account.
How to fix the error code 1326 when use xp_cmdshell
Since sp_xp_cmdshell_proxy_account store user’s password inside the sql server, if the windows account password get changed, the proxy account needs to be updated. Otherwise, it will get the error message as:
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1326'.
To fix the issue, run the
ALTER CREDENTIAL [##xp_cmdshell_proxy_account## ] WITH IDENTITY = 'DOMAIN\USERID',
SECRET = 'PASSWORD';
Change the credential does not require bounce the SQL server to make it effective.
Another scenario for error message is the account get drop and recreate in the domain, even the name is the same but the SSID has been changed. In this case, we have to drop the credential and recreate it.
select name,sid,type_desc,create_date,modify_date from sys.server_principals
To drop and re create the credential:
GRANT EXECUTE ON xp_cmdshell TO [PO7\summer]
Trouble shooting XP_cmdshell hang
What happen if windows host hang or the xp_cmdshell just hang without returning the result?
Use sp_who2 to identify the spid
Use sys.dm_exec_requests and sys.dm_exec_sql_text to confirm it is the xp_cmdshell
Till now, we still have no way to identify what was xp_cmdshell is running
After issuing the kill command. The spid is in the roll back state.
The easiest way to get the hanging child process id is using procexp but the procexp is the GUI tool
The command line version of it is pslist
pslist \\hostnmae –t
taskkill /PID 8036 /F
Check proxy account for xp_cmdshell
select * from sys.credentials
- sys.credentials store the ##xp_cmdshell_proxy_account## information, we can query it to verify whether proxy account is created or not.