MSDTC Adventures: 'System.EnterpriseServices.TransactionProxyException'

I was configuring a new Development environment that consisted of a BizTalk Application Server and a Remote SQL Server.  The BizTalk Installation went smoothly but the Configuration did not.  I tend to configure SSO service on its own as it can be problematic. There is no point in configuring the remaining databases until a person knows that the SSO database is created successfully.  Otherwise, there will be a lot of wasted key strokes.

In this particular case I was able to configure the SSODB but as soon as I would try to configure the Group and Runtime, the System.EnterpriseServices.Transaction Proxy exception started to surface.

If you perform a Google/Bing search for 'System.EnterpriseServices.TransactionProxyException' you will probably get quite a few results.  Most of the results will point you in the direction of a MSDTC not being enabled or configured correctly.

In my case MSDTC was configured correctly based upon Microsoft’s recommendations. I was a little puzzled at this point but in some of the support forums people were discussing the DTC Tester tool.  Having been through a lot of frustration and a few failed attempts at trying to configure the Group and Runtime features I figured I would give it a try.  I am glad I did.

The purpose of DTC Tester is to enlist a remote transaction against the Temp Database which is hosted in SQL Server.  If you are able to successfully enlist and commit this transaction then there is a very high probability that your BizTalk configuration (as it relates to MSDTC) will be successful.

The tool is available here.  Download it and run the self extracting executable.  At this point we will discover a ‘dtctester’ executable and an End User License Agreement.

The dtctester executable needs to be run from a command prompt but requires an ODBC connection is configured before we can run the tool.  The ODBC connection should be pointed at the Database Instance where we want our BizTalk Databases to be created.

ODBC connections can be created via the Control Panel and a System DSN will work.

From a command prompt we can now run the tool.  When I ran it for the first time I encountered errors (highlighted in red)

 

C:\Downloads>dtctester <ODBCConnectionName> <UserName> <Password>
Executed: dtctester
DSN:  <ODBCConnectionName>
User Name: <UserName>
Password: <Password>
tablename= #dtc7063
Creating Temp Table for Testing: #dtc7063
Warning: No Columns in Result Set From Executing: 'create table #dtc7063 (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.

In my situation the issue ended up being the Windows Firewall on the BizTalk Server was not allowing Outbound DTC connections which was blocking MSDTC from participating in Transactions with SQL Server.  You can modify the MSDTC Firewall rules in the Control Panel.

After allowing MSDTC to communicate through the firewall I re-ran the dtctester tool and had success.

C:\Downloads>dtctester <ODBCConnectionName> <UserName> <Password>
Executed: dtctester
DSN:  <ODBCConnectionName>
User Name: <UserName>
Password: <Password>
tablename= #dtc9418
Creating Temp Table for Testing: #dtc9418
Warning: No Columns in Result Set From Executing: 'create table #dtc9418 (ival int)'
Initializing DTC
Beginning DTC Transaction
Enlisting Connection in Transaction
Executing SQL Statement in DTC Transaction
Inserting into Temp...insert into #dtc9418 values (1)
Warning: No Columns in Result Set From Executing: 'insert into #dtc9418 values (
1) '
Verifying Insert into Temp...select * from #dtc9418 (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

After completing this successful test with the dtctester tool I could successfully configure my BizTalk Group and Runtime.