2

I have 2 SQL Server instances running on the same server.
Lets call the instances Instance_A and Instance_B and the server Svr0045.
Both server use the same service accounts (a service account is bound to the server, not the instance, so they all share the same service accounts). For simplicity sake, this service account is named Svr0045_SQLSvc.

I have an SSIS package that is running on Instance_B under the service account which tries to access Instance_A using an OLEDB Connection Manager set to Windows Authentication.
I say "tries" because i get the "Login failed for user 'Svr0045_SQLSvc'" error when i run the package.
Since the service account is the same for both instances i assumed that it should work.
The service account has sufficiant rights (since it's a member of the sysadmin role), is granted connection to the instance and is enabled.

Questions:
Is this problem known / is there a reason to this?
How can i get it to work (if there even is a way)?

4
  • You may have an issue with your ProtectionLevel setting, similar to the one here: stackoverflow.com/questions/15745281/… Feb 22, 2017 at 18:34
  • This has been a problem before with this package (since we have some connection managers working with SQL authentication) but was resolved by changing in to password protected. The password is provided and correct (the SQL authentication works). The current problem is with Windows authentication connection managers, and only with this "second" instance on the same server (there is no problem connecting to instances on other servers with Windows authentication). Feb 23, 2017 at 9:07
  • Is the SSIS package trying to access the OS/filesystem outside of SQL?
    – nkdbajoe
    Sep 24, 2019 at 12:07
  • @nkdbajoe no, the SSIS does not try to access OS/filesystem. It's as "simple" as just copying data from 1 table on server A to server B. This specific problem isn't relevant anymore as Server A has been decomissioned in the meantime, but i still wonder how this was even a thing. Sep 27, 2019 at 12:02

1 Answer 1

0

Check your Sever Authentication mode:

enter image description here

Should be "SQL Server and Windows Authentication mode"

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.