CMDEXEC The process could not be created for step... (reason: Access is denied). The step failed

Question

I have created a job to test executing a CmdExec step. The job step is simply executing a batch file with dir as the command.

The job is executing as the SQL Server service account. This account is both sysadmin of SQL Server and Administrator in Windows. The login also has been granted exec to xp_cmdshell, in master. In Surface Area Configuration, XPCmdShellEnabled = True. If I run the command from the query window it executes as expected.

Question: why am I still getting the job step error? This is plain vanilla install of SQL Server 2008 R2 with SP2 installed. Nothing fancy going on here.

Note: I know how horribly insecure this is, but this is where it has come to in order for me to chip away at this and try and resolve this error. Best I can tell, this just can’t be made to work and BOL makes it sound so easy.

Answer 1

ok, so instead of using a CMDEXEC job step, I changed it back to a t-SQL script step replaced the batch file name with the full string:

EXEC xp_cmdshell 'c:\dircommand.bat' 

and it works. So I am not sure what the problem is with Operating System job steps but at this point I am passed the hurdle. funny thing is, I have another server setup the other way and it works fine. Thanks for the offers of assistance.

Answer 2

This post looks old, but I thought I'd post something just in case someone else encounters this issue. Someone else (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35882) was having a similar problem, but they were able to change the job owner to sa (apparently, the job owner was the user) and it worked. I found a post by @Tara, which discussed "How to reset SQLAgentCmdExec". I'm not sure if this is the OP's problem.

Answer 3

I had the exact same issue as you were facing. I was running a sql agent job with a proxy account. the account had all of the necessary access from a windows OS side of things. the issue was somewhere between SQL Server & Windows. I found this article: Create a SQL Server Agent Proxy

In it:

Permission to act as part of the operating system (SeTcbPrivilege) (only on Windows 2000)

1. Permission to bypass traverse checking (SeChangeNotifyPrivilege)
2. Permission to replace a process-level token (SeAssignPrimaryTokenPrivilege)
3. Permission to adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
4. Permission to log on using the batch logon type (SeBatchLogonRight)

SeAssignPrimaryTokenPrivilege was a missing requirement! So according to MS Documentation, the account that the SQL Server Agent Service runs as requires the following above permissions to be able to support SQL Server Agent proxies.

Start->Run->gpedit.msc

Navigate to: Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment

Find the policies and ensure the proxy account is in the settings above!