question

David 1 1 1 avatar image
David 1 1 1 asked

SQL Server 2005 replication. : "Change sa password breaks replication"

Hi guys, I'm a bit of a noob on SQL so please excuse any dumb questions. I have a merge replication setup with a global server (publisher) and 2 regional servers (subscribers). It is running fine with the default sa password. As soon as I change the sa password on any of the regional servers it fails the replcation with the fault of "Login failed for user sa". I have changed the sa password on one of the regional servers using Mangement Studio. I also used the update replication passwords on the regional server under replication. Then I also change the password on the publisher by right clicking the subscriber and going to properties and changing the subsciber connection password for sa. It still fails and will work the moment I change the sa password back to default in all areas. Have I missed something here??

Thanks in advance.

sql-server-2005replicationpasswordsa
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
RickD avatar image
RickD answered

Yes, the thing you've missed is that you shouldn't be using sa for this type of operation. You should create an account the has dbo rights in the database you are replicating and the subscriber, but it definitely doesn't need sa rights.

Did you change the password in each of your subscribers under Properties -> Subscriber Connection in Management Studio?

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.