Don't panic

 

Cry about...
SQL Server Troubleshooting


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


Symptom:

When attempting to create a new replication publication the following error is produced:

New Publication Wizard

SQL Server is unable to connect to server 'SSSS'.

Additional information:
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 are not supported. Specify the actual server name, 'OOOO'. (Replication.Utilities)

Where "SSSS" is the name of the current server, and (in my case) "OOOO" was the previous name of the same server.

Steps to reproduce this problem:

  1. Microsoft SQL Server Management Studio
  2. Expand the server
  3. Expand Replication
  4. Right click "Local Publications" and select "New Publication ..."

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.


These notes are believed to be correct for SQL Server 2008 and may apply to other versions as well.



About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.