More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Setup Transactional replication with "Immediate Updating Subscribers". Changes on publisher get replicated to the subscriber ...
Q: Setup Transactional replication with "Immediate Updating Subscribers". Changes on publisher get replicated to the subscriber, without any errors. But when try to UPDATE/INSERT data at the subscriber, the following error and not able to modify data at the subscriber:
Server: Msg 18456, Level 14, State 1, Line 3
Login failed for user 'sa'.
How to correct this problem?
A: 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.
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'
(Continued on next question...)
