Getting Login failed for user 'sa' for Distribution agent

  • Jpotucek

    SSCoach

    Points: 18583

    We are replicating a database from one of our production 2000 sql servers to a laptop. The database contains employee information and photos for the security department and they bring the laptop offsite each night.

    They changed the sql server 'sa' password on the laptop and now replication is failing. The distribution agent is failing with the error "Login failed for user 'sa'"

    I cannot figure out how to change how replication is getting authenticated on the laptop. Am I going to have to drop and recreate the puublication?? hoping someone can give me some guidance on this one..

  • george sibbald

    SSC Guru

    Points: 104200

    is the subscriber updateable? Thinking you might need sp_link_publication, but not definite.

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

  • Mike_D

    Hall of Fame

    Points: 3295

    In Enterprise Manager

    Highlight the name of the publisher

    click on Tools on the menu bar

    Move the mouse to Replication > and over to Configure Publishing, Subscribers, and Distribution then click

    Dialog box should appear

    Click on Subscribers tab

    Click the edit button for the relevant subscriber

    You can set the password in the section below "Agent connection to the Subscriber"

    Mike

  • Jpotucek

    SSCoach

    Points: 18583

    Thank you ! Thank you ! Thank you !

  • Mike_D

    Hall of Fame

    Points: 3295

    No problem. Glad it's fixed.

    Regards,

    Mike

  • Jpotucek

    SSCoach

    Points: 18583

    I know this should probably be a different thread.....

    but now that I have my 'sa' problem resolved, I am getting this error on a bunch of tables:

    Could not drop object 'table_name' because it is referenced by a FOREIGN KEY constraint.

    Is there any way to configure replication to avoid this???

    I really need ALL the tables on the subscriber.. they want to be able to launch their software and have it run on the laptop against this 'replicated' database..

  • Mike_D

    Hall of Fame

    Points: 3295

    Presumably, this is the distribution agent applying a snapshot? At my subscribers I don't create the foreign keys. I'm happy that if all is well at the publisher then all will be well at the subscriber. Consequently, I'm not a snapshot expert. In the publication properties on the articles tab if you click on one of the edit buttons next to one of the tables a dialog box appears with a snapshot tab. If you click on that you will see a list of option under "Copy objects to destination". By default most are switched off but there is one for referential integrity.

    If you do want foreign keys at your subscriber you can create them with the "NOT FOR REPLICATION" option which means that the foreign keys are ignored when data is inserted etc. But this won't apply in your case as I'm guessing you are using Snapshot Replication.

    So to fix your distribution agent problem you could temporarily drop the foreign keys if that's not too big a job. Otherwise, it's a drop subscription and recreate the publication so that foreign keys don't get created at the subscriber if that's how they got there in the first place. But as I say, I'm not a snapshot expert so I might be misinforming you.

    Mike

  • Jpotucek

    SSCoach

    Points: 18583

    Mike,

    Thank you for your insight. I will try a couple of things and let you know how I make out!

  • Roy Ernest

    SSC-Dedicated

    Points: 38707

    Is this a Snapshot replication or Transactional Replication or Merge replication you are using? If it is Snapshot replication, I would say it is better not to use replication at all. It is better to use Back and restore method. If you have softwares like Litespeed, your back up and restore will be very quick.

    If it is Transactional Replication, then it is better to not have foreign key and other constraints like that on your subscriber.

    If it is Merge, yes, you will need to have all those constraints. And if it is failing then you have some kind of problem with your application.

    -Roy

  • suzakatha

    Valued Member

    Points: 61

    How do you do the same for Microsoft SQL 2012 R2?

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply