Sql-server – How to login to a remote SQL Server instance via IP and specify the Server name

replicationsql server

I'm following instructions HERE to add a remote publisher to a Distributor server here. However, when trying to add a publisher via SQL Server Authentication I get the following error:

enter image description here

THIS recommends checking to see the machine name and servername is the same:

SELECT SERVERPROPERTY(N'servername')
SELECT @@SERVERNAME

They are.

And there is a stack overflow question (https://stackoverflow.com/questions/8212032/sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-t) that recommends adding an additional connection parameter SERVER='XXX'. But this also doesn't work. I get the following error screen:
enter image description here

Both named pipes and TCP connections are enabled on both machines. And I'm able to login via the connection manager when not trying to configure publications.

So I think my question is, how can I specify a servername using SQL Server authentication as if I were logging into the server via a Windows login?

Best Answer

The solution that worked was to create an alias of (server name => IP address of the publisher) on the local machine on the configuration manager.

Though I suspect that this could also have been added to the windows host file on the local server

On configuration manager, I added the following:

Name: <@@SERVERNAME>
Server: IP address
Protocol: TCP (not sure why this isn't named pipes)
port: 1433
Related Question