Linked server - connection fail from remote computer (using ssms)

Question

I'm facing issue with linked server on Microsoft SQL Server 2012 (SP4). When I remotely connect to SQL server (where linked server is created) with ssms and try to use/test linked server connection (e.g. Test Connection), I get the error

Msg 18456, Level 14, State 1, Line 14, Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

But, when I log locally to SQL server I can access linked server with no problems. I'm using windows authentication.

Here is my configuration: A (my workstation, ssms client) B middle SQL server (linked server definition, pointing to C) C target SQL server

So, the problem only appears when I'm connecting from A (ssms) to B and from here try to access SQL server on C via linked server. Locally, on B, the linked server is accessible corrrectly. It seems like I'm facing double hop problem, but since my knowledge on this topic is (yet) not deep enough, I would like to ask for some help with diagnosing the issue.

Any help would be really appreciated! Regards, Domen

The linked server is configured as follows: Connections are made using login's current context.

EXEC master.dbo.sp_addlinkedserver @server = N'linkedservername', @srvproduct=N'SQL_SERVER', @provider=N'SQLNCLI11', @datasrc=N'MY_SERVER_C'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'linkedservername',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'collation name', @optvalue=NULL
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'linkedservername', @optname=N'remote proc transaction promotion', @optvalue=N'true'

Answer 1

It is as you guessed a double hop issue.

Recreating the issue, trying to connect from my local SSMS, to the source server, and then connecting to a second destination server via a Linked Server "connection test" that is configured as "be made using the current security context".

Workaround #1

Implement kerberos authentication

The kerberos configuration manager is much easier than manually creating the commands

Download Link

More information on implementing kerberos Here

Also check out step 11 of This link:

11. How do I set up Kerberos for linked servers?

Set up SPNs for both instances. Set delegation to Trust this user for delegation to any service on the service account being used on the instance where the linked server is set up. Basically, the server that must pass the ticket along must be set up for delegation.

Workaround #2

Using a SQL Login to map to the windows login.

Step 1: Create the Login on the destination server and give it the needed rights

Step 2: Change Linked server mapping

Note: if you are adding an other account than the one you are currently creating the linked server with, then it will still throw an error.

Step 3: Test the connection again:

Answer 2

I have checked SPN settings and I got Status "Good" for all SPNs. Also Delegation shows me "Full" delegation type. So, from kerberos point of view everything seems to be OK, but the connection still doesn't work. Additionally, I have noticed that the linked server connection works fine for some of my co-workers from their workstations (win 10 Enterprise), but for some it fails like in my case. We all use domain account to connect to middle server. So, I believe that is another indication it's not Kerberos issue. Any suggestions?