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:
- Open Microsoft SQL Server Management Studio and connect to Server
- Expand the server node.
- Expand Replication
- Right click “Local Publications” and select “New Publication …”
OR
- Open Microsoft SQL Server Management Studio and connect to Server
- Expand the server node.
- 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)
32 Comments. Leave new
I faced a similar issue last month and found a similar solution by googling it.
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
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
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.
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.
i am very problem in replication please give me solution
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
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?
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.
Thanks Paul
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
create an client alias on local machine so that you can connect in SSMS without port.
I created alias but still not working. please suggest.
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.
I learned it hard way.
I’m facing the same issue and unable to resolve. How can I setup the replication. Can you explain it more.
Tks u so mutch
Welcome.
You are a fountain of SQL intelligence. This is just the answer I was looking for.
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
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
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…
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.
Is Replication supported when having SQL on Linux?
When i bot the ip static to run sql the same error show .
Whet can I do
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
man your posts and your websit is ALWAYS HELPFUL.. i appreciate your generosity in sharing your knowledge to us… more success in your endeavour….
Hi Pinal Dave, I am using DNS name to connect to sql server ( as my Sql server is different domain). THe DNS name is not a server name for sure . I am puzzled to solve this issue . Thanks for your help .
Hi pinal,
will be there impact to customer after changing the server name.