The operation could not be performed because OLEDB provider "SQLNCLI" for linked server "repl_distributor" was unable to begin a distributed transaction (Microsoft SQL Server, Error 7391)
The above error clearly specifies the connectivity issues between 2 instance, to give more information I have been dealing with this problem at a client's place. The setup that was configured and deployed was a very complicated replication between SQL Server 2005 version cluster as a publisher (out of 5 databases 2 were involved in publishing) and the Distributor is also a SQL Server 2005 instance.
The initial thought on this setup looks fine with no issues to report, the actual problem was started when a new transactional publication has been configured, with this the above error message was reported constantly since the setup of this new replication process.
The DBAs had tried to drop the publication via Management Studio and using TSQL script (with no success):
exec sp_droparticle @publication = @publication, @article = @article, @force_invalidate_snapshot = 1
When executed the script the following error was reported:
Msg 3933, Level 16, State 1, Procedure sp_MSrepl_getdistributorinfo, Line 93
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
To talk about above distributed transaction error BOL specifies in a topic that "save points are not supported in distributed transactions". In the similar way such distributed transactions that were started either explicitly with BEGIN DISTRIBUTED TRANSACTION or within a script is not allowed. Another option is to ensure that MSDTC is up and running in this case, as it is utmost important factor that you need to be ensure within a clustered environment. Also ensure that the SQL Server instance configuration involves the option 'REMOTE PROC TRANS' is ON with SP_CONFIGURE statement.
In the past I have seen the linked server error (error 7391) due to NO TRUST issues between the domains, as the 2 servers were on different domains and on top of that firewall restrictions were in place to stop all kinds of communication between these 2 networks. So keeping that in mind I have checked whether the distributor and publisher are on same domain or any firewall restrictions are in place, not as I was thinking.
As we referred about MSDTC above it is worth to revisit the process defined in the article KBA306843 that helped me solve issues in the past, as there were many changes invovled insce the service pack release of Windows XP & Server 2003 versions.
After all checkup of above process & procedure the resolution was found after restarting the DTC, replication agents and SQLAgent services on the SQL Server instances that are involved here.