51

I get the following message when I want to create a new publication or Subscription.

"Sql Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address or any other alternate name are not supported. specify the actual server name"

Does anyone know what should I do?

Improve this question
2
  • had this case when used hostname without instance name ( MSSQL01 instead of MSSQL01\MSSQL01 ). After use the second the problem disappeared. Commented May 27, 2022 at 10:03
  • this link solves issue blog.sqlauthority.com/2015/09/18/… Commented Jan 6 at 19:21

11 Answers 11

Reset to default
65

I found the solution in the following link http://www.cryer.co.uk/brian/sqlserver/replication_requires_actual_server_name.htm

thankful to Brian Cryer for his useful site

Quoting from the link to avoid link rot:

Cause:

This error has been observed on a server that had been renamed after the original installation of SQL Server, and where the SQL Server configuration function @@SERVERNAME still returned the original name of the server. This can be confirmed by:

select @@SERVERNAME go

This should return the name of the server. If it does not then follow the procedure below to correct it.

Remedy:

To resolve the problem the server name needs to be updated. Use the following:

sp_addserver 'real-server-name', LOCAL

if this gives an error complaining that the name already exists then use the following sequence:

sp_dropserver 'real-server-name' go sp_addserver 'real-server-name', LOCAL go

If instead the error reported is 'There is already a local server.' then use the following sequence:

sp_dropserver old-server-name go sp_addserver real-server-name, LOCAL go

Where the "old-server-name" is the name contained in the body of the original error.

Stop and restart SQL Server.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

@TomStickel I also have the same problem with Azure, without the issue of renamed servers
@Ricconnect -- apparently you want to use SSCM ( configuration management) on your on-premise and local machines , add alias name / server name to both 32 bit and other ( which is 64 bit) .... that got me further, but then I had an issue with error about compatibility ... i was trying a 2014 sql to a 2016 azure cloud vm sql ... but on 2014 i did setup a publication of a database restored from sql server 2008 r2 .... I need to get back to that project task ... just a bit painful
Does one need to run this code on the remote subscriber or the publisher machine?
If you're having this problem with Azure, make sure you're on the latest version of SSMS. That fixed it for me.
I had this issue when trying to setup replication as well. It was a named instance on a cluster role. Old name to new name: CLUSTERROLENAME\SQLINSTANCE -> CLUSTERROLENAME
|
32

There is another solution to this problem, which does not require **sp_dropserver**, **sp_addserver** or server restarting.

Steps:

  1. Read the error message and remember the SERVERNAME which is in quotes.
  2. Run Sql Server Configuration Manager on the machine with Management Studio installed (usually publisher/distributor side);
  3. Expand the Sql Native 10.0 Configuration\Aliases node;
  4. Create a new alias with name SERVERNAME from 1. It should be a NetBIOS machine name or NetBIOS\instance_name for named instances.
  5. Specify the other options for the alias (port, server and protocol).
  6. Repeat 4 and 5 for 32bit native client.

  7. Test. Run Management Studio on the same machine and try to connect to the server (specify alias as server name).

  8. (Optional) Repeat 2 - 7 for all client machines where Management Studio will be used for replication setup.

That's all!

Improve this answer

3 Comments

Yes! It done! But why it have to be set in both server and client?
These settings not for server, but for SSMS-client. If SSMS and sql-server installed on the same machine, then you can set it just once and this bug will disappear only for this SSMS-client instance.
Quick note, if you're creating a publication via FTP and also create an alias, SQL Server will try to connect to the FTP site via the alias name, which may be incorrect. If you edit the c:\windows\System32\drivers\etc\hosts file and add the alias name with it's IP address it corrects this problem.
8

A different solution is to use the additional parameters el SQL Server management Studio to connect to that database.

To do so, when connecting, the server name will be the one that you get by consulting via @@servername (or the one that the error message prompts you), and then select the "Options >>" button

Select Options >> button

Then, in the Additional Connection Parameters tab, you can add the current name of the database

enter image description here

EDIT:

However, when you only have option to run, eg. Linux container using IP listener, then you need to set the server name with the name from select @@servername then set the SERVER='tcp:,', like Shadman comment said. it will trick to connect to IP with the server name.

Improve this answer

5 Comments

