SQL Configure Distribution Connection Issue

Question

I am trying to configure the distribution of SQL Server (2016). This is an Azure SQL VM. However, when doing so I get the following error:

TITLE: Configure Distribution Wizard
------------------------------

SQL Server is unable to connect to server 'COMPANY-SQL'.

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, ''. (Replication.Utilities)

(note it is not actually COMPANY-SQL but a similar naming format). I have tried, as suggested in many posts to sp_dropserver and sp_addserver. This successfully creates @@SERVERNAME set to company-sql-2 which is the name of both my Azure resource (not sure where COMPANY-SQL originates). I'm lost as to how to connect here despite already being connected to the database & why the error says the actual server name is simply ''.

score 0 · Answer 1 · answered Aug 05 '22 at 12:39

SQL Server is unable to connect to server 'COMPANY-SQL'.

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, ''. (Replication.Utilities)

SERVERPROPERTY: The Windows server and instance name, which together make up the specific server instance, are provided by the ServerName attribute.

@@SERVERNAME: The name of the local server as it is set up right now is provided by @@SERVERNAME.

Run the following commands together and you will get two different names.

SELECT SERVERPROPERTY(N'servername')
SELECT @@SERVERNAME

then change the server name by dropping the name got by @@SERVERNAME command with sp_dropserver procedure and set name got by SERVERPROPERTY command with sp_addserver procedure then Restart the SQL Server Service.

score 0 · Answer 2 · answered May 30 '23 at 11:55

I know that's to late to answer but maybe it helps new visitors.

You should replace the SQL Server server name with the correct network name of the computer

Let's execute this queries on master database.

-- Use the Master database
USE master
GO

-- Declare local variables
DECLARE @serverproperty_servername varchar(100),
@servername varchar(100);

-- Get the value returned by the SERVERPROPERTY system function
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'));

-- Get the value returned by @@SERVERNAME global variable
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME);

-- Drop the server with incorrect name
EXEC sp_dropserver @server=@servername;

-- Add the correct server as a local server
EXEC sp_addserver @server=@serverproperty_servername, @local='local';

That worked for me.

Reference : MSSQLSERVER_18483

And in the next step if you faced with RegCreateKeyEx() Error read the solution here

SQL Configure Distribution Connection Issue

2 Answers2