Distributed Remote/Network Transactions with MSDTC

[jmcilhinney]

This is probably not related to VB but it might be so I'll post it here. If it's not VB related then the DB gurus may have an answer anyway.

Background:
I'm creating an add-in for Microsoft Retail Management System. RMS provides a main and an optional offline database connection. In single terminal setups there will only be a main database and it will be on the local machine. In multi-terminal setups there will be a main central database and possibly a local offline database too.

Issue:
I'm using a System.Transactions.TransactionScope object in my VB.NET code to wrap all my database updates. This invokes the Distributed Transaction Coordinator. If the database is on the local system there's no issue. If the database is remote then there is. Originally I was getting an error message telling me that the DTC had disabled its support for remote/network transactions. I did the reading and dicovered the security implications and where and what settings I had to change to enable this support. After following the instructions I read my DTC options look like the attached image. Now I create my TransactionScope and SqlConnection, then when I open the connection I'm told that the transaction has already been implicitly or explicitly commited or aborted. It certainly wasn't explicit on my part!

This might have something specific to do with the .NET classes I'm using but I think that it's more likely a DTC issue. Anyone got any insight?

[kaffenils]

What you are saying is that you do updates to data on two different SQL Servers in the same transaction, where one is running on the local computer and the other on a server?

If so, are you able to BEGIN DISTRIBUTED TRANSACTION (in QA or SSMS) on the local server and do a simple UPDATE on the remote server?

Could it be a firewall problem as described in this post:
http://forums.microsoft.com/MSDN/Sho...68911&SiteID=1

[jmcilhinney]

Thanks for your input kaffenils. That link provided some good infromation but I neglected to mention before that I'd already implemented the suggested changes. I'd addedd exceptions to the firewall for MSDTC and port 135 and it still wasn't working.

I've done a bit more testing and a bit more reading and it turns out that it's definitely a firewall issue. With the firewall turned off on the server there's no problem. With the firewall on, even with those exceptions, the transaction is reported as being closed. My reading suggests one of two possible issues.

1. Port 135 not opened bidirectionally.

There's no option to specify a direction in Windows Firewall so I can only assume that it's bidirectional or it's nothing.

2. Specify and allow RPC ports.

You can specify a range of ports to dynamically assign to RPC in the registry, then add exceptions in the firewall for those ports too. I'm guessing that this is the culprit in this case. I will test it at some future point but I'm now told that the firewall will definitely be off on the server anyway, due to issues associated with RMS itself.