1

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.

1 Answer 1

2

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
3
  • Thank you for this. After great pain with OleDb providers I have got this working using your example. Sorry if I am being slow, but I still do not quite understand what the difference is between creating an CNAME record in the domain's DNS and putting a record in the hosts file on the database server where the linked server is created. The former works and the latter does not... using the same server alias.
    – ktakmn
    Aug 2 at 17:22
  • Because a hosts file entry is more like an DNS A-record. Aug 2 at 17:35
  • 1
    Ok it is possible I am starting to get my head around it. I tested putting an A record into our DNS and it does indeed give the same error as when I put a record in the hosts file: login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. However, I do have an A record which works in the DNS with linked servers. That A record has the same name as the server name - presumably that it why it works with Kerberos, whereas A records with names different to the server name do not work? Thank you again.
    – ktakmn
    Aug 3 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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