September 23, 2014 at 9:36 am
I have created a Test SSIS Package within BIDS (VS 2K8, v 9.0.30729.4462 QFE; .NET v 3.5 SP1) that connects to our Test Listener.
There is only 1 Connection Manager Object, and OLE DB Provider for SQL Server.
The ConnectionString lists: Provider=SQLOLEDB.1;Integrated Security=SSPI
The Test Connection within BIDS works.
The Package Control Flow has just 1 Object, and Execute SQL Task that performs an Exec on an SP that contains only a Select (Read).
The Package runs within BIDS.
I've placed this Package within a Job on the Primary Node. Ive run the job successfully using 32 bit runtime on and off. The location of the file on the server happens to be on a share that resides on what is currently the Secondary Node.
When I try to run the exact copy of this Job on the Secondary Node (Which has been Set up for Read All Connections; Yes), I get an error, regardless of the 32 bit runtime opiton. At this point, the location of the file is on the Secondary Node.
The Error is: "Login failed for user 'OurDomain\Agent_Account'".
The Agent is a member of NT Service\SQLServerAgent on both instances, and that account is a member of SysAdmin. Adding the Agent account as well, and giving that account SysAdmin, makes no difference either.
Why can't I get this to work?
Thanks,
ranmazure
September 23, 2014 at 2:08 pm
I've tested a few other scenarios:
When I create a Job that runs the Select SP in a TSQL Job Step, rather than within SSSI connecting to the Listener, the Job works on both the Primary and Secondary.
I then copied the SSIS package twice, with 1 copy connecting to the Primary, and the other copy connecting to the Secondary.
I then ran these packages within Jobs in the same way.
The Primary successfully ran both Jobs.
The Secondary successfully ran the job that connected to itself, but failed on the connection to the Primary, which is what connecting to the Listener would be also doing.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply