SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting Login failed for user 'sa' for Distribution agent


Getting Login failed for user 'sa' for Distribution agent

Author
Message
Jpotucek
Jpotucek
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 1677
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
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10406 Visits: 13687
is the subscriber updateable? Thinking you might need sp_link_publication, but not definite.

---------------------------------------------------------------------
Mike_D
Mike_D
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 615
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
Jpotucek
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 1677
Thank you ! Thank you ! Thank you !



Mike_D
Mike_D
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 615
No problem. Glad it's fixed.

Regards,

Mike
Jpotucek
Jpotucek
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 1677
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
Mike_D
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 615
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
Jpotucek
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 1677
Mike,

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



Roy Ernest
Roy Ernest
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3772 Visits: 6869
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
suzakatha
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 3
How do you do the same for Microsoft SQL 2012 R2?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search