May 1, 2008
SSIS: Access Is Denied from SQL Agent
Problem: I have an SSIS package, MyPackage,
stored on 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
is denied.
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.
A smart
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:
dtexec /DTS "MyPackage" /SERVER
MyServer /CONFIGFILE "c:\SSIS\ConfigFiles\baseline.dtsConfig"
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING
EAnd 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 MyServer to
localhost, tried the agent again, and finally found
success.