The below will describe two different methods of amending the passwords for replication agents. This is sometimes necessary - for example, if the Windows account or SQL Server login used for replication has been altered.

These methods can be used for any type of replication and must be executed by a member of the sysadmin fixed server role. To check if you have the correct permissions, you can expand the ‘Server Roles’ node under ‘Security’ in Object Explorer, then right-click sysadmin to see the server role properties. This dialog box will display all members assigned to this role.

As with any changes, it is very important to test where possible and monitor the process for any issues. If possible, it would be an idea to perform the below on a test setup first so you can see how it works.

T-SQL

The following process will describe how to change replication passwords using T-SQL.

In SQL Server Management Studio (SSMS), create a new query on your server. Run the following stored procedure, using your own login details and requirements. This will change the password for the account used by the specified replication agents on the server.

sp_changereplicationserverpasswords
  1, --Login authentication - 0: SQL Server Authentication, 1: Windows    Integrated Authentication
 'myexcellentdomain\tindog', --Username of the login (SQL Server or Windows Account)
 'mynewpassword1234' --, New password for specified login
 --'subscriber' --Type of connection for the password change - could be subscriber, distributor, publisher – if you omit this option, all 3 will be changed.

You’ll see from the comments in the above code that you need to specify the type of login you’re changing. Use 0 for a SQL Server login, or 1 for a Windows account.

Next enter the username and new password. When entering the username, make sure you use the domain if it is a Windows account.

The final option is the type of connection you’re applying the password change to – note that you can only specify this option if you are changing details for a SQL Server login. You can choose to apply this to one specific component of replication, or simply apply to all. In the above example, we are changing the password for all components using the login 'myexcellentdomain\tindog'.

Naturally, if the subscriber, distributor and publisher are stored on separate servers, you will need to make the relevant changes on each server. Once the stored procedure has run, the replication agents on the server(s) must be restarted in order for it to take effect (see the final section - 'Checking for Success').

If the stored procedure has run successfully, SSMS will produce a message confirming this (see Figure 1).

Figure 1: Job credential successfully changed

Object Explorer

Alternatively, you can change the password using the GUI in SSMS. Note that this will change the password for all types of replication connections on the server if they use the specified login. So, in the above stored procedure example we chose ‘Subscriber’ for the type of connection – this method is akin to choosing the default connection type.

To do this, under Object Explorer right-click ‘Replication’ then choose ‘Update Replication Passwords’. Here, you can amend the password for either a Windows or SQL Server account, as demonstrated in the image below. You will need to enter the username (ensuring to use the domain if it is a Windows account) and then your new password.

Figure 2: Updating replication passwords via Object Explorer

Checking for Success

The final, critical step in changing the passwords for your replication is to check for success. This is crucial to ensure there are no unexpected errors as a result of the password change, which could compromise availability of your solution.

First, restart the replication agents. This can be done by right-clicking the ‘Replication’ node in Object Explorer, and selecting ‘Launch Replication Monitor’. Under ‘My Publishers’, choose the relevant publisher. On the ‘Agents’ tab, use the drop down to select the Log Reader Agent. Right-click the agent, select ‘stop’, and then ‘start’ again. Also do this for the Queue Reader Agent, and if required, the Snapshot Agent.

Figure 3: Restarting agents in replication monitor

Still in Replication Monitor, under 'My Publishers', select the relevant publisher. Check each agent for errors, and ensure they are all running as expected. In the above example, an incorrect password was entered for the purposes of this demonstration. As the password was incorrect, the log reader agent action was stuck on "Starting Agent..." and eventually failed.

Double-clicking the agent will allow you to view its history. Once the stored procedure was re-ran using the correct password, the agent ran as expected.

Figure 4: Log Reader Agent History

The above screenshot shows this agent history. You can see where the password was changed (incorrectly) at around 1:54pm, and hung on 'initializing' for a few minutes before failing. The password was corrected at 2:00pm and the agent status changed to 'running'.

If possible, monitor the replication components as data is passed through the system. See if records are being passed through as you’d usually expect, and troubleshoot any errors should they occur.