Last week I devoted a full working day to this subject. Distributed Transactions, Linked Servers and MSDTC (Distributed Transaction Coordinator) were the only words available on my internal lexicon. I’ve abused from Google as much as I could with no success. Apparently, there are quite a few issues that can trigger this error, most of them related to a bad MSDTC configuration on both ends (the target SQL server and the one starting the transaction). I was on holidays for 2 weeks and, before I left the office, the queries between servers were working. So I checked, double checked, triple checked all of my configurations. Nothing had changed, and I was not going to change anything on the other end as several other processes could be compromised.
I ended up figuring that, the issue, had nothing to do with _any_ of the solutions found on Google. My problem was totally different.
While taking a break from this issue, I decided to address another issue I had on my TO-DO list, so I started scrolling through my Event Viewer and I had a couple of DFSR (Distributed File System Replication). The error stated:
The DFS Replication service failed to contact domain controller to access configuration information
I ran the following command, as this paper on Technet suggested:
Dfsrdiag pollad /verbose
The result was Operation Failed.
I decided to re-add the server to the Domain. Ran the command again and it ran successfully. I also checked on the Network Adapter if the DNS suffix was properly configured (just in case!). The DFSR started working normally and, out of curiosity I tried to ran my cross-server queries to see if the issue was related to this error, and to my surprise, the query ran with no errors.
Seriously, I am not 100% sure that the cause of the error was the one I described, but the fact is it made it work. On another note, I thought that since there was a problem contacting the DC, maybe that NT Authority/NetworkService account the MSDTC uses to run was not logging on properly thus not being able to authenticate on the end server. I hope this helps someone out there and hopefully save you a few hours of troubleshooting. If it does, drop me a line! If your issue had a totally different solution, I am interested in knowing about it.
Google Searches I made:
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "dvorak" was unable to begin a distributed transaction.
the dfs replication service failed to contact domain controller to access configuration information
"Msg 7391 Unable to begin a distributed transaction"
sql server 2005 to sql server 2008 msg 7391
sql server msg 7391
linked server unable to start distributed transaction
Distributed Transactions fail on Linked server