Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting Login failed for user 'sa' for Distribution agent Expand / Collapse
Author
Message
Posted Wednesday, April 29, 2009 6:13 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:12 AM
Points: 750, Visits: 1,556
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..




Post #706709
Posted Wednesday, April 29, 2009 6:40 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:52 PM
Points: 5,988, Visits: 12,923
is the subscriber updateable? Thinking you might need sp_link_publication, but not definite.

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

Post #706739
Posted Wednesday, April 29, 2009 7:14 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:01 AM
Points: 503, Visits: 610
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
Post #706780
Posted Wednesday, April 29, 2009 8:02 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:12 AM
Points: 750, Visits: 1,556
Thank you ! Thank you ! Thank you !


Post #706834
Posted Wednesday, April 29, 2009 8:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:01 AM
Points: 503, Visits: 610
No problem. Glad it's fixed.

Regards,

Mike
Post #706838
Posted Wednesday, April 29, 2009 8:23 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:12 AM
Points: 750, Visits: 1,556
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..



Post #706861
Posted Wednesday, April 29, 2009 9:24 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, February 24, 2014 1:01 AM
Points: 503, Visits: 610
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
Post #706941
Posted Wednesday, April 29, 2009 9:28 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:12 AM
Points: 750, Visits: 1,556
Mike,

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



Post #706948
Posted Wednesday, April 29, 2009 10:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:27 PM
Points: 3,309, Visits: 6,702
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
Post #707004
Posted Thursday, June 12, 2014 12:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:59 AM
Points: 1, Visits: 3
How do you do the same for Microsoft SQL 2012 R2?
Post #1579886
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse