'Access Denied error when running job in SQL Server Agent

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.

sql-serversql-server-agent


Solution 1:[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

Solution 2:[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?

Solution 3:[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:).

Solution 4:[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.

Solution 5:[5]

If you're running into this issue with SQL 2014 Maintenance Plans, even if the RunAs account and service accounts are fine (as confirmed in EXECUTE AS testing), then try the following:

Check the Local Connection authentication in the Maintenance Plan.

  • if it's set to an account & it's appropriate to do so, switch to Windows NT Authentication, save out the plan
  • if it's already on WinNT, switch to an account, save out the plan, go back in, switch to Windows NT Authentication, save out the plan again.

There was no logging anywhere that would catch this.  I was just thinking where else authentication could be involved, since the failures were around that, but everything was set up properly everywhere else....

As Grandma would say:  "It's always in the last place you look".  ?

Solution 6:[6]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Maximilian Ast
Solution 2
Solution 3 user3727926
Solution 4 Robert van den Berg
Solution 5 Bill Mentzer
Solution 6 Suman

Related Questions