0

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: enter image description here

More details:

  1. Connection string is set at the SQL Agent job and the connection is made using Listener.
  2. The SQL Agent job is ran as Proxy user as we do not want the user to have SysAdmin privilege.
  3. I have ensured that the domain login on secondary server exist.

Anyone has any idea why the package would fail?

Improve this question
1
  • I've read that the error you mention can occur if the SPNs are not set correctly. If the SSIS package is trying to log in using the AG listener, make sure the SPN on it is correct. If NODE-A and NODE-B are not using the same service account for the SQL Server service, that could cause it to work on one but not the other. The solution would be to give the service account permission to update SPNs, run a script to update SPNs when failover occurs, or have both servers use the same service account.
    – Tony Hinkle
    Nov 13, 2019 at 19:35

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.