Sql Server replication requires the actual server name to make a connection to the server

Question

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?

Answer 1

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.

Answer 2

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!

Answer 3

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.

Answer 4

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

1. Check the server name:

@@servername

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

sp_dropserver 'old_name'

3. Add a new name server:

sp_addserver 'new_name', 'local'

Answer 5

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.

Answer 6

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

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

Regards,

Answer 7

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.

Answer 8

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