Login failed when creating a linked server

Question

Login failed when creating a linked server

Walshy

Mr or Mrs. 500

Points: 590
More actions
August 20, 2012 at 4:04 am

#265483

Hi,
I'm trying to create a linked server from a remote sql server to another remote sql server on the network. What security setting should i use?
Whatever I try fails, I either get 'login failed for user nt authority\anonymous logon' when I impersonate or if I map my network account it says 'login failed for user myaccount' however if I create a linked server from my local sql server to the same sql server it works fine. On that one I have it set to impersonate my network account.
Any help gratefully received.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Robin Sasson Hall of Fame Points: 3359 More actions · Answer 1

I usually create dedicated SQL account on the target server for the source server to log on with.

In the SECURITY option of the linked server on the source box, I select "Be made with this security context" and enter the account's user name and password.

:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

chandan_jha18 SSChampion Points: 11330 More actions · Answer 2

I have seen issues with linked server when I tried to impersonate windows\domain accounts. It is usually related to Kerberos issue where multiple hops are not supported.

I suggest you to use an sql account and use the 4th option in the settings where you can provide username and password.

I would be interested to hear from you on how it goes!

Thanks

Chandan

Walshy Mr or Mrs. 500 Points: 590 More actions · Answer 3

Hi,

Yes that works, I'm just at a loss to understand why it didn't work using my login baring in mind I have another server on the same domain connecting to the same server which does work!

Thanks for your reply.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 4

Walsko (8/22/2012)
Hi,
Yes that works, I'm just at a loss to understand why it didn't work using my login baring in mind I have another server on the same domain connecting to the same server which does work!
Thanks for your reply.

I had same issues in my environment and concluded that both the servers in question should have Kerberos authentication enabled for multiple hops. this requires Service Principal Name to be registered. your domain admin can do that. If you do not do that, then Kerberos will not work and the authentication will fall to NTLM which does not support multiple hops.

So if you can ask your domain admin to set up SPN for two servers in QA environment, you can then test your windows login and if such a luxury is not available, then use sql authentication which does not have to anything with Active Directory.

Good luck!

Chandan