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.

Desiredesirea

How are you "running" the program? xp_cmdshell? A job step (Operating system CmdExec, PowerShell, etc)? Other method? – Beloved

Yes I'm running it as Operating system CmdExec – Desiredesirea

Did you restart SQL Agent after re-configuring the login account? – Beloved

Yes I did, still having the error. – Desiredesirea

Who is the owner of the job? SA? – Beloved

I don't have any more simple ideas. Have you tried to launch the app (say, from a batch file with appropriate parameters) using "runas" with the user account you created? Does it work that way? You could also try running a test batch file from your sql job--have it output %USERNAME% to a log file to verify the job is running under the new user account: ECHO %USERNAME% > C:\Temp.log – Beloved

I can give those a shot. I've tried running the app from the command prompt within Windows and it's worked without a problem, it's just getting it to run within SQL Server Agent that has the issue. – Desiredesirea

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

Brana

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?

Thorin

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

Pomander

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.

Renown

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

Camboose

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

Bannasch