Recreate Replication: easiest way? RRS feed

  • Question

  • I have two nodes, Node 1 is publisher/distributor, Node 2 is a subscriber.

    They authenticate using an AD domain account between one another

    Recently a catastrophic failure caused us to recover an Active Directory controller from prior to the date that the AD account used for this authentication existed.

    Now, certain stored procedures on Node 1 which pull data from Node 2 fail with:

    Msg 18456, Level 14, State 1, Line 10
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Attempting to sp_dropserver results in:

    Msg 20584, Level 16, State 1, Procedure sp_MSrepl_check_server, Line 67 [Batch Start Line 137]
    Cannot drop server 'Node2' because it is used as a Subscriber to remote Publisher 'Node1' in replication.

    I have recreated the Login on Node2 using the new domain account. How can I update the "linked server" login account without breaking replication? Or if I do need to break replication, and rebuild it again, what's the easiest way to go about it without losing data on either node?

    Very much appreciate any insight.

    UPDATE: I discovered that replication is working and querying across servers works if I run the script while logged in using sql authentication. I tried recreating my own domain account login for SQL Server, tested that I can log in with that account on both servers. However, querying across servers still doesn't work.

    Not sure why.

    • Edited by SPGSK Friday, May 17, 2019 2:15 AM
    Friday, May 17, 2019 12:45 AM

All replies

  • Hi,

    Firstly the linked servers created when setting up replication are not set for Data Access then testing these are not always work, unless u change configuration of these linked servers. You do not need update linked servers. It is done under Subscription or Publication Security settings.

    "Now, certain stored procedures on Node 1 which pull data from Node 2 fail with"

    You said Node 1 is publisher? Why u saying Node1 pulling data from Node 2?

    What replication u using push or pull?

    If it is push u can update subscription security and choose different account to be used while running distribution agent and authenticating to subscriber (you can setup SQL auth if needed). If both servers are in same domain just use SQL Service account to run distribution agent. I am assuming AD is back up?

    Friday, May 17, 2019 6:37 AM