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: dtctesterDSN: <ODBCConnectionName>User Name: <UserName>Password: <Password>tablename= #dtc7063Creating Temp Table for Testing: #dtc7063Warning: No Columns in Result Set From Executing: 'create table #dtc7063 (ival int)'Initializing DTCBeginning DTC TransactionEnlisting Connection in TransactionError: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 cursor stateTypical Errors in DTC Output Whena. 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 TransactionReleasing DTC Interface PointersSuccessfully 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: dtctesterDSN: <ODBCConnectionName>User Name: <UserName>Password: <Password>tablename= #dtc9418Creating Temp Table for Testing: #dtc9418Warning: No Columns in Result Set From Executing: 'create table #dtc9418 (ival int)'Initializing DTCBeginning DTC TransactionEnlisting Connection in TransactionExecuting SQL Statement in DTC TransactionInserting 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): 1Press enter to commit transaction.
Commiting DTC TransactionReleasing DTC Interface PointersSuccessfully 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.