Export (0) Print
Expand All

How to: View and Modify Subscription Security Settings (RMO Programming)

The security account settings (logins and passwords) required by replication are defined when publications and subscriptions are created. You can change these settings later by using Replication Management Objects (RMO). The RMO classes and properties you use depend on the type of agent and the type of server connection.

ms147345.security(en-US,SQL.90).gifSecurity Note:
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.

  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.
      ms147345.security(en-US,SQL.90).gifSecurity Note:
      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.

      ms147345.note(en-US,SQL.90).gifNote:
      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.

  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:

    • To change the credentials for the Windows account under which the agent runs, set the Login and Password fields of SynchronizationAgentProcessSecurity.
    • To specify Windows Integrated Authentication as the type of authentication that the agent uses when it connects to the Subscriber, set the WindowsAuthentication field of the SubscriberSecurity property to true.
    • To specify SQL Server Authentication as the type of authentication that the agent uses when it connects to the Subscriber, set the WindowsAuthentication field of the SubscriberSecurity property to false, and specify the Subscriber login credentials for the SqlStandardLogin and SqlStandardPassword fields.
      ms147345.note(en-US,SQL.90).gifNote:
      The agent connection to the Distributor is always made using the Windows credentials specified by SynchronizationAgentProcessSecurity. This account is also used to make remote connections using Windows Authentication.

  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.

  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:

    • To change the credentials for the Windows account under which the agent runs, set the Login and Password fields of SynchronizationAgentProcessSecurity.
    • To specify Windows Integrated Authentication as the type of authentication that the agent uses when it connects to the Distributor, set the WindowsAuthentication field of the DistributorSecurity property to true.
    • To specify SQL Server Authentication as the type of authentication that the agent uses when it connects to the Distributor, set the WindowsAuthentication field of the DistributorSecurity property to false, and specify the Distributor login credentials for the SqlStandardLogin and SqlStandardPassword fields.
      ms147345.note(en-US,SQL.90).gifNote:
      The agent connection to the Subscriber is always made using the Windows credentials specified by SynchronizationAgentProcessSecurity. This account is also used to make remote connections using Windows Authentication.

  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.

  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:

    • To change the credentials for the Windows account under which the agent runs, set the Login and Password fields of SynchronizationAgentProcessSecurity.
    • To specify Windows Integrated Authentication as the type of authentication that the agent uses when it connects to the Distributor, set the WindowsAuthentication field of the DistributorSecurity property to true.
    • To specify SQL Server Authentication as the type of authentication that the agent uses when it connects to the Distributor, set the WindowsAuthentication field of the DistributorSecurity property to false, and specify the Distributor login credentials for the SqlStandardLogin and SqlStandardPassword fields.
    • To specify Windows Integrated Authentication as the type of authentication that the agent uses when it connects to the Publisher, set the WindowsAuthentication field of the PublisherSecurity property to true.
    • To specify SQL Server Authentication as the type of authentication that the agent uses when it connects to the Publisher, set the WindowsAuthentication field of the PublisherSecurity property to false, and specify the Publisher login credentials for the SqlStandardLogin and SqlStandardPassword fields.
      ms147345.note(en-US,SQL.90).gifNote:
      The agent connection to the Subscriber is always made using the Windows credentials specified by SynchronizationAgentProcessSecurity. This account is also used to make remote connections using Windows Authentication.

  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.

  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:

    • To change the credentials for the Windows account under which the agent runs, set the Login and Password fields of SynchronizationAgentProcessSecurity.
    • To specify Windows Integrated Authentication as the type of authentication that the agent uses when it connects to the Subscriber, set the WindowsAuthentication field of the SubscriberSecurity property to true.
    • To specify SQL Server Authentication as the type of authentication that the agent uses when it connects to the Subscriber, set the WindowsAuthentication field of the SubscriberSecurity property to false, and specify the Subscriber login credentials for the SqlStandardLogin and SqlStandardPassword fields.
    • To specify Windows Integrated Authentication as the type of authentication that the agent uses when it connects to the Publisher, set the WindowsAuthentication field of the PublisherSecurity property to true.
    • To specify SQL Server Authentication as the type of authentication that the agent uses when it connects to the Publisher, set the WindowsAuthentication field of the PublisherSecurity property to false, and specify the Publisher login credentials for the SqlStandardLogin and SqlStandardPassword fields.
      ms147345.note(en-US,SQL.90).gifNote:
      The agent connection to the Distributor is always made using the Windows credentials specified by SynchronizationAgentProcessSecurity. This account is also used to make remote connections using Windows Authentication.

  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.

  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.

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();
}

Community Additions

ADD
Show:
© 2014 Microsoft