We had a devil of a time trying to figure out how to get an SQL Server Agent Job to run 3 DTSX packages that needed to collect their source data from 2 outside database servers.
The Server Agent by default executes all its jobs with its own service account as the user. Well, that didn't sit well with the other 2 databases, who didn't give a rats butt about that user - they weren't letting him/her/it in.
Finally figured out we could swap that default user with one that was a recognized user on the other 2 servers.
We had to set that user up as a Credential. In object Explorer, go to Security, and then right click to set up a new Credential. I just put the acceptable user id in as both the Credential name and Identity, along with that user's network pw. I left Encryption provider unchecked.
Then I set up the same as a proxy under SQL Server Agent.
Then I changed the default 'Run As' on each job step to the new proxy, which now came up as a drop menu choice.
Badda bing, bang, boom, the job now worked.
Once we were able to get our server's service account set up as a user on the other 2 servers, we switched the 'run-as' back.
No comments:
Post a Comment