Running SSIS package with linked server through a SQL Job

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

Answer 1

Most likely that you launch job from default Run as login. You should create credential with your login, so package will have access and priveleges to linked database under your login. You may read this article for better explanation.