View and Modify Replication Security Settings

  • Makale

This topic describes how to view and modify replication security settings in SQL Server 2014 by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO). For example, you might want to change the connection of the Log Reader Agent to the Publisher from SQL Server Authentication to Windows Integrated Authentication, or you might need to change the credentials used to run an agent job when the Windows account password has changed. For information about the permissions required by each agent, see Replication Agent Security Model.

In This Topic

Before You Begin

Limitations and Restrictions

  • The stored procedures that you use will depend on the type of agent and the type of server connection.

  • The RMO classes and properties you use depend on the type of agent and the type of server connection.

Security

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

Permissions

Using SQL Server Management Studio

View and modify security settings in the following dialog boxes:

  1. The Update Replication Passwords dialog box, which is available from the Replication folder of SQL Server Management Studio. If you change the password for a SQL Server account or Windows account on a server in a replication topology, use this dialog box rather than updating the password for each agent that uses the account. If agents on more than one server use the same account, you must connect to each server and change the password. The password is updated in all of the places that replication uses the password. The password is not updated in other places, such as linked servers.

  2. The Agent Security page of the Publication Properties - <Publication> dialog box. For more information about accessing this dialog box, see View and Modify Publication Properties.

  3. The Subscription Properties - <Subscription> dialog box. For more information about accessing this dialog box, see View and Modify Push Subscription Properties and View and Modify Pull Subscription Properties.

  4. The Distributor Properties - <Distributor> and Distribution Database Properties - <Database> dialog boxes. For more information about accessing these dialog boxes, see View and Modify Distributor and Publisher Properties.

  5. The Publisher Properties - <Publisher> dialog box. For more information about accessing this dialog box, View and Modify Distributor and Publisher Properties.

To change the password for an account used by one or more agents

  1. If the account is a SQL Server account, this dialog box will also change the password for the SQL Server account. If the account is a Windows account, change the password in Windows first. For more information, see the Windows documentation.

    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.

  2. Connect to the server in SQL Server Management Studio, and then expand the server node.

  3. Right-click the Replication folder, and then click Update Replication Passwords.

  4. In the Update Replication Passwords dialog box, specify the account and the new password.

  5. Click OK.

To change security settings for the Snapshot Agent

  1. On the Agent Security page of the Publication Properties - <Publication> dialog box, click the Security Settings button next to the Snapshot Agent text box.

  2. In the Snapshot Agent Security dialog box, specify the account under which the agent should run:

    • Enter a new Windows account in the Agent account text box.

    • Enter a new strong password in the Password and Confirm Password text boxes.

  3. Specify the context under which the agent should connect from the Distributor to the Publisher. If you select Using the following SQL Server login, you must also specify the login:

    • Enter a login in the Login text box

    • Enter a new strong password in the Password and Confirm Password text boxes.

    Note

    If the Publisher is an Oracle Publisher, the connection context is specified in the **Distributor Properties - <Distributor>**dialog box. See below for the procedure to change the context.

  4. Click OK.

To change security settings for the Log Reader Agent

  1. On the Agent Security page of the Publication Properties - <Publication> dialog box, click the Security Settings button next to the Log Reader Agent text box.

  2. In the Log Reader Agent Security dialog box, specify the account under which the agent should run:

    • Enter a new Windows account in the Agent account text box

    • Enter a new strong password in the Password and Confirm Password text boxes.

  3. Specify the context under which the agent should connect from the Distributor to the Publisher. If you select Using the following SQL Server login, you must also specify the login:

    • Enter a login in the Login text box

    • Enter a new strong password in the Password and Confirm Password text boxes.

    Note

    If the Publisher is an Oracle Publisher, the connection context is specified in the **Distributor Properties - <Distributor>**dialog box. Change the context using the next procedure.

  4. Click OK.

    Note

    There is one Log Reader Agent for each published database. Changing the security settings for the agent on one publication affects the settings for all publications in the publication database.

