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

Question

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

Lisa_Kent

Old Hand

Points: 314
More actions
August 18, 2008 at 9:11 am

#195919

Hi,
I'm working on a virtual server with SQL Server 2005 installed.
The server was called VM_NNNN_ABC1 and the SQL Server had the same name. There were then some problems with the virtual server and it was renamed to VM_NNNN_XYZ1 (and the SQL Server appeared to have been renamed also).
I then tried to define a local publication (no replication was previously set up on this database), but got the error:
SQL Server is unable to connect to VM_NNNN_XYZ1.
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 alternative name ar not supported. Specify the actual server name VM_NNNN_ABC1.
I checked @@servername - this returned VM_NNNN_ABC1, although sp_helpserver showed only VM_NNNN_XYZ1.
So I ran sp_dropserver 'VM_NNNN_ABC1'
followed by sp_addserver 'VM_NNNN_XYZ1', 'local'
and stopped and restarted the database (and SQL Server Agent).
Now, both @@servername and sp_helpserver show the server as being 'VM_NNNN_XYZ1' which is correct.
However, I'm still getting the same error when trying to define a local publication, albeit with a different (correct) actual server name:
SQL Server is unable to connect to VM_NNNN_XYZ1.
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 alternative name ar not supported. Specify the actual server name VM_NNNN_XYZ1.
Can anyone help?
Regards,
LK

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

carolyn v mckechnie Old Hand Points: 306 More actions · Answer 1

Does your Management Studio registration use the actual server name or does it use the IP address or a virtual name? I ran into this problem as well, when I was working with Replication on a clustered server because the clustered server uses a virtual IP address. I set up a new server registration in Management Studio (on the server console) and used the actual server name of the cluster node, then was able to create the publication from that server registration. Hope it helps...

CVM

Lisa_Kent Old Hand Points: 314 More actions · Answer 2

It uses the actual server name rather than the IP address, so I don't think that's my problem. I'm going to set up a new test database and see if I have the same issue.

LK

MicahGentry SSC Journeyman Points: 87 More actions · Answer 3

Has anyone found a solution for this? I am having the same issue, and I am guessing it has something to do with the fact that I am running SQL Server 2005 as a virtual server using Hyper-V.

raj acharya SSCrazy Points: 2805 More actions · Answer 4

hi when you run sp_helpserver what is id of that server is it server

Raj Acharya

forsqlserver SSCoach Points: 18902 More actions · Answer 5

Hi All,

I m getting same problem.I have ss2k5-Sp2 .

Thanks

Atiq Rahman-266579 SSC Veteran Points: 266 More actions · Answer 6

Gavin Lilley posted the solution... read it care fully...

I copied the same...

1 - On the remote server (Subscriber server) run the following sql statement

SELECT CONVERT(char(20), SERVERPROPERTY('servername'));

and take the out put which your Servername if it is default instance, servername\instancename if it is named instance and use the

ouput while configuring alias in step2..

2 - On local server (Publication server) , create an alias to the remote using the name from the above

You can create the Alias using "SQL SERVER Configuration Manger"....

Start/All programs/sql server 2005/SQL SERVER Configuration Manger/SQL Server Native Client Configuration/Aliases

Check BOL how to configure Alias

http://msdn2.microsoft.com/en-us/library/ms181035.aspx

3 - On local server(Publication server), Edit the hosts file to resolve the remoter host name to the remote ip

Host file location is C:\WINDOWS\system32\drivers\etc

4 - Create the subscriber

Regards,

Atiq Rahman

raj4share SSC Journeyman Points: 75 More actions · Answer 7

It's worked, thank you very much! I thought it won't work, as the error message shows alias not supporting, but this worked.

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 8

I know its an old thread but i hope someone can throw some light..

I am getting the same error but my subscriber is an Oracle database.Does anyone come across such issue??

TIA

patricaandesrson02 SSC Enthusiast Points: 101 More actions · Answer 9

Hi friend I have read your thread really its a big problem we will find its solution till than please stay in touch Thanks.server to server replication

sanajmshaji SSC Journeyman Points: 86 More actions · Answer 10

sanajmshaji

SSC Journeyman

Points: 86

November 15, 2011 at 9:46 am

#1408001

Many thanks for your advice.

It worked well.

Cheers

pravels SSC Rookie Points: 43 More actions · Answer 11

Creating the alias did work. My problem is I have a port number appended to server name to login through studio. Creating the alias by following below steps solved my issue. Thanks!