sp_link_publication (Transact-SQL)
Applies to: 
SQL Server
Sets the configuration and security information used by synchronization triggers of immediate updating subscriptions when connecting to the Publisher. This stored procedure is executed at the Subscriber on the subscription database.
Important
When you configure a Publisher with a remote Distributor, the values supplied for all parameters, including @job_login and @job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Configure SQL Server Database Engine for encrypting connections.
Transact-SQL syntax conventions
Syntax
sp_link_publication
[ @publisher = ] N'publisher'
, [ @publisher_db = ] N'publisher_db'
, [ @publication = ] N'publication'
, [ @security_mode = ] security_mode
[ , [ @login = ] N'login' ]
[ , [ @password = ] N'password' ]
[ , [ @distributor = ] N'distributor' ]
[ ; ]
Arguments
[ @publisher = ] N'publisher'
The name of the Publisher to link to. @publisher is sysname, with no default.
[ @publisher_db = ] N'publisher_db'
The name of the Publisher database to link to. @publisher_db is sysname, with no default.
[ @publication = ] N'publication'
The name of the publication to link to. @publication is sysname, with no default.
[ @security_mode = ] security_mode
The security mode used by the Subscriber to connect to a remote Publisher for immediate updating. @security_mode is int, and can be one of these values. When possible, use Windows authentication.
| Value | Description |
|---|---|
0 |
Uses SQL Server Authentication with the login specified in this stored procedure as @login and @password. Note: In previous versions of SQL Server, this option was used to specify a dynamic remote procedure call (RPC). |
1 |
Uses the security context (SQL Server Authentication or Windows Authentication) of the user making the change at the Subscriber. Note: This account must also exist at the Publisher with sufficient privileges. When you use Windows Authentication, security account delegation must be supported. |
2 |
Uses an existing, user-defined linked server login created using sp_link_publication. |
[ @login = ] N'login'
The login. @login is sysname, with a default of NULL. This parameter must be specified when @security_mode is 0.
[ @password = ] N'password'
The password. @password is sysname, with a default of NULL. This parameter must be specified when @security_mode is 0.
[ @distributor = ] N'distributor'
The name of the Distributor. @distributor is sysname, with a default of an empty string.
Return code values
0 (success) or 1 (failure).
Remarks
sp_link_publication is used by immediate updating subscriptions in transactional replication.
sp_link_publication can be used for both push and pull subscriptions. It can be called before or after the subscription is created. An entry is inserted or updated in the MSsubscription_properties system table.
For push subscriptions, the entry can be cleaned up by sp_subscription_cleanup. For pull subscriptions, the entry can be cleaned up by sp_droppullsubscription or sp_subscription_cleanup. You can also call sp_link_publication with a NULL password to clear the entry in the MSsubscription_properties system table for security concerns.
The default mode used by an immediate updating Subscriber when it connects to the Publisher doesn't allow a connection using Windows Authentication. To connect with a mode of Windows Authentication, a linked server has to be set up to the Publisher, and the immediate updating Subscriber should use this connection when updating the Subscriber. This requires the sp_link_publication to be run with @security_mode set to 2. When you use Windows Authentication, security account delegation must be supported.
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".
-- Execute this batch at the Subscriber.
DECLARE @publication AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS nvarchar(512);
SET @publication = N'AdvWorksProductTran';
SET @publicationDB = N'AdventureWorks2022';
SET @publisher = $(PubServer);
SET @login = $(Login);
SET @password = $(Password);
-- At the subscription database, create a pull subscription to a transactional
-- publication using immediate updating with queued updating as a failover.
EXEC sp_addpullsubscription
@publisher = @publisher,
@publication = @publication,
@publisher_db = @publicationDB,
@update_mode = N'failover',
@subscription_type = N'pull';
-- Add an agent job to synchronize the pull subscription,
-- which uses Windows Authentication when connecting to the Distributor.
EXEC sp_addpullsubscription_agent
@publisher = @publisher,
@publisher_db = @publicationDB,
@publication = @publication,
@job_login = @login,
@job_password = @password;
-- Add a Windows Authentication-based linked server that enables the
-- Subscriber-side triggers to make updates at the Publisher.
EXEC sp_link_publication
@publisher = @publisher,
@publication = @publication,
@publisher_db = @publicationDB,
@security_mode = 0,
@login = @login,
@password = @password;
GO
USE AdventureWorks2022;
GO
-- Execute this batch at the Publisher.
DECLARE @publication AS sysname;
DECLARE @subscriptionDB AS sysname;
DECLARE @subscriber AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriptionDB = N'AdventureWorks2022Replica';
SET @subscriber = $(SubServer);
-- At the Publisher, register the subscription, using the defaults.
USE [AdventureWorks2022]
EXEC sp_addsubscription
@publication = @publication,
@subscriber = @subscriber,
@destination_db = @subscriptionDB,
@subscription_type = N'pull',
@update_mode = N'failover';
GO
Permissions
Only members of the sysadmin fixed server role can execute sp_link_publication.