To change the context under which the Snapshot Agent and Log Reader Agent for an Oracle publication make connections to the Publisher

  1. On the Publishers page of the Distributor Properties - <Distributor> dialog box, click the properties button (...) next to a Publisher.

  2. In the Agent Connection to the Publisher section, specify the login and password used by the replication administrative user schema you configured. For more information, see Configure an Oracle Publisher.

  3. Click OK.

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

  1. In the Subscription Properties - <Subscription> dialog box at the Publisher, you can make the following changes:

    • To change the account under which the Distribution Agent runs and makes connections to the Distributor, click the Agent process account row, and then click the properties (...) button in the row. Specify an account and password in the Distribution Agent Security dialog box.

    • To change the context under which the Distribution Agent connects to the Subscriber, click the Subscriber Connection row, and then click the properties (...) button in the row. Specify the context in the Enter Connection Information dialog box.

      If you use queued updating subscriptions, the Queue Reader Agent also uses the context specified here for connections to the Subscriber.

  2. Click OK.

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

  1. In the Subscription Properties - <Subscription> dialog box at the Subscriber, you can make the following changes:

    • To change the account under which the Distribution Agent runs and makes connections to the Subscriber, click the Agent process account row, and then click the properties (...) button in the row. Specify an account and password in the Distribution Agent Security dialog box.

      If you use queued updating subscriptions, the Queue Reader Agent also uses the context specified here for connections to the Subscriber.

    • To change the context under which the Distribution Agent connects to the Distributor, click the Distributor Connection row, and then click the properties (...) button in the row. Specify the context in the Enter Connection Information dialog box.

  2. Click OK.

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

  1. In the Subscription Properties - <Subscription> dialog box at the Publisher, you can make the following changes:

    • To change the account under which the Merge Agent runs and makes connections to the Publisher and Distributor, click the Agent process account row, and then click the properties (...) button in the row. Specify an account and password in the Merge Agent Security dialog box.

    • To change the context under which the Merge Agent connects to the Subscriber, click the Subscriber Connection row, and then click the properties (...) button in the row. Specify the context in the Enter Connection Information dialog box.

  2. Click OK.

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

  1. In the Subscription Properties - <Subscription> dialog box at the Subscriber, you can make the following changes:

    • To change the account under which the Merge Agent runs and makes connections to the Subscriber, click the Agent process account row, and then click the properties (...) button in the row. Specify an account and password in the Merge Agent Security dialog box.

    • To change the context under which the Merge Agent connects to the Publisher and Distributor, click the Publisher Connection row, and then click the properties (...) button in the row. Specify the context in the Enter Connection Information dialog box.

  2. Click OK.

To change the account under which the Queue Reader Agent runs

  1. On the General page of the Distributor Properties - <Distributor> dialog box, click the properties (...) button next to the distribution database.

  2. In the Distribution Database Properties - <Database> dialog box, click the Security Settings button next to the Agent process account text box.

  3. In the Queue Reader Agent Security dialog box, specify the account under which the agent runs and makes connections to the Distributor:

    • Enter a new Windows account in the Process account text box

    • Enter a new strong password in the Password and Confirm Password text boxes.

  4. Click OK.

    Note

    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.

To change the context under which the Queue Reader Agent makes connections to the Publisher

  1. On the Publishers page of the Distributor Properties - <Distributor> dialog box, click the properties button (...) next to the Publisher.

  2. In the Agent Connection to the Publisher section, specify a value of Impersonate the agent process account or SQL Server Authentication for the Agent Connection Mode option. If you specify SQL Server Authentication, also enter values for Login and Password.

  3. Click OK.

    Note

    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.

To change the context under which the Queue Reader Agent makes connections to the Subscriber

  • The Queue Reader Agent uses the same connection context as the Distribution Agent for the subscription. For more information, see the procedures above for the Distribution Agent.

To change security settings for an immediate updating pull subscription

  1. In the Subscription Properties - <Subscription> dialog box at the Subscriber, click the Publisher Connection row, and then click the properties (...) button in the row.

  2. In the Enter Connection Information dialog box, select one of the following options:

    • Use a login from a linked or remote server. Select this option if you have defined a remote server or linked server between the Subscriber and the Publisher using sp_addserver (Transact-SQL), sp_addlinkedserver (Transact-SQL), SQL Server Management Studio, or another method.

    • Use SQL Server Authentication with the following login and password. Select this option if you have not defined a remote server or linked server between the Subscriber and the Publisher. Replication will create a linked server for you. The account you specify must already exist at the Publisher.

  3. Click OK.

Note

This procedure changes the method that replication triggers use to connect from the Subscriber to the Publisher when changes are made at the Subscriber. You can also change settings associated with the Distribution Agent for an immediate updating subscription. For more information, see the procedures earlier in this topic.

