Quick one today:
Recently, on one of our lab machines, this error occurred with replication. On the surface ‘distributor_admin‘ login seems familiar (Obvious with its naming), but we do not configure this login anywhere during replication set up (on the surface).
Login failed for user ‘distributor_admin‘. (Source: MSSQLServer, Error number: 18456)
First we’ll directly get to ideas on resolving it, then we’ll look at some internal details.
- If you have the password for Distributor_admin, update publisher with it. Go to Replication >> Publisher Properties >> (enter the password in the ‘Administrative Link Password‘ section).
- If you do not have the password, then we need to first reset it.
- Go to Distributor. Right click on Replication >> Distributor Properties >> Publisher tab >> (set new password in the ‘Administrative Link Password‘ section)
- Now, go to Publisher and implement Step 1 (above) on the Publisher.
In Sql Server, all interactions are carried out under clearly defined security logins and their corresponding roles (for better security management). Same applies to Replication as well. When we configure Distributor, a new default login (a.k.a. distributor_admin) is created; we specify password as part of ‘Configure Distributor‘ wizard. If you go to logins section on the distributor machine, we could see the new login. This login is used to perform any operations on Distributor.
When we configure Publisher, we specify this password (for the default login ‘distributor_admin‘), that the Publisher uses to connect to Distributor. This servers two purposes.
- Provides proper security context for Publisher to establish linked server connection to Distributor and perform any necessary activities.
- Provides Distributor that this Publisher (with correct password) is authorized to remotely carry out operations in Distributor.
Hence it is called Administrative Link Password. For more information, please go through this MSDN article.