How to: View and Modify Replication Security Settings (SQL Server Management Studio)

The security settings used by a replication agent when connecting to the various servers in a replication topology are defined when publications and subscriptions are created. These settings can be changed after creation in Microsoft SQL Server Management Studio. 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.

Note

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

View and modify security settings in the following dialog boxes:

• 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.
• The Agent Security page of the Publication Properties - <Publication> dialog box. For more information about accessing this dialog box, see How to: View and Modify Publication and Article Properties (SQL Server Management Studio).
• The Subscription Properties - <Subscription> dialog box. For more information about accessing this dialog box, see How to: View and Modify Push Subscription Properties (SQL Server Management Studio) and How to: View and Modify Pull Subscription Properties (SQL Server Management Studio).
• The Distributor Properties - <Distributor> and Distribution Database Properties - <Database> dialog boxes. For more information about accessing these dialog boxes, see How to: View and Modify Distributor Properties (SQL Server Management Studio).
• The Publisher Properties - <Publisher> dialog box. For more information about accessing this dialog box, How to: View and Modify Publisher Properties (SQL Server Management Studio).

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 Configuring 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.

   For information about the permissions required by the linked server account, see the "Immediate Updating Subscriptions" section of Security Considerations for Updating Subscriptions.

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). For more information, see How to: View and Modify Replication Security Settings (Replication Transact-SQL Programming).

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.

See Also

Concepts

Replication Agent Security Model
Replication Security Best Practices

Other Resources

How to: View and Modify Replication Security Settings (Replication Transact-SQL Programming)
Managing Logins and Passwords in Replication
Security Considerations for Replication

Help and Information

Getting SQL Server 2005 Assistance