Stephen A. Fuqua (SAF) is a Bahá'í, software developer, and conservation and interfaith advocate in the DFW area of Texas.

SSIS: Access Is Denied from SQL Agent

May 1, 2008

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 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 MyServer to localhost, tried the agent again, and finally found success.