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'