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 …”

SQL SERVER - FIX - Replication Error: SQL Server replication requires the actual server name to make a connection to the server  repl-error-01

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”

SQL SERVER - FIX - Replication Error: SQL Server replication requires the actual server name to make a connection to the server  repl-error-02

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:

SQL SERVER - FIX - Replication Error: SQL Server replication requires the actual server name to make a connection to the server  repl-error-03

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)

Previous Post
SQL SERVER – Notes about scheduler with Operating Systems
Next Post
SQL SERVER – Edition Upgrade from Evaluation to Enterprise Core – AlwaysOn Warning!

Related Posts

29 Comments. Leave new

  • corporateworldasiseeit
    September 18, 2015 10:42 am

    I faced a similar issue last month and found a similar solution by googling it.

    Reply
  • Hi Pinal,
    I had a similar problem with sql server 2012 …
    so I solved ….

    sp_dropserver ‘MyserverB’
    go
    sp_addserver ‘MyServerA’, LOCAL
    go

    select name from sys.databases where is_published=1 or is_subscribed=1

    Regards

    Walter

    Reply
  • hi all,
    i configured transaction replication…after synchronisation on both publisher and subscriber …by mistake i deleted one row(eg: empno-111)from subcriber….now i update the same row empno-111 from the publisher side,,,,now i m getting synchronisation issue becoz empno-111 is not availabe on subcriber…….can any one fix this issue

    Reply
  • Thanks, in sql 2012, for some reason the drop / add did not work with variables; but worked with hard coded values, similar to what Walter above suggested. And I had to stop and start SQL server service to take that effect.

    Reply
  • Mötz Jensen
    May 2, 2016 7:27 pm

    Hi all,

    I’m working with a customer on their custom made barcode scanner solution. They use .Net Compact Framework and SQL Server Compact Edition 3.5 SP 2.

    They have som replication configured to download and upload different tables. They are migrating the infrastructure and while doing that, switching the AD names. We are unable to use the FQDN to help the clients reach the correct server during the transition.

    I believe that we are facing the same issue as on this connect incident:
    https://docs.microsoft.com/en-us/collaborate/connect-redirect

    Try connecting to your SQL Server instance, using the FQDN and view the publisher / Distributor properties – you will be meet with the same error as the one from renaming your server.

    Reply
  • i am very problem in replication please give me solution

    Reply
    • Solution is already given. You need a restart after running below commands.

      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

      Reply
      • Msg 15015, Level 16, State 1, Procedure sp_dropserver, Line 42
        The server ‘WIN-R3DCRK63J8E’ does not exist. Use sp_helpserver to show available servers.
        Msg 15004, Level 16, State 1, Procedure sp_validname, Line 17
        Name cannot be NULL.

      • do you see same name in sp_helpserver?

      • Faisal Younus
        May 9, 2018 5:36 pm

        my server say name already exist
        my server name is abc-abcdefghi-abc\abcd but it appears while creating replication abc-abcdefghi-a\abcd and i am unable to connect from target server while creating subscription which abc-abcdefghi-a\abcd. if I gave abc-abcdefghi-abc\abcd then say “SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, “

  • Thanks Pinal, as always! Same issue here, only occurred as a result of a customer cloning a virtual server i.e. the @@SERVERNAME on the cloned server was still that of the original.

    Reply
  • Hi Pinal, I am trying to configure the replication in one of the test environment. This is SQL 2012 server and connecting through the port 14481.

    When I ran below one
    SELECT @@sERVERnAME
    sELECT sERVERPROPERTY(N’ServerName’)
    I got same servername without the port number.

    But while trying to create subscriber , I am getting error like
    “SQL Server requires actual servername to make a connection to the server”

    Please advise

    Reply
  • If you are using SSMS, you will continue to get this issue, even if names match as discussed, if your session is connected to localhost! It’s the wizard that cannot run with the credentials used to log in. I have always logged into the console of a local SQL instance using localhost, but never used the replication wizards. Lesson learned. From now on, when using SSMS, I will always log into the SQL server using its actual name.

    Reply
  • Tks u so mutch

    Reply
  • You are a fountain of SQL intelligence. This is just the answer I was looking for.

    Reply
  • I’m getting the same error. but this didn’t workout.
    Possible reason: My subscription server is a cluster with 2 nodes. clustername: abc_xyz , node 1: abc node: xyz.
    There replication works fine but When I try to failover to the secondary node the replication is not working.
    I’m using PullSubscription

    Reply
  • Ken Sturgeon
    June 22, 2018 8:54 pm

    It would seem that there’d be some risk in changing the server name. What happens to all of the databases on the server as it’s currently named? What of all the applications that may be connected to the server?
    Thank you very much

    Reply
    • This is the exact question I have as well. The “solution” is simple enough – but I really need to understand the implications, if any, of doing this on a production server that has applications already built on top, SSIS packages run on it, etc…

      Reply
  • Can someone explain about below comment:

    If you are using SSMS, you will continue to get this issue, even if names match as discussed, if your session is connected to localhost! It’s the wizard that cannot run with the credentials used to log in. I have always logged into the console of a local SQL instance using localhost, but never used the replication wizards. Lesson learned. From now on, when using SSMS, I will always log into the SQL server using its actual name.

    Reply
  • Is Replication supported when having SQL on Linux?

    Reply
  • When i bot the ip static to run sql the same error show .
    Whet can I do

    Reply
  • Hi,
    I accidentally dropped distribution database on Active/passive sql server 2016. this is new server, not having any subscriptions and publications. Somehow able to cleanup with sp_dropdistributor @no_checks= 1 , @ignore_distributor= 1. now Im unable to re configure distribution. Its giving error
    SQL Server is unable to connect to ‘APCluster1’
    SQL Server replication requires the actual servername to make a connection to the server. Specify the actual server name, “.(Replication.Utilities)

    I can reinstall sql cluster but don’t have much time for any other issues I may encounter while redoing cluster. I request you to kindly let me know if any other option of removing all replication footprints from this server. Regards, RS

    Reply

Leave a Reply

Menu