Our SSIs package running via SQL Agent Job on a dedicated Reporting server had been running fine since deployed more than 3 weeks ago. Due to performance issue we had to fail over the database to Secondary. Since we failed over the SQL Agent Job is failing with error in package indicating that the login failed for domain user (DOMAIN\etl.user.
We also noticed this error: The login is from an untrusted domain and cannot be used wit Integrated authentication.
Below is the visual of our Architecture and explanation:
More details:
- Connection string is set at the SQL Agent job and the connection is made using Listener.
- The SQL Agent job is ran as Proxy user as we do not want the user to have SysAdmin privilege.
- I have ensured that the domain login on secondary server exist.
Anyone has any idea why the package would fail?
Best Answer
I figured what was the issue. Apparently the SSIS job was connecting with 2 databases that site on 2 different clusters, thus it required to connect to 2 different Listeners. When I added the user on secondary nodes of both clusters the issue was resolved. We were able to test this during the patching windows when we had to fail-over.