Exception: System.EnterpriseServices.TransactionProxyException

I get an exception when configuring BizTalk 2010.

BizTalk application server.
Separate SQL server for the databases.

Exception: System.EnterpriseServices.TransactionProxyException

Looking at http://support.microsoft.com/kb/293799

When a System.EnterpriseServices.TransactionProxyException exception is triggered during a transaction completion, it cannot be caught from other application domains. Instead, you receive a System.Runtime.Serialization.SerializationException exception that resembles the following:

Unhandled Exception: System.Runtime.Serialization.SerializationException: Type 'System.EnterpriseSer vices.TransactionProxyException' in Assembly 'System.EnterpriseServices, Version=2.0.0.0, Culture=ne utral, PublicKeyToken=b03f5f7f11d50a3a' is not marked as serializable.

Gave me a hint to the problem, however was not the solution…… or any where near to it be careful following this I did not.

I saw that the databases were created during the configuration, and then deleted; only leaving the management database in a weird state. So the communication to the database server was fine.

I did some more investigation and found the dtctester tool… great for testing if my dtc was set up correctly, as this is one thing that BizTalk leverages extensively.

I ran the 'dtctester' tool, and it came up with the following:

Executed: dtctester.exe
DSN: PMS
User Name: *******
Password: ********
tablename= #dtc16032
Creating Temp Table for Testing: #dtc16032
Warning: No Columns in Result Set From Executing: 'create table #dtc16032 (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.

Ok, so my problem was DTC….

I found a great response to a similar problem that told me to look at a couple of DTC issues. (http://dbaspot.com/sqlserver-server/215054-msdtc-doesnt-seem-work.html)

http://support.microsoft.com/kb/817064
http://support.microsoft.com/kb/301600

If you are running Win2K3 SP1, you will need to reset the DTC security parameters:

http://support.microsoft.com/kb/899191

Since you are beginning the transaction on a remote host, that host will also need to properly configure its DTC services.

Finally, if the communication must transit any firewalls, then you will need to restrict RPC ports on both Client and Server for Internet Ports, and then authorize these Ports in the firewall ACLs.

http://support.microsoft.com/kb/300083
http://support.microsoft.com/kb/250367/EN-US

I checked the firewall, no problem, I checked the other things, and made a change to the DTC security not via the method mentioned but via the component services.

Everyone should know by now to change the security settings under component services for the dtc on the sql server and the BizTalk server.

Go to the Component services/My Computer/Distributed Transaction Controller/LocalDTC

Properties/security

Ensure that everything in here is checked, (Remote admin is optional)

I usually select no Authentication required, and ensure that the network service account is running the service.

I did this, thinking I’ve found the problem…..

Nope same problem re occurs…

I find out that the SQL server is clustered, so I go to the cluster manager, and look at the dtc settings, and open the localdtc from there, the same thing, all the settings are correct because I had already changed them.

Then I notice, CLUSTER DTC, and expand that and mange that…. The cluster has its OWN instance of DTC, of course, it does not use the local DTC at all.

I look at the security settings of the cluster DTC, and change those to match, and wolla… it works…. Nice to know if the SQL server is clustered…

Always look at DTC, if your BizTalk does not wish to install to a remote SQL server.