Cannot connect to linked server using name in hosts file

Question

I am trying to set up a linked server in SQL Server 2017 using the local Windows hosts file on the server where the linked server is being set up. The reason for this is to allow me to hard-code the linked server name but configure the server it points to by changing the local hosts file.

I am using Windows Authentication on both database servers. I am able to access both.

When I create a linked server referencing the IP address, like so, it works. I can connect and query a sample table.

EXEC sp_addlinkedserver @server = '192.168.1.109'
, @srvproduct = 'SQL Server'

When I add the following line to the host file, the linked server created above, which connects via IP address, no longer works.

192.168.1.109 hostsfiletest

I get the following error:

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

Normally I would think this is something to do with lacking SPNs, but both servers in this situation have SPNs set up, I can connect if I remove the above line from the hosts file once again.

Also, if I try to set up another linked server using the name in the hosts file, like so, it also fails, with the same error.

EXEC sp_addlinkedserver @server = 'hostsfiletest'
, @srvproduct = 'SQL Server'

I am not sure where I am going wrong.

I can ping the target server from the server I am setting the Linked Server up on, and I can connect to the target SQL Instance from that same server using hostsfiletest as the instance name, so the line in the hosts file is clearly doing something. When I remove the line from the hosts file I cannot connect using hostsfiletest as the instance name.

As another test, when I put the same line as above into my laptop's local hosts file, I am able to connect to the target server in SSMS using hostsfiletest as the server name.

I would be grateful for any assistance. I am also open to other ways of achieving the same thing, if there would be easier ways of setting up a linked server which points to a configurable location.

Answer 1

Normally I would think this is something to do with lacking SPNs

Yep. Kerberos SPNs depend on the hostname you use to connect. If you connect to a host called SomeServer.foo.bar the the service account on running the remote SQL Server must have an SPN for MSSQLSvc\SomeServer.foo.bar. This is essentially the same scenario as: How to Add a Hostname Alias for a SQL Server Instance

You can update the OleDb provider you use and decouple the linkded server name from theSQL Server name at the same time like this:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'MYLINKEDSERVER', 
                                   @srvproduct=N'SQL', 
                                   @provider=N'MSOLEDBSQL', 
                                   @datasrc=N'Someserver.foo.bar', 
                                   @catalog=N'MASTER'

GO
EXEC master.dbo.sp_serveroption @server=N'MYLINKEDSERVER', 
                                @optname=N'rpc out', 
                                @optvalue=N'true'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MYLINKEDSERVER', 
                                     @locallogin = NULL , 
                                     @useself = N'True'
GO
exec ('select 1 a') at MYLINKEDSERVER