Skip to content

Latest commit

 

History

History
107 lines (72 loc) · 5.81 KB

sp-link-publication-transact-sql.md

File metadata and controls

107 lines (72 loc) · 5.81 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_link_publication (Transact-SQL)
Sets the configuration and security information used by synchronization triggers of immediate updating subscriptions when connecting to the Publisher.
markingmyname
maghan
randolphwest
08/22/2024
sql
replication
reference
sp_link_publication_TSQL
sp_link_publication
sp_link_publication
TSQL

sp_link_publication (Transact-SQL)

[!INCLUDE 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.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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. [!INCLUDE ssNoteWinAuthentication]

Value Description
0 Uses [!INCLUDE ssNoVersion] Authentication with the login specified in this stored procedure as @login and @password.

Note: In previous versions of [!INCLUDE ssNoVersion], this option was used to specify a dynamic remote procedure call (RPC).
1 Uses the security context ([!INCLUDE ssNoVersion] 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

:::code language="sql" source="../replication/codesnippet/tsql/sp-link-publication-tran_1.sql":::

Permissions

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

Related content