Troubleshooting xp_cmdshell failures

This post assumes you have properly enabled the xp_cmdshell feature using the Surface Area Configuration tool and you have used Management Studio | Server Properties | Security to establish a proxy for xp_cmdshell execution.

Sample command:    master..xp_cmdshell 'whoami'
SQL Authentication: TestLogin   (Public in pubs)

When xp_cmdshell is executed it returns initialization errors with error message 15121. 

Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'API' failed with error code: 'OS ERROR CODE'.

Use the API and OS ERROR CODE to troubleshoot problems.   The API and OS ERROR CODEs can be looked up on MSDN.  You can also use 'net helpmsg ####' to retrieve the text of the OS ERROR CODE.

Bad Password (Server Properties | Security | Proxy does not validate password)
If password is changed outside of Management Studio (Computer Management) so it is not in sync with SQL.
If user was dropped and recreated from the system (Computer Management) so it has a SID change.

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'.

 1326 = Logon failure: unknown user name or bad password.

User does not have login as BATCH rights (Local Security Policy | User Rights Assignement | Logon As A Batch Job)

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: '1385'.

1385 = Logon failure: the user has not been granted the requested logon type at this computer.

Note: Drop the 'W' when searching  LoginUser not LoginUserW for the unicode versus ascii version LoginUserA

Success

master..xp_cmdshell 'whoami'

output
-----------------
dorrdelltestuser

Note: 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.

Bob Dorr
SQL Server Senior Escalation Engineer