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'
Best Answer
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:
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: