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

SQL Server replication requires the actual server name to make a connection to the server – How to Resolve it

by

When working with SQL Server replication and you try to add a new subscription under a local publication, under certain circumstances you might end up with the error message: Cannot connect to … SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name (Replication.Utilities).

 

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

 

Why do you get this Error?

The reason for which you get the above error message, most probably has to do with the fact that even though you provide the “correct” instance name in the subscriber connection dialog, the internal server name in the sys.servers system table of your instance, is probably wrong.

This might happen for example, in case where you renamed the network name of the SQL Server instance, without properly following the official procedure as described in MS Docs, that involves also calling certain system stored procedures.

 

How to Resolve the Issue

Read on below, in order to see how you can resolve the issue, if the scenario you are encountering is the same like the one described above and you get the exact error message: SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name (Replication.Utilities).

 

Step 1: Check the instance name registered in sys.servers

The first thing to do for resolving the issue, is to execute the below T-SQL statement against your instance, in order to retrieve the instance name, as it is stored in the sys.servers system table:

SELECT @@SERVERNAME;
GO

 

Step 2: If the instance name returned in Step 1 is not the correct one

If the above query returns a SQL Server instance name which is not the actual name of your instance, then you will have to rename it to the correct one.

So, for example, based on the official Microsoft procedure, for a renamed standalone computer that hosts a default instance of SQL Server, you modify accordingly and run the below T-SQL script:

EXEC sp_dropserver '<old_name>';  
GO  
EXEC sp_addserver '<new_name>', local;  
GO

 

Similarly, for a renamed standalone computer that hosts a named instance of SQL Server, you modify accordingly and run the below T-SQL script:

EXEC sp_dropserver '<old_name\instancename>';  
GO  
EXEC sp_addserver '<new_name\instancename>', local;  
GO

 

Right after you properly rename the SQL Server instance, you need to restart the instance.

After restarting the instance, you may try again to add the subscription.

Hope this helps!

 

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

Featured Online Courses:

 

Related SQL Server Administration Articles:

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 5,372