Trans Repl fails on subscriber

• lof

  Default port

  Points: 1498

  More actions

  January 15, 2003 at 3:56 pm

  #80612

  Hi

  I have a transactional replication set up between two SQL2k servers on Win2K. the distributor and publisher is in a domain. The subsriber is a standalone server (not part of a domain). The replication is set up with updates on subscriber.

  When I try to update subscriber tables I get following error

  [Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed for user 'sa'

  Replication/updates from publisher work fine.

  Both servers have the same Pwd for 'sa' Both agents are set up to use sa account

  I can link from subscriber to publisher using sa account.

  Anyone has an idea what is the problem?

  I get 'Login Failed for user 'sa'' even if the replication and agents are set to run under a different user.

  where is the sa account comming into play?

  Thanks

  Jakub

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  January 15, 2003 at 4:20 pm

  #446446

  I think its via linked server, maybe its configured wrong?

  Andy

  http://www.sqlservercentral.com/columnists/awarren/

  Andy
  SQLAndy - My Blog!
  Connect with me on LinkedIn
  Follow me on Twitter

• lof

  Default port

  Points: 1498

  More actions

  January 15, 2003 at 4:23 pm

  #446448

  The linked server works fine on Publisher.

  There is no linked server set up on subscriber. Does there need to be one for replication?

  Jakub

  Edited by - lof on 01/15/2003 4:24:25 PM

• lof

  Default port

  Points: 1498

  More actions

  January 16, 2003 at 8:30 am

  #446515

  Also,

  there is nothing in any of the logs about this, except that xprepl.dll being used

  any help would be appreciated. I need to get this on-line

  Thanks

  Jakub

• lof

  Default port

  Points: 1498

  More actions

  January 16, 2003 at 3:35 pm

  #446571

  I found my answer, where else but in BOL

  Posting here so others can benefit

  BOL:

  When setting up a push subscription using the Push Subscription Wizard in SQL Server Enterprise Manager or the sp_addsubscription stored procedure, the default configuration uses dynamic RPC at the Subscriber. The dynamic RPC defaults to using the sa login with no password . This is done to avoid sending logins or passwords over the network, and can be changed at the Subscriber using sp_link_publication.

  Use sp_helpsubscription_properties to view the values for the above sp.

  there it is, works like charm

  Thanks to everyone

  Jakub