Linked server error

Question

Hi,

I created the Linked server in ServerA using the command :

EXEC sp_addlinkedserver

@server=N'TestLink',

@srvproduct=N'',

@provider=N'MSOLEDBSQL',

@datasrc=N'ServerB;

I am able to get the output using through linked server query - select * from TestLink.xyz.dbo.table1 in ServerA

But when ever i am trying to query from remote server ServerC i am getting below output.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Please help me how to resolve the error?

Thanks,

Jo

Answer 1

This is the famous double-hop issue. That is, when you connect from ServerA from ServerC, ServerA must be able to send your security token to ServerB. For this work, you need to have Kerberos running and SPNs in place. If you are in a workgroup and not a domain, it is unlikely that you can get it to work at all.

As a starting point, when you connect from ServerC, run this query:

SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid

If it says KERBEROS, that's at least a start. If it says NTLM, that's the first thing to you need to change.

I'm not very good at this stuff myself, but I have found that David Postlewaite's presentations has helped me to understand a little bit. You can watch him on YouTube here: https://www.youtube.com/watch?v=oY9-qctTMwQ