This procedure applies only to pull subscriptions. For push subscriptions, use the stored procedure sp_link_publication (Transact-SQL).

To change the password for the administrative connection from the Publisher to the Distributor

  1. On the Publishers page of the Distributor Properties - <Distributor> dialog box, enter a strong password in the Password and Confirm Password text boxes.

  2. Click OK.

  3. On the General page of the Publisher Properties - <Publisher> dialog box, enter a strong password in the Password and Confirm Password text boxes.

  4. Click OK.

Using Transact-SQL

Important

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.

    Important

    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 Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

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.

    Important

    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 Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

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.

    Important

    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 Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

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.

    Important

    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 Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

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.

    Important

    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 Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

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.

Using Replication Management Objects (RMO)

Important

When possible, prompt users to enter security credentials at runtime. If you must store credentials, use the cryptographic services provided by the Microsoft Windows .NET Framework.

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

  1. Create a connection to the replication server by using the ServerConnection class.

  2. Create an instance of the ReplicationServer class by using the connection from step 1.

  3. Call the ChangeReplicationServerPasswords method. Specify the following parameters:

    • security_mode - a ReplicationSecurityMode value that specifies the type of authentication for which all instances of the password are being changed.

    • login - the login for which all instances of the password are being changed.

    • password - the new password value.

      Important

      When possible, prompt users to enter security credentials at runtime. If you must store credentials, use the cryptographic services provided by the Windows .NET Framework.

      Note

      Only a member of the sysadmin fixed server role can call this method.

  4. Repeat steps 1-3 at every server in the replication topology where the password must be updated.

To change security settings for the Distribution Agent for a push subscription to a transactional publication

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the TransSubscription class.

  3. Set the PublicationName, DatabaseName, SubscriberName, and SubscriptionDBName properties for the subscription, and set the connection from step 1 for the ConnectionContext property.

  4. Call the LoadProperties method to get the properties of the object. If this method returns false, either the subscription properties in step 3 were defined incorrectly or the subscription does not exist.

  5. Set one or more of the following security properties on the instance of TransSubscription:

  6. (Optional) If you specified a value of true for CachePropertyChanges, call the CommitPropertyChanges method to commit changes on the server. If you specified a value of false for CachePropertyChanges (the default), changes are sent to the server immediately.

To change security settings for the Distribution Agent for a pull subscription to a transactional publication

  1. Create a connection to the Subscriber by using the ServerConnection class.

  2. Create an instance of the TransPullSubscription class.

  3. Set the PublicationName, DatabaseName, PublisherName, and PublicationDBName properties for the subscription, and set the connection from step 1 for the ConnectionContext property.

  4. Call the LoadProperties method to get the properties of the object. If this method returns false, either the subscription properties in step 3 were defined incorrectly or the subscription does not exist.

  5. Set one or more of the following security properties on the instance of TransPullSubscription:

  6. (Optional) If you specified a value of true for CachePropertyChanges, call the CommitPropertyChanges method to commit changes on the server. If you specified a value of false for CachePropertyChanges (the default), changes are sent to the server immediately.

To change security settings for the Merge Agent for a pull subscription to a merge publication

  1. Create a connection to the Subscriber by using the ServerConnection class.

  2. Create an instance of the MergePullSubscription class.

  3. Set the PublicationName, DatabaseName, PublisherName, and PublicationDBName properties for the subscription, and set the connection from step 1 for the ConnectionContext property.

  4. Call the LoadProperties method to get the properties of the object. If this method returns false, either the subscription properties in step 3 were defined incorrectly or the subscription does not exist.

  5. Set one or more of the following security properties on the instance of MergePullSubscription:

  6. (Optional) If you specified a value of true for CachePropertyChanges, call the CommitPropertyChanges method to commit changes on the server. If you specified a value of false for CachePropertyChanges (the default), changes are sent to the server immediately.

To change security settings for the Merge Agent for a push subscription to a merge publication

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the MergeSubscription class.

  3. Set the PublicationName, DatabaseName, SubscriberName, and SubscriptionDBName properties for the subscription, and set the connection from step 1 for the ConnectionContext property.

  4. Call the LoadProperties method to get the properties of the object. If this method returns false, either the subscription properties in step 3 were defined incorrectly or the subscription does not exist.

  5. Set one or more of the following security properties on the instance of MergeSubscription:

  6. (Optional) If you specified a value of true for CachePropertyChanges, call the CommitPropertyChanges method to commit changes on the server. If you specified a value of false for CachePropertyChanges (the default), changes are sent to the server immediately.

