Access Denied error when running job in SQL Server Agent

Question

I am trying to get SQL Server Agent to run a program with arguments (as a Operating system CmdExec job step), but everytime it runs the job I receive the following error: The process could not be created for Step 1 of job, reason: Access is denied).

The research I did online indicated that this could be a permissions issue. I set up a new user account in Windows and gave it full permissions on the program I wanted to run, then mapped this user profile to the SQLSERVERAGENT profile within MS SQL but I still get this error.

Any help with this would be appreciated.

Answer 1

above steps worked for me

Enable XP_cmdshell
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Create credential

CREATE CREDENTIAL cmdshell_agent WITH IDENTITY = 'account_name', SECRET = 'password';
GO

Create proxy

USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'tst_Proxy',@credential_name=N'cmdshell_agent', @enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'tst_Proxy', @subsystem_id=3
GO

After this use tst_proxy in the sql agent job to run the job. Job ran successfull

Answer 2

I had the same problem, and the solution was embarrassingly simple: my path to the .exe was off. It's a little confusing that this reflects as an "Access Denied" error (because the Agent account doesn't really know that the file doesn't exist, just that it can't reach such a file).

One simple step you can take while troubleshooting is to run the exact command in a command window. Does it work as expected? How about if you log in using the Agent account?

Answer 3

First you'll need to make sure that XP_CMDSHELL is allowed.

exec sp_configure 'xp_cmdshell',1
go
reconfigure

1. You'll need to create a credential with the user you created.
2. Create a proxy referencing the credential you created. Give this proxy access to the "Operating System(CmdExec)" subsytem.
3. In the job step itself, make sure that it is executing as this proxy (Run as:).

Answer 4

Got the same error message. It turned out that the SQL Agent account did not have permissions on the Tools directory where sqlcmd.exe is located. Gave Read & Execute permissions to the SQL Agent Account, problem solved.

Answer 5

We need to make sure that the account through which the job is being executed has full access to the folders/document involved in the job steps.

This should resolve the issue without adding the proxy.