This causes the connection error "A network-related or instance-specific error occurred while establishing a connection to SQL Server. ... (Provider: Named Pipes Provider, error: 40 ...)". I am able to connect to this server without the SERVER='xxx' param. I'm connecting remotely, also I should note that these queries SELECT SERVERPROPERTY('MachineName') and SELECT @@SERVERNAME both return the same string
Hi @ZachSmith, it worked for me, are you sure in the Login tab you are using only the server name?
I added an alias of the host name to the ip address. Originally I was using an ip address as the host name
Zach Smith, adding the alias to the windows host file worked for me. Genius level!
this method works for me but i have to mention that in Server Name paste your result from SELECT SERVERPROPERTY(N'servername') or SELECT @@SERVERNAME and in Additional Connection Parameters add something like this SERVER=tcp:192.168.1.1,1433;. Thanks to @an3sarmiento
5

Short answer: check if you're connecting with the wrong network alias through SSMS

For example, your server might accessible as both:

  • MyDataServer
  • MyDataServer.Company.Com

When a replication publication/subscription is created it gets associated with the name used to connect to the server at the time, if you connect using the different alias or fully qualified name it gives you the error stated on the question.

Details: I just ran into this, the answers about changing the @@servername helped me understand the issue, but I didn't have to do that.

Consider this:

The sql server box was setup as {my_system_name}.local.domain

In our network we have a network alias such that {my_system_alias} allows us to reach the server and avoiding having to enter the fully qualified domain name (FQDN).

Sql Server was installed and also a local replication publication was set up while being connected using the {my_system_alias} name.

I then connected to the server using SSMS specifying the FQDN, if I right click on properties for this existing Replication -> Local Publication I get the error: "Sql Server replication requires the actual server name".

All I had to do: reconnect to the server in SSMS using the alias (or the other way around) and it will allow me to see the Replication Publication details.

PS: Phill's comment in another answer is also an alternative, making a hosts file entry that matches the name you need to use for the connection.

Improve this answer

Comments

4

You need to change the server name in there like a machine name.

  1. Check the server name:

@@servername

  1. Delete the old name of the server that appears to you in the name of the test above:

sp_dropserver 'old_name'

  1. Add a new name server:

sp_addserver 'new_name', 'local'

Improve this answer

Comments

2

Sharing my fix, after trying EVERYTHING else I could find to resolve this issue. I was attempting to setup replication on SQL Server 2017 using SSMS 18.2 (the latest version at the time of this post). When I setup the distributor, everything went OK, but then when I went into distributor properties and checked the box to 'Create Queue Reader Agent', I would get the famous "SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, ''. (Replication.Utilities)" issue.

After many days, I tried a different version of SSMS. Installing SSMS 17.9.1 and re-attempting everything worked properly. Hopefully this helps someone else.

Improve this answer

Comments

1

If you're working on a hosted environment where the server names are weird strings that may actually be being shared with other servers... e.g. WIN-II7VS-100. The solution I've found thanks to @ZachSmith is to edit the hosts file on the server you want to connect from and set it to the static IP of the SQL server you wish to connect to.

Improve this answer

Comments

1

if this problem on replication machine must run below code on replication machine 1- first find real name server

select @@SERVERNAME go

2- drop real server name with code below

sp_dropserver 'real-server-name' go

3-add real server name with code below

sp_addserver 'real-server-name', LOCAL go
Improve this answer

2 Comments

Mr. Tamer did not write in the accepted answer in which machine and when these codes should be executed! If this error has different scenarios. If you read my text, I wrote the scenario to execute this code My effort is to help people not to go astray
Just to complete the this answer... If these command dont reflect then restrat the sql server. by going to SQL config manager and right click on sql services& choose restart.
1

For me i had to make sure that the SERVERPROPERTY(N'servername') and SELECT @@SERVERNAME produce the same output and i have the same server name set in property and @@servername this querie will do the job to fix this:

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 that restarting the ms sql server and the replication worked fine.

Improve this answer

Comments

1

I have encountered this when trying to configure the replication for an SQL Developer Edition running in Docker. Normally, I would connect to localhost (no port) as I have mapped the internal 1433 to the hosts 1433 port.

I have managed to trick the connect for replication by setting server name to localhost,1433.

As a side note I have tried Pinal's solution, but for me the property and the name were the same (I did not mess with server name):

select SERVERPROPERTY(N'servername') -- sql1 select @@SERVERNAME -- sql1
Improve this answer

Comments

0

if this problem on subscription machine for fix this problem just you must install equal or higher version SSMS than destination machine.

Improve this answer

2 Comments

Are you sure you are answering the correct question?
yes this solution is correct. for replication in destination, when we want create a subscription, our SSMS version in current System must be equal or higher than destination SSMS version. this way is one of the base work for this matter

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.