sql server replication: We changed the 'sa' password on the subscriber to the exact same
password but then the transactional replication from the distributor
to subscriber is throwing this error:

Login failed for user 'sa'. (Source: MSSQLServer, Error number: 18456)

We tried resetting the password under the distribution agent but that
didn't work.

Any ideas on how to get this working again?

Hello Greg,

Immediate Updating subscribers connect the publisher using dynamic RPC, and
this is the default. Dynamic RPC defaults to using the sa login with blank
password. This behavior can be changed at the Subscriber using
sp_link_publication. See "Security for Immediate-updating Subscribers" and
"sp_link_publication (T-SQL)" in Books Online for more information

To get rid of this error, execute sp_link_publication on the subscribing
database and specify the sa password for the publisher.

E.g:
sp_link_publication
@publisher = 'Your_Publishing_Server_Name',
@publisher_db = 'Your_Publishing_Database_Name',
@publication = 'Your_Publication_Name',
@security_mode = 0,
@login = 'sa',
@password = 'Your_Publisher_SA_Password'

Source: http://vyaskn.tripod.com/repl_ans4.htm

--
Ekrem Önsoy
MCBDA, MCTS: SQL Server 2005, MCSD.Net, MCSE, MCT



[quoted text, click to view]