Edit

Share via

sp_changedistributor_property (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance

Changes the properties of the Distributor. This stored procedure is executed at the Distributor on any database. For remote Distributors, this stored procedure needs to be executed on all the Publisher servers that connect to the remote Distributor.

If the distribution or Publisher database is in an availability group, the stored procedure needs to be executed on all Distributor and Publisher nodes, regardless of their current role in the availability group.

Transact-SQL syntax conventions

Syntax

sp_changedistributor_property
    [ [ @property = ] N'property' ]
    [ , [ @value = ] N'value' ]
[ ; ]

Arguments

[ @property = ] N'property'

The property for a given Distributor. @property is sysname, and can be one of these values:

Property name Acceptable values Description
heartbeat_interval Any int value (in minutes) Maximum number of minutes that an agent can run without logging a progress message. @heartbeat_interval is int, with a default of 10 minutes.
encrypt_distributor_connection mandatory, optional, strict, true, false, yes, no Specifies the encryption type between the Distributor and other replication components.

Applies to: SQL Server 2025 (17.x) Preview and later versions.
trust_distributor_certificate yes, no Specifies whether to trust the certificate used by the Distributor for encrypted connections. The default is no.

Applies to: SQL Server 2025 (17.x) Preview and later versions.
host_name_in_distributor_certificate Any string Specifies the expected host name in the Distributor's certificate.

Applies to: SQL Server 2025 (17.x) Preview and later versions.
NULL (default) All available @property values are printed.

Note

Secure defaults pertain to the underlying OLEDB provider 19, which enhances security. The option to override the default is less secure than configuring your instance to use a trusted certificate. After overriding the default, you have the option to configure SQL Server to use a certificate, and then use the sp_changedistributor_property stored procedure to set the trust_distributor_certificate=no property back to the secure default.

[ @value = ] N'value'

The value for the given Distributor property. @value is nvarchar(255), with a default of NULL.

Return code values

0 (success) or 1 (failure).

Remarks

sp_changedistributor_property is used in all types of replication.

Examples


-- Change the heartbeat interval at the Distributor to 5 minutes. 
USE master 
exec sp_changedistributor_property 
    @property = N'heartbeat_interval', 
    @value = 5;
GO

Configure distributor to trust the self-signed certificate

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

EXECUTE sp_changedistributor_property
    @property = N'trust_distributor_certificate',
    @value = N'yes';

Note

Secure defaults pertain to the underlying OLEDB provider 19, which enhances security. The option to override the default is less secure than configuring your instance to use a trusted certificate. After overriding the default, you have the option to configure SQL Server to use a certificate, and then use the sp_changedistributor_property stored procedure to set the trust_distributor_certificate=no property back to the secure default.

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

Related content