1

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'
1
  • Yes it looks like a double hop issue, you can either opt to use a SQL Login impersonation on the destination server, or configure kerberos authentication. For this I would use the kerberos configuration manager. Dec 20, 2018 at 13:14

2 Answers 2

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".

enter image description here

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:

  1. 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

enter image description here

Step 2: Change Linked server mapping

enter image description here

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:

enter image description here

3
  • Can you check if this is done on both servers: "Set up SPNs for both instances. Set delegation to Trust this user for delegation to any service on the service account of the source server". The kerberos configuration manager should be able to spot issues with SPN's on any of the two servers. Dec 20, 2018 at 13:41
  • Hi Randi, thanks for your reply. The first option is the right option for our system. And I believe, the kerberos is already configured. If i check the connection properties on the middle server (B) for my session, I can see that it is using Kerberos SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid; net_transport auth_scheme TCP KERBEROS What else should I verify to confirm kerberos is working correctly? BR, Domen
    – user168550
    Dec 20, 2018 at 13:59
  • @user168550 >>>What else should I verify<<< Your Windows account should be enabled for delegation
    – sepupic
    Dec 20, 2018 at 14:22
0

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

1
  • The issue should be that for some reason kerberos is not being used. Have you tried issueing 'klist purge' to purge the cached tickets? But I guess these should be cleared after a while, not sure. Dec 24, 2018 at 14:46

Your Answer

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

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