May 11, 2008 at 10:48 am
Hey Guys,
I'm trying to run an ssis package using a new SQL agent job I've created. However, I'm getting an error saying the validtion failed because it cannot connect to the server where my package is getting the data. My package is just a simple Transfer SQL Server Objects task that copies a couple of tables from another server. The source server is on another domain, different from where my destination server resides. But the weird thing is that when I run the package alone, it succeeds. It's only when it's initiated from the job that I get the unable to connect error.
I suspect that it may have something to do with the service that my sql server agent service and intergration services service are running on. The ssis service is running on Network Services account, while the sql agent is using the local system account. I'm unable to change the account of the sql agent, however.
If this is the culprit, could there be a workaround? It's a shame that I can't schedule my package because of this reason.
Really appreciate any ideas you guys may have. 🙂
Jafar
May 11, 2008 at 11:15 am
How are you connecting to this other system? Are you trying to use Windows Authentication? If so, then that will use the SQL Agent account to try to authenticate, which will not succeed.
Instead, try changing the connection to a SQL account.
BTW - it works when you run it manually because you (your windows credentials) has access to both instances of SQL Server.
HTH,
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 12, 2008 at 7:43 am
What about using a linked server to your other domain SQL box?
July 21, 2008 at 7:12 am
Hi
I suggest it's better to use Linked Server which will be flexible
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply