question

SQLRocker avatar image
0 Votes"
SQLRocker asked BrandonMark-4923 published

Can 'distributor_admin' sql login be disabled

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://docs.microsoft.com/en-us/sql/relational-databases/replication/security/secure-the-distributor?view=sql-server-ver15
https://docs.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://docs.microsoft.com/en-us/sql/relational-databases/replication/enable-a-remote-publisher-at-a-distributor-sql-server-management-studio?view=sql-server-ver15
https://docs.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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

From secure-the-distributor

Disabling the distributor_admin login
If the distributor_admin login is disabled at a remote Distributor, you may no longer be able to do the following:

Create or delete publications.
Change the articles of an existing publication.
See the agent status using SQL Server Management Studio (SSMS) or Replication Monitor on the Publisher.
Create or delete subscriptions.
Post tracer tokens by using Replication Monitor or by executing sys.sp_posttracertoken.
Configure a remote Publisher at the Distributor.
As such, disabling the distributor_admin login at a remote Distributor is not recommended. While disabling the distributor_admin login on a local distributor may not impose the same limitations, it is still not a recommended practice.


0 Votes 0 ·
Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered Cathyji-msft commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the reply @Cathyji-msft .

So how does it (replication) work when mixed mode authentication is not used in sql server (i.e 'windows authentication' is used)?

0 Votes 0 ·

Hi @SQLRocker,

The distributor_admin account is required for both windows and mixed authentication. It works the same as in mixed mode.

0 Votes 0 ·
SQLRocker avatar image
0 Votes"
SQLRocker answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered Cathyji-msft edited

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.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLRocker avatar image
0 Votes"
SQLRocker answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
1 Vote"
Cathyji-msft answered Cathyji-msft edited

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLRocker avatar image
0 Votes"
SQLRocker answered

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




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SQLRocker avatar image
0 Votes"
SQLRocker answered Cathyji-msft commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SQLRocker,

Thanks for your response. Have a good luck!

I personally think that deleting a login account or disabling a login account has the same effect. They all cause the account can not be used as normal.

0 Votes 0 ·
SQLRocker avatar image
0 Votes"
SQLRocker answered Cathyji-msft commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Let's waiting for the good news.

0 Votes 0 ·