Changing the SQL Server Agent logon account
If the Publisher has a Subscriber that uses Windows authentication, the SQL Server Agent logon account on the Publisher SQL Server must have the required access permissions on the Subscriber SQL Server. For more information, see SQL Server permissions in the Active Roles Installation Guide.
Because of these requirements of the SQL Server Agent logon account, there might be a case where you have to specify a different logon account with sufficient access permissions. You might also have to change the password of the logon account. This section provides instructions on how to change the SQL Server Agent logon account.
To specify the name and password of the SQL Server Agent logon account by using SQL Server Configuration Manager
-
On the computer running the Publisher SQL Server, open SQL Server Configuration Manager.
-
In the Console tree, select SQL Server Services.
-
In the Details pane, right-click the SQL Server Agent to modify, and then click Properties.
-
On the Log On tab, click This account, and specify the account name and password.
-
Click OK.
-
For the changes to take effect, click Yes in the confirmation message box.
Modifying Replication Agent credentials
This section provides information on how to repair Active Roles replication if it fails due to insufficient permissions of Replication Agents. The credentials that are used by Replication Agents to access a given SQL Server depend on authentication mode of the Administration Service connection to that SQL Server:
-
Windows authentication: In this mode, Replication Agents use the credentials of the SQL Server Agent service that is running on the PublisherSQL Server computer.
-
SQL Server authentication: In this mode, Replication Agents use the credentials of the SQL Server login that is specified for the Administration Service connection to SQL Server.
The following sections describe these two options.
Windows authentication
If the Administration Service uses Windows authentication, Replication Agents connect to SQL Server in the security context of the SQL Server Agent service. Therefore, the SQL Server Agent logon account must have sufficient permissions for replication to work properly. For more information, see SQL Server permissions in the Active Roles Installation Guide.
If the SQL Server Agent logon account does not have the appropriate permissions, is deleted, or has the password changed, Active Roles replication fails. To resolve this problem, give the required permissions to the logon account, or configure the SQL Server Agent service to log on with a different account that has the appropriate permissions. For instructions on how to configure the SQL Server Agent service to log on with a given account, see Changing the SQL Server Agent logon account.
You can use the following instructions to verify that the Replication Agents are configured properly. The instructions vary depending on whether the SQL Server holds the Publisher or Subscriber role. In both cases, connect to the Publisher SQL Server using SQL Server Management Studio.
Replication Agent connection to Publisher
If the Administration Service connects to the Publisher SQL Server using Windows authentication, follow these steps to verify that the Replication Agents are configured properly.
To verify that the Replication Agents are configured properly
-
With SQL Server Management Studio, connect to the Publisher SQL Server.
-
In the Object Browser, under the Publisher SQL Server, right-click the Replication folder, and then click Distributor Properties.
-
In the left pane of the Distributor Properties window, click Publishers.
-
In the Publishers list, select the entry representing the Publisher SQL Server, and click in that entry to display the Publisher Properties dialog.
-
In the Publisher Properties dialog, under Agent Connection to the Publisher, verify that the Agent Connection Mode property is set to Impersonate the agent process account.
Replication Agent connection to Subscriber
If the Administration Service connects to the Subscriber SQL Server using Windows authentication, follow these steps to verify that the Replication Agents are configured properly:
To verify that the Replication Agents are configured properly
-
With SQL Server Management Studio, connect to the Publisher SQL Server.
NOTE: You must have Management Studio connected to the Publisher SQL Server, regardless of whether you are managing Replication Agents for the Publisher or for a Subscriber.
-
In the Object Browser, under the Publisher SQL Server, expand Replication > Local Publications > AelitaReplica.
-
In the list under AelitaReplica, right-click the Subscriber SQL Server and click Properties.
-
In the Subscription Properties window, in the Security section, expand the Subscriber connection entry.
-
Verify that the Subscriber connection property is set to Impersonate agent process account (Windows Authentication).
SQL Server authentication
If the Administration Service uses SQL Server authentication, the Replication Agents connect toSQL Server in the security context of the SQL Server login that is specified for the Administration Service connection to SQL Server.
If the login does not have sufficient rights, it has been deleted, or had the password changed, the Active Roles replication fails. To resolve this problem, do the following:
To avoid replication fail
-
Choose an SQL Server login with sufficient rights. For more information, see SQL Server permissions in the Active Roles Installation Guide.
-
Configure the Administration Service to use that login. For more information, see Viewing database connection settings.
-
Configure the Replication Agents to use that login.
The following sections elaborate on how to configure the Replication Agents to use a given SQL Server login. The instructions vary depending on whether SQL Server in question is the Publisher or a Subscriber.
Replication Agent connection to Publisher
If you have changed the SQL Server login for the Administration Service connection to the Publisher, use the following steps to configure the Replication Agents with that login:
To configure the Replication Agent to connect to the Publisher
-
With SQL Server Management Studio, connect to the Publisher SQL Server.
-
In the Object Browser, under the Publisher SQL Server, right-click the Replication folder, and then click Distributor Properties.
-
In the left pane of the Distributor Properties window, click Publishers.
-
In the Publishers list, select the entry representing the Publisher SQL Server, and click in that entry to display the Publisher Properties dialog.
-
In the Agent Connection to the Publisher area, click Login, and type the login name.
-
Click Password, and then click in the Password entry.
-
In the Enter Password dialog, type and confirm by retyping the password of that login.
-
To close the Enter Password dialog, click OK.
-
To close the Publisher Properties dialog, click OK.
Replication Agent connection to Subscriber
If you have changed the SQL Server login for the Administration Service connection to a Subscriber, use the following steps to configure the Replication Agents with that login:
To configure the Replication Agent to connect to the Subscriber
-
With SQL Server Management Studio, connect to the Publisher SQL Server.
NOTE: You must have Management Studio connected to the Publisher SQL Server, regardless of whether you are managing Replication Agents for the Publisher or for a Subscriber.
-
In the Object Browser, under the Publisher SQL Server, expand Replication > Local Publications > AelitaReplica.
-
In the list under AelitaReplica, right-click the entry corresponding to the Subscriber SQL Server and click Properties.
-
In the Subscription Properties window, in the Security section, expand the Subscriber connection entry.
-
Click in the Subscriber Connection entry.
This displays the Enter Connection Information dialog.
-
In the Login box, type the login name.
-
In the Password and Confirm password boxes, type and confirm by retyping the password of that login.
-
To close the Enter Connection Information dialog, click OK.
-
To close the Subscription Properties dialog, click OK.