All right!  The point of this post is going to bore 90% of my audience so before I get started, I'd like to mention how *awesome* it is that the Iron Man movie now stars Robert Downey Jr. *and* Terrence Howard.  For those of you not familiar with the Iron Man concept, he's like any other superhero except he is also a raging alcoholic.  Frankly, I'm a little surprised that they didn't cast the Igloo Coder for this role - not "millionaire playboy" enough?  Anyway, to tie this together into a rich  tapestry, the below image is a good approximation of how I felt while debugging the issue I talk about below:


The rest of this post most of you can safely ignore - those of you who like Biztalk keep reading.

Recently I was working on a Biztalk orchestration that "listened" for changes in a remote SQL Server database - once these changes occured, Biztalk would jump in and rip out the corresponding data.  The only problem?  Biztalk stopped responding to any changes in the DB.  After some brief searching, I thought I had found the problem; hey, the receive location is disabled!  Just enable it and things should be fine, right?

Nope!

Re-enabling the port was shortlived - after about 5 minutes the port would close itself again.  This is actually a feature in Biztalk - when multitudes of errors start occuring, Biztalk shuts down the port.  Checking the Event Viewer only told us:

a) The receive location "SQL://LOCATION_TOO_HOT_FOR_THIS_BLOG" is shutting down. Details:"The error threshold has been exceeded. The receive location is shutting down.".  Not too helpful.
 
b) The adapter "SQL" raised an error message. Details "New transaction cannot enlist in the specified transaction coordinator".  Now *this* one is interesting - since Biztalk relies on DTC, was there a problem with DTC?  

Doing some searching found me this blog post, which actually talked about Biztalk *installation* problems when Windows 2003 Service Pack 1 was installed and Biztalk was trying to use a remote database.  It turns out that the service pack makes some changes to MSDTC that you need to be aware of if you are running Biztalk 2004.  

If you've been hit with Windows 2003 Service Pack 1 and your SQL receive locations are failing to respond, you might want to make the following tweak (attached screenshot also courtesy of that blog post):


You get here by going into Component Services -> My Computer (properties) -> DTC tab, Security Configurations.  Note: if the tab looks *entirely* different than what you see above, Win2003 SP1 is likely *not* your problem here (this is a change that takes affect when SP1 is installed).

When you are here, ensure that Client and Administration/Allow Remote Clients is checked!  In some cases you apaprently also need to change your Transaction Manager Communication to "No Authentication Required", although I keep thinking this is opening a security hole for some reason...

This post is mostly for my own edification but if it manages to save even one person from travelling down this abyss it will have been worth it...