How to: View and Modify Replication Security Settings (Replication Transact-SQL Programming)

  • Article

The security account settings (logins and passwords) required by replication are defined when publications and subscriptions are created. These settings can be changed later using replication stored procedures. The stored procedures that you use will depend on the type of agent and the type of server connection.

Note

For security reasons, the actual value of passwords are masked in result sets returned by replication stored procedures.

ms147827.security(en-US,SQL.90).gifSecurity Note:
In all of the following procedures, when possible, prompt users to enter security credentials at runtime. If you store credentials in a script file, you must secure the file to prevent unauthorized access.

To change all instances of a stored password at a replication server

  1. At a server in a replication topology on the master database, execute sp_changereplicationserverpasswords. Specify the Microsoft Windows account or Microsoft SQL Server login whose password is being changed for @login and new password for the account or login for @password. This changes every instance of the password used by all agents on the server when connecting to other servers in the topology.

    Note

    To only change the login and password for a connection to a particular server in the topology (such as the Distributor or Subscriber), specify this server's name for @server.

  2. Repeat step 1 at every server in the replication topology where the password must be updated.

    Note

    After changing a replication password, you must stop and restart each agent that uses the password before the change takes effect for that agent.

To change security settings for the Snapshot Agent

  1. At the Publisher, execute sp_helppublication_snapshot, specifying @publication. This returns the current security settings for the Snapshot Agent.

  2. At the Publisher, execute sp_changepublication_snapshot, specifying @publication and one or more of the following security settings to change:

    • To change the Windows account under which the agent runs or just password for this account, specify @job_login and @job_password.
    • To change the security mode used when connecting to the Publisher, specify a value of 1 or 0 for @publisher_security_mode.
    • When changing the security mode used when connecting to the Publisher from 1 to 0 or when changing a SQL Server login used for this connection, specify @publisher_login and @publisher_password.
    ms147827.security(en-US,SQL.90).gifSecurity Note:
     When configuring 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 Encrypting Connections to SQL Server.

To change security settings for the Log Reader Agent

  1. At the Publisher, execute sp_helplogreader_agent, specifying @publisher. This returns the current security settings for the Log Reader Agent.

  2. At the Publisher, execute sp_changelogreader_agent, specifying @publication and one or more of the following security settings to change:

    • To change the Windows account under which the agent runs or just password for this account, specify @job_login and @job_password.
    • To change the security mode used when connecting to the Publisher, specify a value of 1 or 0 for @publisher_security_mode.
    • When changing the security mode used when connecting to the Publisher from 1 to 0 or when changing a SQL Server login used for this connection, specify @publisher_login and @publisher_password.

    Note

    After changing an agent login or password, you must stop and restart the agent before the change takes effect.

    ms147827.security(en-US,SQL.90).gifSecurity Note:
     When configuring 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 Encrypting Connections to SQL Server.

To change security settings for the Distribution Agent for a push subscription

  1. At the Publisher on the publication database, execute sp_helpsubscription, specifying @publication and @subscriber. This returns subscription properties, including security settings for the Distribution Agent running at the Distributor.

  2. At the Publisher on the publication database, execute sp_changesubscription, specifying @publication, @subscriber, @subscriber_db, a value of all for @article, the name of the security property for @property, and the new value of the property for @value.

  3. Repeat step 2 for each of the following security properties being changed:

    • To change the Windows account under which the agent runs or just the password for this account, specify a value of distrib_job_password for @property and a new password for @value. When changing the account itself, repeat step 2 specifying a value of distrib_job_login for @property and the new Windows account for @value.
    • To change the security mode used when connecting to the Subscriber, specify a value of subscriber_security_mode for @property and a value of 1 (Windows Integrated Authentication) or 0 (SQL Server Authentication) for @value.
    • When changing the Subscriber security mode to SQL Server Authentication, or if changing login information for SQL Server Authentication, specify a value of subscriber_password for @property and the new password for @value. Repeat step 2, specifying a value of subscriber_login for @property and the new login for @value.

    Note

    After changing an agent login or password, you must stop and restart the agent before the change takes effect.

    ms147827.security(en-US,SQL.90).gifSecurity Note:
     When configuring a Publisher with a remote Distributor, the values supplied for all properties, including distrib_job_login and distrib_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 Encrypting Connections to SQL Server.

To change security settings for the Distribution Agent for a pull subscription

  1. At the Subscriber, execute sp_helppullsubscription, specifying @publication. This returns subscription properties, including security settings for the Distribution Agent running at the Subscriber.

  2. At the Subscriber on the subscription database, execute sp_change_subscription_properties, specifying @publisher, @publisher_db, @publication, the name of the security property for @property, and the new value of the property for @value.

  3. Repeat step 2 for each of the following security properties being changed:

    • To change the Windows account under which the agent runs or just the password for this account, specify a value of distrib_job_password for @property and a new password for @value. When changing the account itself, repeat step 2 specifying a value of distrib_job_login for @property and the new Windows account for @value.
    • To change the security mode used when connecting to the Distributor, specify a value of distributor_security_mode for @property and a value of 1 (Windows Integrated Authentication) or 0 (SQL Server Authentication) for @value.
    • When changing the Distributor security mode to SQL Server Authentication or if changing login information for SQL Server Authentication, specify a value of distributor_password for @property and the new password for @value. Repeat step 2, specifying a value of distributor_login for @property and the new login for @value.

    Note

    After changing an agent login or password, you must stop and restart the agent before the change takes effect.

