CODEWITHSUNDEEP
sql-serversql-server-agent
Him_Jalpert
Him_Jalpert

Reputation: 2516

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.

Upvotes: 5

Views: 29458

Answers (6)

Bill Mentzer
Bill Mentzer

Reputation: 1

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".  😏

Upvotes: 0

Suman
Suman

Reputation: 1

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.

Upvotes: -2

Robert van den Berg
Robert van den Berg

Reputation: 106

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.

Upvotes: 0

raodba
raodba

Reputation: 51

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

Upvotes: 5

user565869
user565869

Reputation:

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?

Upvotes: 3

user3727926
user3727926

Reputation: 106

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

Upvotes: 1

Related Questions