SSIS: Access Is Denied from SQL Agent
May 1, 2008
Problem: I have an SSIS package,
MyServer in the Package Store. I create an
Agent to run the package, running under a SQL account hooked up to
a proper proxy and credentials for Windows authentication. Works in
development, doesn't work in production: the agent gets the error
Connect to SSIS Service on machine "MyServer" failed: Access
Solution: Clearly there is something different between the two servers, and it is probably an important difference. Kirk Haselden has a few comments about this issue. They're instructive, but didn't solve my problem. I granted my proxy account full access to MsDtsServer, but still I get the denial.
guy once pointed out that the
dtexec command line
program shows more information about errors than the agent does, so
in the Agent properties → my job → edit → Command
Line tab, I copied the command line options. Then I opened up a
command prompt and ran
dtexec /DTS "MyPackage" /SERVER MyServer /CONFIGFILE "c:\SSIS\ConfigFiles\baseline.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
And it worked.
Clearly there is still some problem with remote connections. The agent sees the proper server name seems to try a remote connection instead of a local one — even though the SQL Server instance with the agent is the same one driving Integration Services.
Back in the
Job Step Properties, I edited the
command line manually and changed
localhost, tried the agent again, and finally found