SQL Server Performance Forum – Threads Archive
Replication password change
I do not know if I should post this in this category. However, since it is replication related, i thought u guys would be able to give better idea. We recently had a problem when we changed the password being used in replication. The password got changed successfully and we were able to login using the new password. However, the snapshot agent was unable to connect to the publisher and gave us a "login failed for user…." error. Hence, we implemented the below: To change the publisher’s properties:– Open Enterprise Manager
– Connect to the Distribution server
– Go to Tools -> Replication -> Configure Publishing, Subscribers, and Distribution…
– Go to Publishers tab
– Click on the properties button (…) against the publisher’s name
– Enter the new password for the SQL Server login. To change the subscriber’s properties, repeat the same steps as above, but in step 3, go to Subscribers tab. Restart your agents, and they should be able to connect to the publisher/subscriber without any errors. I did the above and the Replication worked fine. I want to know if this process can be scripted so that I can automate the password change process. Regards, Chetan
Run the sql trace while doing pwd modification and see what it is using…and also check sp_link_publication procedure in BOL…
MohammedU.
Moderator
SQL-Server-Performance.com
Hi, I checked this SP. It stores the login and password info in a table called MSsubscription_properties. I do not find this table in the subscription database. I am using push subscription. Do you have any ideas on that since this is a production environment, the customer has certain constraints running the profiler. Let me know if anybody can help? Regards, Chetan
I apologise for a mistake in previous post. The table MSsubscription_properties exists but there is no data in this table. I re-checked and found that the SP either inserts/updates the table which is fine. However, my question is if that if this table currently does not hold data then this should not be the required table I am looking for; since I am looking to update a existing record and not inserting any new record. Let me know if this is not correct interpretation.
Best Regards, Chetan
I changed the password again while the profiler was running…….could not find the sp_link_publication…can anybody help in identifying the SP? I think this is a regular activity in any setup and somebody here in this forum would know [?]
Best Regards, Chetan
]]>