Can 'distributor_admin' sql login be disabled

Question

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.

Answer 1

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.

Answer 2

Hi @SQLRocker ,

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

---

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

Answer 3

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.

---

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

Answer 4

@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.

Answer 5

@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.