Transactional Replication with Updatable Subscription -Subscriber not updating, throws RPC error run sp_link_publication but publisher updates works

Today while working on one of the Replication Server in SQL 2005 where 4 servers participate in a Transactional Replication with Updatable Subscription with Immediate Update are not working but they work when we change update method from immediate to queued because queued update is not using MS-DTC. So after confirming that DTC is causing error, I started my troubleshooting and finally solved the error. Below are the error messages faced and their solution.

Error Message:
The RPC security information for the Publisher is missing or invalid. Use sp_link_publication to specify it.Updateable Subscriptions: Rolling back transaction.The transaction ended in the trigger. The batch has been aborted.

Solution:
Run the below command by connecting to subscriber server through remote desktop. Make sure you run this code in the subscriber database
sp_link_publication @publisher = '{publisher instance here}'
, @publisher_db = '{published database name here}'
, @publication = '{publication name}'
, @security_mode = '1'
, @login = '{sql server login account to connect publisher}'
, @password = '{password}'
, @distributor = '{distributor instance name here}'

For complete syntax description of above Stored Proc Click here

After running above command, you can see a linked server created with below type name
REPLLINK_ABC-DB-02-914029527_DBNAMA-646054632_PUB_PUBNAME190009504_SUBDBNAME-1041306886

Now replication will work!

Error Message:
MSDTC on server 'ABCD-EF-01\DATA' is unavailable.

Solution:
Make MS-DTC to run with "NT Authority\NetworkService" login in both Publisher and Subsriber with Mutual Authentication. It works! We faced this error when we ran with Domain Account which had local admin privilege and also SQL server is running with this domain account but still this error.

Error Message:
When you select "Refresh all Components" in MS-DTC you get this error "Catalog Error -- The requested action cannot be performed because DTC is not running.

Solution:
MS DTC is running with an account other than "NT Authority\NetworkService", so change the account back to "NT Authority\NetworkService" it will work

Error Message:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
This occurs when we run the sp_link_publication command in Management Studio connected from my desktop

Solution:
Get into Remote Desktop to subscriber server and run command there, It will work! This error occurs because of delegation error.
For example you have three server X, Y, Z and you are connecting from X to Y and from there using Linked Server when you try to query Z server you will get this error. This will happen only if X, Y, Z are running under domain account. In order to resolve this issue you need to setup Delegation in Active Directory. Use this link for help on this

Also don't create SQL Client alias'es to any of the replication servers. It will create issues.

Steps to toubleshoot MS-DTC in Source Server
1. Check Control Panel --> Administrative Tools --> Component Services --> Computers --> Right Click My Computer --> Properties --> Security Confugration --> MSDTC and be sure that you have it set to Allow Remote Clients.

2. Start --> Run --> Type "appwiz.cpl" --> Add/Remove Windows Components --> Application Server --> Details, verify that Enable Network DTC access is checked. If not check it, click OK, Next etc...to install that piece (does not require reboot).

3. If both of those are turned on and should be working. Still if it is not working then goto command prompt trying uninstalling and reinstalling msdtc by running below commands.

msdtc -uninstall
msdtc -install

(Last time we had MSDTC issue in one of the cluster server, after 6 hours struggle by PSS Engineers, finally running above two commands solved the problem)

Once all of those things have been handled try running your transaction again.

If you are running within a cluster, try all of those things and then if it still does not work then try moving the Group that MSDTC is contained in over to a different node.

Tools to Troublshoot MSDTC Issues
1. DTCTester Tool
2. DTC Ping Tool

Useful Links:
1) How to Re-install MSDTC for a nonclustered windows server
2) How to clean up a damaged COM+ catalog
3) How to Setup Delegation (With Reference Pictures)
4) How to Setup Delegation
5) How to setup SPN