WCF-SQL and DTC Ambient Transactions in a cross domain scenario

Just two weeks ago I came across a requirement whereby I had to deploy a BizTalk application to an integrated test environment, this application making use of a WCF-SQL receive location with ambient transactions configured.  The ambient transaction option ensures that the BizTalk adapter flows a transaction through to SQL Server and thus the SQL transaction will only commit when the message received by BizTalk is successfully written to the BizTalk message box database.  This is of course crucial in a guaranteed delivery based solution where you can’t afford to lose any messages.

The agreement made with the database developers was that Windows Integrated security would be used so the BizTalk host instance account would need permissions over the relevant SQL resources.  What I didn’t realize until we performed the deployment was that on the test environment the SQL database server was not on the domain but was rather configured to be in a workgroup.  The problem of Windows integrated security being unavailable was easy to overcome as we decided that on the test environment we would use SQL logins instead, however I did not want to compromise on transactional behavior as doing so would mean that we would observe different behaviors in the test environment and other environments.  I also found this blog post (no disrespect intended to the writer who took the time to share his learnings) that suggested this was not possible, however I was not convinced.

I found that all worked well when ambient transactions were turned off, however when turned on it looked like the receive location just hangs, holding a lock on SQL resources (I tried to do a select on the table in question using SQL Server Management Studio and it couldn’t return any values due to locks being in place) which won’t be removed until the host instance is reset.  This is after ensuring that MSDTC was setup on both servers as per the below configuration (MSDTC settings can be found by running dcomcnfg, expanding Component Services\Computers\My Computer\Distributed Transaction Coordinator\Local DTC, right clicking on it and choosing properties and then browsing to the security tab).

The very first thing I did was to ensure that any firewalls (both OS level and hardware levels) were configured to allow DTC connections between the two servers.  This wasn’t a problem in my case as both servers had their windows firewalls turned off and seeing as they were on the same network there were no hardware firewalls between them.  If firewall configuration is applicable to you take a look at this article.

The next thing I did was download the DTCPing tool, which you will need to extract onto both the BizTalk server as well as the SQL server and have running on both.  You will then need to type in the SQL server NetBIOS name into the tool and run it, repeating the same test from the SQL server to the BizTalk server.  There are a variety of error messages that the tool might return to you (see this article for more information on troubleshooting MSDTC using DTCPing) however in my scenario I found that there was no problem when running the test from my BizTalk server to the SQL server, however when I ran the test from the SQL server to the BizTalk server I got an error saying “gethostbyname failure – Can not resolve xxx Invalid remote host” where xxx was the NetBIOS name of my BizTalk server.  The reason for this is that the BizTalk server NetBIOS name was not known to the SQL server (the SQL server NetBIOS name was known to my BizTalk server in my case but if it wasn’t then the following fix would be needed on both servers) so I had to add an entry relating my BizTalk server’s NetBIOS name to its IP address in the HOSTS file on the SQL Server (the HOSTS file can typically be found in “C:\Windows\System32\Drivers\etc”).  Once this was done I was able to successfully connect in both directions using DTCPing.  A quick and easy way to find out whether a NetBIOS name is known to a machine or not is to open a command prompt and type in “ping {NetBIOS name}”.  If that doesn’t work but “ping {IP address}” does work then chances are that this step is necessary for you.

Even though DTCPing was now returning a successful result I found that my BizTalk receive location was still exhibiting the same behavior with ambient transactions turned on.  My next troubleshooting step was to download the DTCTester tool.  DTCTester is a command line utility which only needs to be run on the server that is enlisting the transaction (the BizTalk server in my case) unlike the DTCPing tool however, it does require you to setup an ODBC data source corresponding to your target, in my case the SQL server database (see this article for more information on creating a SQL Server targeted ODBC data source).  You can execute the DTCTester tool by opening a command prompt window, browsing to the directory where you extracted the executable, and typing in “dtctester.exe {ODBC data source name} {username used to connect to data source} {password used to connect to data source}”.  In my case I got a very generic error message as below, which didn’t really help me solve my problem but did prove to me (not that I needed much convincing) that the problem still remained in the DTC layer.

I will admit that the very last change I had to make to fix the problem was arrived at through a process of trial and error.  I had to drop down the minimum authentication level required for DTC from the previous value of “Incoming caller authentication required” as was set on both servers to “No authentication required” as below.

I did discuss this removal of authentication requirements with the security architect at my client site before making the change to ensure it wouldn’t be a problem for them and compromise their security.  After reviewing a few articles such as this one describing the potential risks of using unsecured DTC, and this one describing DTC security, we decided that seeing as the externally facing firewall blocked any external DTC connection attempts to both the BizTalk server and the SQL server this security setting was acceptable on the test environment only.  It would be more desirable for the SQL server to be brought onto the domain however project timelines did not allow for this level of disruption so this alternative was discounted, even though it was preferable from a security and architecture perspective.  It was also deemed that the differing authentication levels for MSDTC between the test environment and other environments was unlikely to introduce any functional differences to the BizTalk applications deployed there.

The changes I made might or might be enough to fix your specific problem, but at the very least I hope this blog post expresses what tools are available to troubleshoot such problems and what thought process needs to be employed to get MSDTC working for the WCF-SQL adapter and in general when one or both of the machines in question are not on a domain.