I’ve been chasing problems with MSDTC today. We were trying to get one machines on a different domain to use MSDTC through COM+ to talk to a remote SQL Server on a different domain.
Select / Read operations seemed to work fine but when it attempted to use an UPDATE method in a transaction, it failed with an exception saying
COM+ was unable to talk to the Microsoft Distributed Transaction Coordinator (Exception from HRESULT: 0x8004E00F)
The following information describes my eventual journey to success.
The first thing to try is DTCPing. I found a Microsoft Knowledgebase article that takes you through checking firewall permissions and checking the MSDTC ports. Get this working from both client and server ends first.
The second utility to try from Microsoft is DTCTester. There’s another useful Microsoft Knowledgebase article which takes you through how to run it. One thing I had to do before running this was create an ODBC System DSN entry from the client computer to the server for DTCTester to use. This isn’t how I was connecting to the SQL Server but was necessary to test.
In the midst of all of this the Distributed Transaction Service decided to crash and uninstall itself on the client machine. If this happens try the following on a command line prompt:
- msdtc -uninstall
- msdtc -install
- msdtc -resetlog
Check that the DTC service has been reinstalled and has the Network Service permission.
Running DTCTester.exe resulted in
tablename= #dtc29131 Creating Temp Table for Testing: #dtc29131 Warning: No Columns in Result Set From Executing: 'create table #dtc29131 (ival int)' Initializing DTC Beginning DTC Transaction Enlisting Connection in Transaction Error: SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server Driver] Distributed transaction error' Error: SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid cur sor state Typical Errors in DTC Output When a. Firewall Has Ports Closed -OR- b. Bad WINS/DNS entries -OR- c. Misconfigured network -OR- d. Misconfigured SQL Server machine that has multiple netcards. Aborting DTC Transaction Releasing DTC Interface Pointers Successfully Released pTransaction Pointer.
There were several other investigation paths which bore no fruit so I’ll just cut to the solution; one of which included creating a VPN tunnel from client to server.
I found a blog post after searching for the keywords “dtctester sqlstate=25S12”. One of the solutions was to turn the MSDTC security down to requring No Authentication instead of Mutual Authentication Required. This makes sense as the fact both client and server computers were on different domains, I suspect authentication requests from either end were failing because of this.
- Open Component Services (Administrative Tools -> Component Services)
- Expand the Component Services tree and the Computers tree to see ‘My Computer’
- Right click on My Computer and select Properties
- Select the MSDTC tab
- Click on ‘Security Configuration’ in the bottom left hand corner Transaction Configuration section.
- Change the Transaction Manager Communication from Mutual Authentication Required to No Authentication Required.
- Click OK out of all dialogs
Running DTCTester again now results in the following:
tablename= #dtc6875 Creating Temp Table for Testing: #dtc6875 Warning: No Columns in Result Set From Executing: 'create table #dtc6875 (ival i nt)' Initializing DTC Beginning DTC Transaction Enlisting Connection in Transaction Executing SQL Statement in DTC Transaction Inserting into Temp...insert into #dtc6875 values (1) Warning: No Columns in Result Set From Executing: 'insert into #dtc6875 values ( 1) ' Verifying Insert into Temp...select * from #dtc6875 (should be 1): 1 Press enter to commit transaction. Commiting DTC Transaction Releasing DTC Interface Pointers Successfully Released pTransaction Pointer. Disconnecting from Database and Cleaning up Handles
The application that had previously failed now works and happiness has returned to the land of MSDTC and COM+ (as much as the land of COM+ can be happy!)