Can 'distributor_admin' sql login be disabled

SQLRocker 106 Reputation points
2021-04-13T23:34:02.963+00:00

So we are moving away from sql logins and are using only windows logins, and thus wanted to disable 'distributor_admin' sql login.

We do have replication setup with remote distributor. After reading a few msft docs i understand that ‘distributor_admin’ login is needed and is used in 'repl_distributor' linked server and by replication sps when connection to distributor.

https://learn.microsoft.com/en-us/sql/relational-databases/replication/security/secure-the-distributor?view=sql-server-ver15
https://learn.microsoft.com/en-us/archive/blogs/sqlserverfaq/error-messages-and-the-solutions-related-to-distributor_admin-login

Above doc says “Please note that the login distributor_admin should never be deleted. “ But doesn’t say about disabling it

couple of other msft docs
https://learn.microsoft.com/en-us/sql/relational-databases/replication/enable-a-remote-publisher-at-a-distributor-sql-server-management-studio?view=sql-server-ver15
https://learn.microsoft.com/en-us/sql/relational-databases/replication/configure-publishing-and-distribution?view=sql-server-ver15

We had disabled 'distributor_admin' & replication worked fine for a few weeks, but after a reboot we got a account disabled error on 'distributor_admin' which affected the repliacation, re-enabling it solved it.

So I am thinking is there a workaround, we have mixed mode auth, but how does it work if all sql servers invloved in repl have windows auth .... how does the concept of 'distributor_admin' work then.

Pretty much i wanted to know if 'distributor_admin' can be disabled with repl where distributor is remote and if there is a workaround (changing login on the linked server comes to mind) or is there a repl sp change. And how does it work if all you have is windows auth on sql server (not mixed) , thanks.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,596 questions
{count} votes

9 answers

Sort by: Most helpful
  1. CathyJi-MSFT 20,671 Reputation points Microsoft Employee
    2021-04-14T02:20:33.77+00:00

    Hi @SQLRocker ,

    > Pretty much i wanted to know if 'distributor_admin' can be disabled with repl where distributor is remote

    No, it can’t be disabled.

    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.


    If the response is helpful, please click "Accept Answer" and upvote it, thank you.


  2. CathyJi-MSFT 20,671 Reputation points Microsoft Employee
    2021-04-16T07:48:28.753+00:00

    Hi @SQLRocker ,

    I check this in my environment. Node 2 is Publisher server, Node 4 is subscriber server, Node 1 is remote distributor server.

    88440-screenshot-2021-04-16-154012.jpg

    88544-screenshot-2021-04-16-154150.jpg

    88552-screenshot-2021-04-16-154247.jpg

    88545-screenshot-2021-04-16-154443.jpg

    88520-screenshot-2021-04-16-154523.jpg

    88561-screenshot-2021-04-16-154543.jpg


    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    No comments

  3. CathyJi-MSFT 20,671 Reputation points Microsoft Employee
    2021-04-16T08:59:08.01+00:00

    Hi @SQLRocker ,

    > is the pub and dist still using the 'distributor_admin' sql login for repl connections... and does the repl work even when distributor_admin is disabled.

    No, it failed after disabled distributor_admin account.

    88460-screenshot-2021-04-16-170013.jpg

    88507-screenshot-2021-04-16-165831.jpg


    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    No comments

  4. SQLRocker 106 Reputation points
    2021-04-16T06:42:53.263+00:00

    @CathyJi-MSFT

    Well, i don't have a env where i can test this. But i am failing to understand how an sql login can work in win auth.

    No comments

  5. SQLRocker 106 Reputation points
    2021-04-16T08:45:07.557+00:00

    @CathyJi-MSFT

    Thanks for sharing the setup, is the pub and dist still using the 'distributor_admin' sql login for repl connections... and does the repl work even when distributor_admin is disabled... one test i would do would be to disable the login, chk if repl works, if yes , then restart sql services & chk again.

    No comments