I have an SSIS package that I want to deploy to SQL Server A. This package needs to access tables on SQL Server A, and another SQL Server, B. What is the best way to set this up? Should I create a domain account, and create a matching login on each machine and grant the appropriate permissions? If I did this, how do I run the job that runs the SSIS package completely under that domain account context? How should I be saving connection info within the SSIS package?

Do I really need to use a proxy account? It seems that if I have connection information saved in the SSIS package, it shouldn't matter what account is running the SQL Server Agent or anything like that.

Would just using a linked server using a SQL Server login make the most sense?

  • 1
    First question: do you want the package running under the saved credentials every time it is executed? Assume this is your login information, do you want it always using your creds? what if you leave the company and your account is disabled? – Bryan Hudson Mar 13 '15 at 14:20
  • Ideally, I am thinking a non-expiring service account on the domain would work. If I just have "Windows Authentication" selected on the connection managers in the package, when it runs in a job, it runs completely under the context of the SQL Server Agent account, not my account, right? If I create a service account that can access Server B, and then set up an SSIS proxy with a credential on Server A, would that keep everything running under the service account? – SomeGuy Mar 13 '15 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.