sp_adddistributor (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance

Creates an entry in the sys.servers table (if there isn't one), marks the server entry as a Distributor, and stores property information. This stored procedure is executed at the Distributor on the master database to register and mark the server as a distributor. In the case of a remote distributor, it's also executed at the Publisher from the master database to register the remote distributor.

Transact-SQL syntax conventions

Syntax

sp_adddistributor
    [ @distributor = ] N'distributor'
    [ , [ @heartbeat_interval = ] heartbeat_interval ]
    [ , [ @password = ] N'password' ]
    [ , [ @from_scripting = ] from_scripting ]
    [ , [ @encrypt_distributor_connection = ] N'encrypt_distributor_connection' ]
    [ , [ @trust_distributor_certificate = ] N'trust_distributor_certificate' ]
    [ , [ @host_name_in_distributor_certificate = ] N'host_name_in_distributor_certificate' ]
[ ; ]

Arguments

[ @distributor = ] N'distributor'

The distribution server name. @distributor is sysname, with no default. This parameter is only used if setting up a remote Distributor. It adds entries for the Distributor properties in the msdb..MSdistributor table.

[ @heartbeat_interval = ] heartbeat_interval

The maximum number of minutes that an agent can go without logging a progress message. @heartbeat_interval is int, with a default of 10 minutes. A SQL Server Agent job is created that runs on this interval to check the status of the replication agents that are running.

[ @password = ] N'password'

The password of the distributor_admin login. @password is sysname, with a default of NULL. If the password is NULL or an empty string, @password is reset to a random value. The password must be configured when the first remote distributor is added. distributor_admin login and @password are stored for linked server entry used for a distributor RPC connection, including local connections. If distributor is local, the password for distributor_admin is set to a new value. For Publishers with a remote Distributor, the same value for @password must be specified when executing sp_adddistributor at both the Publisher and Distributor. sp_changedistributor_password can be used to change the Distributor password.

[ @from_scripting = ] from_scripting

@from_scripting is bit, with a default of 0. Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

[ @encrypt_distributor_connection = ] N'encrypt_distributor_connection'

Applies to: SQL Server 2025 (17.x) Preview and later versions.

Determines whether the internal linked server connection from the publisher to distributor is encrypted. The values are mapped to the OLE DB provider's Encrypt property. @encrypt_distributor_connection is nvarchar(10), and can't be NULL.

@encrypt_distributor_connection can be one of the following values:

• mandatory (default with Microsoft OLE DB provider 19)
• no or false (default with Microsoft OLE DB provider 18)
• true or yes
• optional
• strict

[ @trust_distributor_certificate = ] N'trust_distributor_certificate'

Applies to: SQL Server 2025 (17.x) Preview and later versions.

Indicates whether the distributor's TLS certificate should be trusted without validation. The value is mapped to the OLE DB provider's TrustServerCertificate property, and is typically used in conjunction with the Mandatory encryption setting when using self-signed certificates. @trust_distributor_certificate is nvarchar(5), and can't be NULL.

@trust_distributor_certificate can be one of the following values:

• no (default)
• yes

[ @host_name_in_distributor_certificate = ] N'host_name_in_distributor_certificate'

Applies to: SQL Server 2025 (17.x) Preview and later versions.

Specifies the host name from the Distributor's certificate, when it's different from the Distributor name, such as when the IP address or DNS alias is used as the Distributor name. Leave the @host_name_in_distributor_certificate parameter empty if the host name in the certificate matches the Distributor name. @host_name_in_distributor_certificate is nvarchar(255) of any string value, with a default of NULL.

Return code values

0 (success) or 1 (failure).

Remarks

sp_adddistributor is used in snapshot replication, transactional replication, and merge replication.

Examples

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- Install the Distributor and the distribution database.
DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = $(DistPubServer);
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);
-- Specify the replication working directory.
SET @directory = N'\\' + $(DistPubServer) + '\repldata';
-- Specify the publication database.
SET @publicationDB = N'AdventureWorks2022'; 

-- Install the server MYDISTPUB as a Distributor using the defaults,
-- including autogenerating the distributor password.
USE master
EXEC sp_adddistributor @distributor = @distributor;

-- Create a new distribution database using the defaults, including
-- using Windows Authentication.
USE master
EXEC sp_adddistributiondb @database = @distributionDB, 
    @security_mode = 1;
GO

-- Create a Publisher and enable AdventureWorks2022 for replication.
-- Add MYDISTPUB as a publisher with MYDISTPUB as a local distributor
-- and use Windows Authentication.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);

USE [distribution]
EXEC sp_adddistpublisher @publisher=@publisher, 
    @distribution_db=@distributionDB, 
    @security_mode = 1;
GO

Configure distributor to trust the self-signed certificate

To override the secure default of the OLEDB 19 provider and set trust_distributor_certificate=yes so the distributor trusts the self-signed certificate, use the following example:

EXECUTE sys.sp_adddistributor @trust_distributor_certificate = 'yes';

For more information, review the remote distributor breaking change in SQL Server 2025 Preview.

Permissions

Only members of the sysadmin fixed server role can execute sp_adddistributor.

Related content

• Configure Publishing and Distribution
• sp_changedistributor_property (Transact-SQL)
• sp_dropdistributor (Transact-SQL)
• sp_helpdistributor (Transact-SQL)
• System stored procedures (Transact-SQL)
• Configure Distribution