You are hereFixing the "SQL Server replication requires the actual server name to make a connection to the server." Error


Fixing the "SQL Server replication requires the actual server name to make a connection to the server." Error


By hagrin - Posted on 20 June 2010

Recently, I ran into a whole set of problems setting up a client's SQL 2005 database mirroring solution. I had successfully setup database mirroring numerous times and I couldn't figure out why I was having such a hard time with this instance. I loaded up the Database Mirroring Monitor, tried selecting my Principal server in the mirroring solution and I received the following error -

"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."

I was confident that the name I was entering was in fact the current machine name. I decided to run the following query against the master database on the SQL server in question -

SELECT @@SERVERNAME

To my surprise the name listed didn't match the computer name of the SQL Server! At this point, I realized that this server had a different computer name when SQL was installed and the server had been renamed since then. Fixing this problem isn't a trivial renaming of the SQL instance unfortunately. To fix this issue you need to run the following stored procedures -

sp_dropserver 'old_server_name'
GO
sp_addserver 'current_computer_name', 'local'

At this point, if you re-run the SELECT @@SERVERNAME query, you will get a NULL value for the server name even after a restarting of the SQL Service. Just restart the SQLSERVER service and when you re-run the query the new name will now appear.

Tags