SQL SERVER – FIX – Replication Error: SQL Server replication requires the actual server name to make a connection to the server 

Replication as a topic is quite old and proven technology. After I saw the introduction of SQL Server AlwaysOn from SQL Server 2012, I hardly see folks talking about replication. For me replication will always be a technology which allows for partial (selected tables/articles) synchronization technology, which a lot of people in a large DB world still require.

Recently, I was trying to configure replication for one of my upcoming sessions internally playing around. I was getting the same error for “Configure Distribution” and “New Publication”. Here is the text of the error message:

TITLE: Configure Distribution Wizard
——————————
SQL Server is unable to connect to server ‘SQL16NODEB\SQL2014’.
——————————
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, ‘MyServer‘. (Replication.Utilities)
——————————
BUTTONS:
OK
——————————

On my machine, here are the steps to reproduce this problem:

1. Open Microsoft SQL Server Management Studio and connect to Server
2. Expand the server node.
3. Expand Replication
4. Right click “Local Publications” and select “New Publication …”

OR

1. Open Microsoft SQL Server Management Studio and connect to Server
2. Expand the server node.
3. Right click “Replication” and choose “Configure Distribution”

First, notice the error message, it talks about two names here: SQL16NODEB\SQL2014 and MyServer. I have captured profiler on the SQL instance to find out what is being done by SQL Server to raise this error. Here are the two meaningful queries:

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


When I run them individually in SSMS, I get below output:

After seeing this I realized that I changed my server name recently for some other demo and that is causing a problem.

DECLARE @actualname NVARCHAR(100)
DECLARE @currentname NVARCHAR(100)
SELECT @actualname = CONVERT(NVARCHAR(100), SERVERPROPERTY(N'servername'))
SELECT @currentname = @@SERVERNAME
EXEC sp_dropserver @currentname
EXEC sp_addserver @actualname, local

After running above, we need to restart the SQL Server Service. Then, make sure that you are getting the same output for below queries:

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

Have you ever encountered the same issue and found some other solution? Please comment and let me know so that we can learn from each other.

Reference: Pinal Dave (https://blog.sqlauthority.com)