To change security settings for the Merge Agent for a push subscription

  1. At the Publisher on the publication database, execute sp_helpmergesubscription, specifying @publication, @subscriber, and @subscriber_db. This returns subscription properties, including security settings for the Merge Agent running at the Distributor.

  2. At the Publisher on the publication database, execute sp_changemergesubscription, specifying @publication, @subscriber, @subscriber_db, the name of the security property for @property, and the new value of the property for @value.

  3. Repeat step 2 for each of the following security properties being changed:

    • To change the Windows account under which the agent runs, or just the password for this account, specify a value of merge_job_password for @property and a new password for @value. When changing the account itself, repeat step 2 specifying a value of merge_job_login for @property and the new Windows account for @value.
    • To change the security mode used when connecting to the Subscriber, specify a value of subscriber_security_mode for @property and a value of 1 (Windows Integrated Authentication) or 0 (SQL Server Authentication) for @value.
    • When changing the Subscriber security mode to SQL Server Authentication, or if changing login information for SQL Server Authentication, specify a value of subscriber_password for @property and the new password for @value. Repeat step 2, specifying a value of subscriber_login for @property and the new login for @value.
    • To change the security mode used when connecting to the Publisher, specify a value of publisher_security_mode for @property and a value of 1 (Windows Integrated Authentication) or 0 (SQL Server Authentication) for @value.
    • When changing the Publisher security mode to SQL Server Authentication, or if changing login information for SQL Server Authentication, specify a value of publisher_password for @property and the new password for @value. Repeat step 2, specifying a value of publisher_login for @property and the new login for @value.

    Note

    After changing an agent login or password, you must stop and restart the agent before the change takes effect.

    ms147827.security(en-US,SQL.90).gifSecurity Note:
     When configuring a Publisher with a remote Distributor, the values supplied for all properties, including merge_job_login and merge_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 Encrypting Connections to SQL Server.

To change security settings for the Merge Agent for a pull subscription

  1. At the Subscriber, execute sp_helpmergepullsubscription, specifying @publication. This returns subscription properties, including security settings for the Merge Agent running at the Subscriber.

  2. At the Subscriber on the subscription database, execute sp_change_subscription_properties, specifying @publisher, @publisher_db, @publication, the name of the security property for @property, and the new value of the property for @value.

  3. Repeat step 2 for each of the following security properties being changed:

    • To change the Windows account under which the agent runs or just the password for this account, specify a value of merge_job_password for @property and new password for @value. When changing the account itself, repeat Step 2 specifying a value of merge_job_login for @property and the new Windows account for @value.
    • To change the security mode used when connecting to the Distributor, specify a value of distributor_security_mode for @property and a value of 1 (Windows Integrated Authentication) or 0 (SQL Server Authentication) for @value.
    • When changing the Distributor security mode to SQL Server Authentication or if changing login information for SQL Server Authentication, specify a value of distributor_password for @property and the new password for @value. Repeat step 2, specifying a value of distributor_login for @property and the new login for @value.
    • To change the security mode used when connecting to the Publisher, specify a value of publisher_security_mode for @property and a value of 1 (Windows Integrated Authentication) or 0 (SQL Server Authentication) for @value.
    • When changing the Publisher security mode to SQL Server Authentication or if changing login information for SQL Server Authentication, specify a value of publisher_password for @property and the new password for @value. Repeat step 2, specifying a value of publisher_login for @property and the new login for @value.

    Note

    After changing an agent login or password, you must stop and restart the agent before the change takes effect.

To change security settings for the Snapshot Agent to generate a filtered snapshot for a Subscriber

  1. At the Publisher, execute sp_helpdynamicsnapshot_job, specifying @publication. In the result set, note the value of job_name for the Subscriber's partition to change.

  2. At the Publisher, execute sp_changedynamicsnapshot_job, specifying @publication, the value obtained from step 1 for @dynamic_snapshot_jobname, and a new password for @job_password or login and password for the Windows account under which the agent runs for @job_login and @job_password.

    ms147827.security(en-US,SQL.90).gifSecurity Note:
     When configuring 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 Encrypting Connections to SQL Server.

To change security settings for the Queue Reader Agent

  1. At the Distributor, execute sp_helpqreader_agent. This returns the current Windows account under which the Queue Reader Agent runs.

    • At the Distributor, execute sp_changeqreader_agent, specifying the Windows account settings for @job_login and @job_passwsord.

    Note

    After changing an agent login or password, you must stop and restart the agent before the change takes effect. There is one Queue Reader Agent for each distribution database. Changing the security settings for the agent affects the settings for all publications at all Publishers that use this distribution database.

  2. The Queue Reader Agent makes connections to the Subscriber using the same connection context as the Distribution Agent for the subscription.

To change security mode used by an immediate updating Subscriber when connecting to the Publisher

  1. At the Subscriber on the subscription database, execute sp_link_publication. Specify @publisher, @publication, the name of the publication database for @publisher_db, and one of the following values for @security_mode:

    • 0 to use SQL Server Authentication when making updates at the Publisher. This option requires you to specify a valid login at the Publisher for @login and @password.
    • 1 to use the security context of the user making changes at the Subscriber when connecting to the Publisher. See sp_link_publication for restrictions related to this security mode.
    • 2 to use an existing, user-defined linked server login created using sp_addlinkedserver (Transact-SQL).

To change the password for a remote Distributor

  1. At the Distributor on the distribution database, execute sp_changedistributor_password, specifying the new password for this login for @password.

    Important

    Do not change the password for distributor_admin directly.

  2. At every Publisher that uses this remote Distributor, execute sp_changedistributor_password, specifying the password from step 1 for @password.

See Also

Tasks

How to: Upgrade Replication Scripts (Replication Transact-SQL Programming)

Concepts

Programming Replication Using System Stored Procedures

Other Resources

How to: View and Modify Replication Security Settings (SQL Server Management Studio)
Managing Logins and Passwords in Replication
Replication Agent Security Model
Security Considerations for Replication

Help and Information

Getting SQL Server 2005 Assistance