Monday, March 4, 2019

Access Denied error when running job in SQL Server Agent

This is most common error, I have seen in SQL Server Agent. This error occurs when you try to run a job/program through SQL Server Agent with arguments (as an Operating system CmdExec job step)
This is because in the most cases, SQL Server Agent uses an NT Service account to run the jobs. So, we have to make sure that we should provide all the permissions to this account to avoid any failure.
In other cases, you can configure a user to create or execute Microsoft SQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL Server Agent fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.

By default, members of these database roles can create their own job steps that run as themselves. If these non-administrative users want to run jobs that execute other job step types (for example, SSIS packages), they will need to have access to a proxy account. All members of the sysadmin fixed server role have permission to create, modify, and delete proxy accounts.

If you are trying to access data from shared drive or trying to call a utility from the shared drive then you may be get this error such as Access Denied.
The main reason is that when you try to run the SSIS package in your development environment then the user who is created that package has the full access of the files/utilities path. Hence, package runs without any issues.
But the same package is deployed on the server and run through a job under SQL Server Agent account then it turned out that the SQL Agent account did not have permissions on the Tools directory where sqlcmd.exe or raw data file is located. As a result, we get Access Denied error when it runs under SQL Server Agent account because the Agent account doesn't really know that the file doesn't exist, just that it can't reach such a file.
Troubleshooting: 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?

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

-- Enable XP_cmdshell

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1

To solve this error, we have to give Read & Execute permissions to the SQL Agent Account on that Tools directory where sqlcmd.exe or raw data file is located.