To change the login information used by an immediate updating Subscriber when it connects to the transactional publisher

  1. Create a connection to the Subscriber by using the ServerConnection class.

  2. Create an instance of the ReplicationDatabase class for the subscription database. Specify Name and the ServerConnection from step 1 for ConnectionContext.

  3. Call the LoadProperties method to get the properties of the object. If this method returns false, either the database properties in step 2 were defined incorrectly or the subscription database does not exist.

  4. Call the LinkPublicationForUpdateableSubscription method, passing the following parameters:

    • Publisher - the name of the Publisher.

    • PublisherDB - the name of the publication database.

    • Publication - the name of the publication to which the immediate updating Subscriber is subscribed.

    • Distributor - the name of the Distributor.

    • PublisherSecurity - A PublisherConnectionSecurityContext object that specifies the type of security mode used by the immediate updating Subscriber when connecting to the Publisher and login credentials for the connection.

Example (RMO)

This example checks the supplied login value and changes all passwords for the supplied Windows login or SQL Server login stored by replication on the server.

// Set the Distributor and distribution database names.
string serverName = publisherInstance;

ReplicationServer server;

// Create a connection to the Distributor using Windows Authentication.
ServerConnection conn = new ServerConnection(serverName);

try
{
    // Open the connection. 
    conn.Connect();

    server = new ReplicationServer(conn);

    // Load server properties, if it exists.
    if (server.LoadProperties())
    {
        string[] slash = new string[1];
        slash[1] = @"\";

        // If the login is in the form string\string, assume we are 
        // changing the password for a Windows login.
        if (login.Split(slash, StringSplitOptions.None).Length == 2)
        {
            //Change the password for the all connections that use
            // the Windows login. 
            server.ChangeReplicationServerPasswords(
                    ReplicationSecurityMode.Integrated, login, password);
        }
        else
        {
            // Change the password for the all connections that use
            // the SQL Server login. 
            server.ChangeReplicationServerPasswords(
                    ReplicationSecurityMode.SqlStandard, login, password);
        }
    }
    else
    {
        throw new ApplicationException(String.Format(
            "Properties for {0} could not be retrieved.", publisherInstance));
    }
}
catch (Exception ex)
{
    // Implement the appropriate error handling here. 
    throw new ApplicationException(String.Format(
        "An error occured when changing agent login " +
        " credentials on {0}.",serverName), ex);
}
finally
{
    conn.Disconnect();
}

' Set the Distributor and distribution database names.
Dim serverName As String = publisherInstance

Dim server As ReplicationServer

' Create a connection to the Distributor using Windows Authentication.
Dim conn As ServerConnection = New ServerConnection(serverName)

Try
    ' Open the connection. 
    conn.Connect()

    server = New ReplicationServer(conn)

    ' Load server properties, if it exists.
    If server.LoadProperties() Then

        ' If the login is in the form string\string, assume we are 
        ' changing the password for a Windows login.
        If login.Split("\").Length = 2 Then

            ' Change the password for the all connections that use
            ' the Windows login. 
            server.ChangeReplicationServerPasswords( _
            ReplicationSecurityMode.Integrated, login, password)
        Else

            ' Change the password for the all connections that use
            ' the SQL Server login. 
            server.ChangeReplicationServerPasswords( _
            ReplicationSecurityMode.SqlStandard, login, password)
        End If
    Else
        Throw New ApplicationException(String.Format( _
         "Properties for {0} could not be retrieved.", publisherInstance))
    End If
Catch ex As Exception
    ' Implement the appropriate error handling here. 
    Throw New ApplicationException(String.Format( _
     "An error occured when changing agent login " + _
     " credentials on {0}.", serverName), ex)
Finally
    conn.Disconnect()
End Try

Follow Up: After you modify replication security settings

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

See Also

Replication Management Objects Concepts
Upgrade Replication Scripts (Replication Transact-SQL Programming)
Manage Logins and Passwords in Replication
Replication Agent Security Model
Replication Security Best Practices
SQL Server Replication Security
Replication System Stored Procedures Concepts