November 15, 2012 at 11:28 am
We have multiple SSIS servers and would like to keep the Integration Services Catalog on a separate SQL box. I placed all the packages on a single server and created jobs to run the SSIS packages. These jobs call out the server that holds the packages and attempt to run the ETL processes but halt because of Authentication issues.
The SQL Agent is set with the proper authentication on all SSIS boxes. Everything works fine when I run the job and the catalog is on the same server, but when I call a remote box that stores SSIS Packages it fails trying to use the NT AUTHORITY login to run the packages. I am not sure why this is happening.
All Servers use the same authentication with the SQL Agent using a verified id that works. I have also tried credentials and received the same result. It seems odd I should be able to call another server to load packages in a job but then uses a different authentication to run the packages.
We can store all the packages locally on each SSIS server but would like to run several ETL Servers while storing the packages on a separate server for easier management.
Any ideas? Thanks in advance.
November 16, 2012 at 12:33 am
Does the SQL Server account uses a domain user?
You need to give this user permissions on the SSIS catalog.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 16, 2012 at 4:09 am
Yes, the account is a user account that has access to all relevant servers. All SSIS servers use the same account as the SQL agent log on.
I can create jobs using the remote catalog, I don't have a problem until I try to run them.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply