none
Running a SSIS package with SQL Job and Linked Server

    Question

  • I have a SSIS 2008 package. In one of the Script task I am calling a stored procedure which is  using Openquery using linked server. I deployed this package with protection level as "EncryptWithPassword" and gave a password to the package. Created a SQL job and edited its command line to include the password. If I login to SQL Server Mgmt Studio with Windows Authentication and run the job manually it runs fine. But when I schedule it then I get an error that "The Communication link to Linked server failed".

    Please help 
    Saturday, December 21, 2013 10:03 AM

Answers

  • I think this is due to the fact the job runs under whatever the SQL Agent account runs under which happened not to have the rights to use the linked server.

    The typical remedy is in using a proxy to run the SSIS step of the Agent job: http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/


    Arthur My Blog

    Monday, December 23, 2013 2:58 PM
  • Hi Vivek.B,

    The issue should occur because the SQL Server Agent Service Account or SQL Agent Proxy account under which the job step runs doesn’t have sufficient permissions on the linked server.

    If the job owner is the sysadmin fixed server role, the job can be run under the SQL Server Agent Service Account or a proxy account, then please make sure the SQL Agent Service Account or the proxy account has corresponding login on the linked server. If the job owner is not a sysadmin fixed server role, the job must run under a proxy account. In this case, make sure the proxy account has a corresponding login on the linked server.

    Reference:
    http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/13/who-owns-my-job-and-who-runs-it.aspx

    Regards,


    Mike Yin
    TechNet Community Support

    Tuesday, December 24, 2013 1:25 PM

All replies

  • I think this is due to the fact the job runs under whatever the SQL Agent account runs under which happened not to have the rights to use the linked server.

    The typical remedy is in using a proxy to run the SSIS step of the Agent job: http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/


    Arthur My Blog

    Monday, December 23, 2013 2:58 PM
  • Hi Vivek.B,

    The issue should occur because the SQL Server Agent Service Account or SQL Agent Proxy account under which the job step runs doesn’t have sufficient permissions on the linked server.

    If the job owner is the sysadmin fixed server role, the job can be run under the SQL Server Agent Service Account or a proxy account, then please make sure the SQL Agent Service Account or the proxy account has corresponding login on the linked server. If the job owner is not a sysadmin fixed server role, the job must run under a proxy account. In this case, make sure the proxy account has a corresponding login on the linked server.

    Reference:
    http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/13/who-owns-my-job-and-who-runs-it.aspx

    Regards,


    Mike Yin
    TechNet Community Support

    Tuesday, December 24, 2013 1:25 PM