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

@CathyJi-MSFT

Interesting, thanks a lot Cathyji.

It's interesting that sql logins still can be created and exist in a win auth sql server, and also that they work.

My issue is that due to policy (which is in the works) we need to disable all sql logins, which includes 'distributor_admin', and i don't see any msft doc explicitly saying that one cannot disable this login.

I would be interested if someone out there has done that (disabled the login) and knows a workaround for repl to still work, thanks.

Answer 2

Hi @SQLRocker ,

Thanks for your reply. If the responses helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Answer 3

Thanks @CathyJi-MSFT , i have up-voted your comments as helpful, but i am still looking for a workaround.

Answer 4

Thanks @CathyJi-MSFT , from what i have observed and from what you have tested, it seems like disabling 'distributor_admin' causes replication not to work. what i am looking for is someone out there who maybe has replaced that login in the linked server connections by a windows login and maybe has had to change some repl sp's , in order to make repl work with a windows login.

I would be interested if anyone has done that or thought about it, thanks for your help and time @CathyJi-MSFT , i appreciate it.