Share via

SQL Server Replication

Pawan Rai 1 Reputation point
2022-11-08T10:53:49.287+00:00

I am setting up SQL Server replication (transaction) with below steps and configuration:

Configuration:

  1. Publisher on Machine A
  2. Distribution on Machine B
  3. Subscriber on Machine C

Steps:

  1. Created a common sql server user with same password on both machine A and B
  2. On machine B set up the distributor by enabling distribution and adding a publisher (machine A using username and password created in step 1). Also specified the admin link password.
  3. On Server A trying to configure distribution by using "Use the following server as distributor"-> added the IP of server B, username and password as specified in step 1. Then added the same password as the admin link password defined on server B in step 2.

However, it always says Sql server could not connect to distributor using the specified password.

I have used a complex password abiding password policy and also tried multiple solutions posted online by many users. Basically, removed the publisher from distributor and added again/password updated again and then setting up distributor server on publisher. But keep getting the same error.

Kindly suggest what is wrong here. Have also checked and enabled is_data_access_enabled properties on both machines. Remove access is also turned on for both.

Would also want to refer to any good article that sets up replication (transaction) using 3 different servers on 3 different machines.

Any inputs will be highly appreciated.

Thanks
Pawan

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. YufeiShao-msft 7,091 Reputation points
    2022-11-09T07:09:32.287+00:00

    Hi @Pawan Rai ,

    Something you can do to try to solve this issue:

    Connect to the server where your distribution db resides, right click on the replication folder and select distributor properties, select publishers, uncheck the already entered your publisher and select OK, add a new publisher, enter a password for administrative link, then connect to your publishing server and create a new publication using the remote distribution, enter the password you used earlier when assigning the publisher in the distribution properties

    Or try to remote access was disable
    check if the Run_value under the remote access option is set to 1, if not try to, and restart SQL instance 

    sp_configure ‘remote access’, 1

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.