none
SQL Server Replication Configuration- Stuck at Replication Administrative Password step. RRS feed

  • Question

  • Hi everyone,

    I have been struggling for the past one week to setup the replication in our environment and has been stuck on Administrative Password issue when adding Distributor on Publisher Server.

    Replication Configuration I'm using:

    • Distributor: Remote Distributor(as we do not want any performance impact on our prod)
    • SQL Server Version 2017: Is same on all servers (Distributor, Publisher, and Subscriber).
    • Instance: Default instance (not named)
    • No firewall in our environment.
    • All servers are fully connected (Can connect to each other via SSMS).
    • Remote access is enabled.

    Troubleshooting I have performed:

    1. Reset the Distributor password from Distributor Properties. (https://social.technet.microsoft.com/Forums/azure/en-US/dadf963d-9246-44ed-841a-10d27a6cdc4e/not-able-to-connect-to-remote-distributor-from-publisher-using-administrative-link-password?forum=sqlreplication)
    2. Removed publishers and added it back and reset the password.
    3. Deleted the whole distributor and reconfigured.
    4. Changed distributor_admin password from Security (GUI) and using the script method (sp_changedistributor_password [ @password = ] 'password')
    5. Tried the configuration on another server as distributor.
    6. Ensured password policy is met, even removed password policy enforcement.
    7. Removed distributor, changed SSMS version and configured the distributor again.
    8. Configured the whole setup using scripts only.
    9. Checked the Error log of target Server (No login or repl related errors)
    10. Searched for the issue exhaustively on forums.

    No solution has worked so far, any input will be appreciated.

    enter image description here

    

    Thanks in advance. 


    Additional Info:

    When I configured the Distributor on remote machine it created two Linked Servers (repl_distributor and Second as servername of the publisher server). When I tested the connection of both servers I got error as below screenshot. When I tested the connection of both servers I got error as below screenshot. Online search suggest enabling MSDTC which is did but no luck with resolving the original issue or even the Linked Server issue.

    



    • Edited by Shaddy_1 Tuesday, December 17, 2019 4:21 PM
    Thursday, December 12, 2019 6:46 PM

All replies

  • Hi Shaddy_1,

     

    Would  you please try the following steps to fix your issue:

     

    1.1) Connect to the server where your distribution db resides

    2) Right click on the replication folder and select distributor

    properties.

    3) Select publishers. If you have already entered your publisher

    uncheck and select OK

    4) Add a new publisher ,Enter a password for the administrative link

    5) Connect to your publishing server and and create a new publication

    using the remote distributor.

    6) Enter the password you used earlier when assigning the publisher in

    the distribution properties

     

    2.Check if SQL Server is running in fiber mode (Check if ‘Lightweight Pooling’  parameter in sp_configure is enabled on all involved instances)

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 13, 2019 6:42 AM
  • Hi Dedmon,

    Thank you for your comments

    1. I had tried the option 1 several times with no luck.

    2. The lightweight pooling is disabled on all servers. 

    Any other suggestions? 

    Friday, December 13, 2019 4:16 PM
  • Hi everyone,

    I have been struggling for the past one week to setup the replication in our environment and has been stuck on Administrative Password issue when adding Distributor on Publisher Server.

    Replication Configuration I'm using:

    • Distributor: Remote Distributor(as we do not want any performance impact on our prod)
    • SQL Server Version 2017: Is same on all servers (Distributor, Publisher, and Subscriber).
    • Instance: Default instance (not named)
    • No firewall in our environment.
    • All servers are fully connected (Can connect to each other via SSMS).
    • Remote access is enabled.

    Troubleshooting I have performed:

    1. Reset the Distributor password from Distributor Properties. (https://social.technet.microsoft.com/Forums/azure/en-US/dadf963d-9246-44ed-841a-10d27a6cdc4e/not-able-to-connect-to-remote-distributor-from-publisher-using-administrative-link-password?forum=sqlreplication)
    2. Removed publishers and added it back and reset the password.
    3. Deleted the whole distributor and reconfigured.
    4. Changed distributor_admin password from Security (GUI) and using the script method (sp_changedistributor_password [ @password = ] 'password')
    5. Tried the configuration on another server as distributor.
    6. Ensured password policy is met, even removed password policy enforcement.
    7. Removed distributor, changed SSMS version and configured the distributor again.
    8. Configured the whole setup using scripts only.
    9. Checked the Error log of target Server (No login or repl related errors)
    10. Searched for the issue exhaustively on forums.

    No solution has worked so far, any input will be appreciated.

    enter image description here

    

    Thanks in advance. 


    Additional Info:

    When I configured the Distributor on remote machine it created two Linked Servers (repl_distributor and Second as servername of the publisher server). When I tested the connection of both servers I got error as below screenshot. When I tested the connection of both servers I got error as below screenshot. Online search suggest enabling MSDTC which is did but no luck with resolving the original issue or even the Linked Server issue.

    



       No one had this issue? I had a high hope on Microsoft forum. Still stuck at the same step. 

        
    Monday, December 30, 2019 8:44 PM