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.

Solution 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

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