While working on a project to configure Transactional replication on Production database as a reporting solution, where requirement was to setup remote distributor (in my case: On subscriber server), I was able to successfully configure the distributor but when I was trying to configure the publication using remote distributor, I faced below mentioned error and that was causing me to move forward.
B) Check if the remote access parameter in sp_configure is enabled on the Publisher. In my case it was already enabled so no luck this time as well. Finally I tried the option C and that worked for me like a magic.
Database Administrator
Microsoft Certified IT Professional (MCITP)
As I found the solution so it forces me to share with all if this can save you in critical time if you are in trouble due to this error.
Error: SQL Server could not connect to the Distributor using the specified password.
By seeing error message , it seems that it is simple error and perhaps I mentioned wrong password but I was sure that this is not the case.
There are couple of things which should be validated in order to resolve this issue and last option C worked for me.
A) I have tried changing password for distributor account by following below mentioned steps but this also didn't help me as expected.
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
5- Enter a password for the administrative link
6- Connect to your publishing server and and create a new publication
using the remote distributor.
7- Enter the password you used earlier when assigning the publisher in
the distribution properties
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
5- Enter a password for the administrative link
6- Connect to your publishing server and and create a new publication
using the remote distributor.
7- Enter the password you used earlier when assigning the publisher in
the distribution properties
B) Check if the remote access parameter in sp_configure is enabled on the Publisher. In my case it was already enabled so no luck this time as well. Finally I tried the option C and that worked for me like a magic.
C) Check if SQL Server is running in fiber mode (Check if ‘Lightweight Pooling’ parameter in sp_configure is enabled on all involved instances) . In my case it was so I turned off ‘Lightweight Pooling’ parameter and issue was resolved.
P.S: SQL Server can run in one of two modes: thread mode or fiber mode. By default, SQL Server runs in thread mode in which a SQL Server worker is associated with a Windows thread throughout all phases of its execution. This can be changed with the sp_configure option ‘Lightweight Pooling’. When Lightweight Pooling is turned on, SQL Server runs in fiber mode in which a SQL Server worker is associated with a user-mode Windows construct called fiber. Switching among fibers is handled in the user mode with the objective of reducing the cost of calling into the kernel for context switches.
Brgds,
Chhavinath Mishra
No comments:
Post a Comment