49

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?

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
Vahid Ghadiri
  • 3,966
  • 7
  • 36
  • 45
  • had this case when used hostname without instance name ( MSSQL01 instead of MSSQL01\MSSQL01 ). After use the second the problem disappeared. – p4w3l May 27 '22 at 10:03

10 Answers10

64

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.

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
Vahid Ghadiri
  • 3,966
  • 7
  • 36
  • 45
  • @TomStickel I also have the same problem with Azure, without the issue of renamed servers – Ricconnect Mar 30 '17 at 14:53
  • @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 – Tom Stickel Mar 30 '17 at 20:18
  • Does one need to run this code on the remote subscriber or the publisher machine? – Bellash Feb 24 '18 at 18:30
  • 1
    If you're having this problem with Azure, make sure you're on the latest version of SSMS. That fixed it for me. – QFDev Apr 12 '18 at 09:06
  • Also make sure to supply the username for the subscriber (Azure DB) in the form `[user_id]@[server_name]`. I wasted hours on this problem. – QFDev Apr 12 '18 at 10:20
  • Had this problem and took the first branch, but needed to `sp_dropserver 'old-server-name' go sp_addserver 'real-server-name', LOCAL go` instead of `sp_dropserver 'old-server-name' go sp_addserver 'real-server-name', LOCAL go` – joshbooks Mar 20 '19 at 00:09
  • 1
    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` – Inphinite Phractals Aug 11 '22 at 21:00
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!

Jim Simson
  • 2,774
  • 3
  • 22
  • 30
eLVik
  • 741
  • 7
  • 14
  • 1
    Yes! It done! But why it have to be set in both server and client? – Cheung Aug 23 '13 at 07:22
  • 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. – eLVik Aug 23 '13 at 09:58
  • 1
    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. – Phill Apr 01 '14 at 08:28
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

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.

Benyamin Limanto
  • 775
  • 10
  • 24
an3sarmiento
  • 323
  • 4
  • 13
  • 2
    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 – Zach Smith May 18 '17 at 17:21
  • Hi @ZachSmith, it worked for me, are you sure in the Login tab you are using only the server name? – an3sarmiento May 22 '17 at 23:56
  • 2
    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 May 23 '17 at 02:01
  • Zach Smith, adding the alias to the windows host file worked for me. Genius level! – Diego Sep 13 '17 at 20:26
  • 2
    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 – Shadman Mar 26 '20 at 18:05
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.

Chris Amelinckx
  • 4,334
  • 2
  • 23
  • 21
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'

Anthony Faull
  • 17,549
  • 5
  • 55
  • 73
galgil
  • 217
  • 1
  • 3
  • 10
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.

Ben
  • 861
  • 8
  • 4
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.

J. Minjire
  • 1,003
  • 11
  • 22
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
Iman Javadi
  • 141
  • 5
  • 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 – Iman Javadi Oct 12 '20 at 09:59
  • 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. – meekash55 Aug 05 '22 at 10:06
0

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

Iman Javadi
  • 141
  • 5
  • Are you sure you are answering the correct question? – seanb Oct 12 '20 at 03:13
  • 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 – Iman Javadi Oct 12 '20 at 04:45
0

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.

Aymen B'm
  • 1
  • 1