Error messages and the solutions related to “distributor_admin” login

Article
12/21/2009

Error messages:

The log reader fails with the following error message:
==============================================

The process could not execute 'sp_MSpub_adjust_identity' on 'PR2K8\YUKON'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Get help: https://help/MSSQL_REPL20011

Get help: https://help/18456

Snapshot Agent may fail with this error:
=========================================
Could not retrieve agent status. (Login failed for user 'distributor_admin'. (.Net SqlClient Data Provider))

Distribution agent may fail with this error message:
======================================================

An error occurred while attempting to access the subscription. (View Synchronization Status)

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Rmo)

------------------------------

For all the above error messages, the cause of the problem may be that the distributor_admin login password does not match with the password set on the distributor server.

Solution:
=========

If you know the password of distributor_admin login on the distributor server, then set the same password on the Publisher Server using stored procedure sp_changedistributor_password

If you don’t know the password for distributor_admin login on the Distributor Server then you need to follow the below steps:

1. On the distributor server, run SP sp_changedistributor_password with the new password that you would like to set.

Example: sp_changedistributor_password 'newpassword'

Or, you can also use the Distributor Properties window on the Distributor Server((Right click on the Replication folder and click Distributor Properties) to change the Distributor_Admin password as shown in the below figure:

2. On all the remote publisher server/s, run SP sp_changedistributor_password using the password specified in the first step.

Or, you can also use the Publisher Properties window on the Publisher Server to change the distributor_admin password (Right click on the Replication folder and click Publisher Properties) as shown in the below figure:

Information about distributor_admin login in SQL Server and why it is needed in replication
============================================================================

Distributor_admin is a SQL Server login account which is used by the repl_distributor linked server (remote server) in SQL Server replication. This login is created on the Distributor Server and the administrator specifies the password for the distributor_admin login (for the Remote Distributor Server). The same password is then needed at the remote publisher when distribution is configured from the remote publisher server. This helps in securing the Distributor Server so that none of the servers in the topology can use the Distributor Server for distribution purpose without specifying the distributor_admin password. Therefore, the distributor_admin password works as a key for the Publisher Server to enable itself to use the remote server as its distributor server.

What is Local and Remote distributor

local distributor - distribution database is present within publisher instance

remote distributor - distribution database is present on the remote SQL Server instance and not on the publisher instance

Configuring a Local Distributor :
=========================

When you run the "Configure distribution" wizard on the Distributor Server for local distributor, the following gets created on the Distributor Server:

1. distributor_admin (SQL Server login account) is created on the SQL Server Distributor Server and the password is generated automatically.

2. Also, the linked server (remote server) named repl_distributor is created on the SQL Server Distributor Server which uses the distributor_admin login credentials for the remote server connections.

-----------------------

Configuring a Remote Distributor :
===========================

Configuring a Remote Distributor is done in two steps:

A. When you run the "Configure distribution" wizard on the Distributor Server for remote distributor, you specify the remote publisher and the following gets created on the Distributor Server:

1. Distributor_admin (SQL Server login account) is created on the SQL Server Distributor Server and the password is specified by the Administrator.

2. Also, the linked server (remote server) named repl_distributor is created on the SQL Server Distributor Server which uses the distributor_admin login credentials for the remote server connections

B. You then go to the Publisher Server and run the “configure distribution” wizard and specify the distributor server along with the distributor_admin login password (same password that you specified in Step A-1).

The repl_distributor linked server is used for the following purposes:

1. Used by the replication stored procedures and functions that require processing on the publication database as well as the distribution database.For example: When you create the publication using wizard or using SP sp_addpublication an entry is made in syspublications table on the publication database and details about this publication are also inserted in the mspublications table in the distribution database.

This linked server is used regardless of local distributor or for the remote distributor

2. Used to fetch the information from the distribution database like replication agent status:

For example: Information about distribution agent and log reader agent status from msdistribution_history, mslogreader_history tables from the distribution database

Whenever you add the first remote publisher on the distributor server, the password for distributor_admin login is required. When you add the subsequent remote publisher on the Distributor server, the same password is used that was used for the first publisher.

Also, when you configure distribution from the remote publisher, distributor_admin password is required.

Please note that the login distributor_admin should never be deleted. To change the password for the distributor_admin login, always use the SP sp_changedistributor_password on the Distributor server as well as on the remote publisher server/s or use the above GUI screens.

Written By:
Gaurav Mathur
Microsoft SQL Server Escalation Services

Reviewed By,
Akbar Farishta, TL, Microsoft SQL Server
Akshay Mittal, Microsoft SQL Server Escalation Services

Error messages and the solutions related to “distributor_admin” login

Additional resources