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.
Scenario:
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?
Solution:
First you'll need to make
sure that XP_CMDSHELL is allowed.
|
-- Enable XP_cmdshell
EXEC sp_configure 'show
advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
|
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.
