SQL Adapter Trouble Explained

I spent all morning today with a coworker at one of our clients diagnosing a problem deploying a BizTalk Server 2004 solution on a test environment. The problem in question was that when running BTSDeploy.exe on the binding file that contained all the send and receive port definitions, it kept failing when trying to create a Send Port that used the SQL adapter with the following error message:

Error: Failed updating Binding information.
BindingException: Could not validate TransportTypeData or Address properties
for Primary Transport of Send Port '<port name here>'.
Exception from HRESULT: 0x80131500

We looked everywhere to see where the error was coming from. First, I checked the biding file to see if the TransportTypeData element contained a valid value, and after a lot of checking we concluded it was indeed correct. Then we checked that the user had access to the SQL Server database the port referenced; it had. We also checked that all servers had network transactions enabled (they were all Windows Server 2003 machines), and found out that the SQL Server machine didn't have that, so we fixed it, but we still couldn't create the ports.

We also found this KB article that referenced this error message and went ahead and applied BizTalk SP1 as well (which we hadn't done yet). The scenario referenced in the KB article wasn't exactly the same one as ours (and indeed it wasn't), but it didn't hurt to try, anyway.

Now, in this particular case, the setup consisted of two physical machines; let's call them A and B. A had all the BizTalk databases and was also running a virtual machine on VPC (A1) which ran BizTalk Server itself. B had SQL Server installed with the remote DB referenced by the problematic Send Port, but also had a virtual machine running on it on VPC that had other stuff not directly relevant.

After much checking, we finally diagnosed the problem. A1 was not seeing B (nor B1) through the network, though it could see every other machine out there. However, A could see B fine. We also noted that B1 couldn't access A or A1 either, but B could see A fine! So we checked network settings, changed IP addresses, everything, and it didn't help.

In desperation, we just switch off virtual machine A1 and voila! B1 now could see machine A on the network. This was the clue we needed and after checking, we realized that somewhow, A1 and B1 (the two virtual machines) had ended up configured with the same MAC addresses on the virtual network cards, so they would conflict with one another. We just switch off the machines and then changed the MAC address by hand editing the .VMC file for the machines, and, once that was done, the problem went away and we BTSDeploy ran successfully creating all ports.

So, the first lesson of today is: When creating SQL adapters, always make sure that you can access the database server correctly; it will save you a lot of grief.

The second lesson here is that BizTalk could make it a lot easier to diagnose the situation. The problem here is that it is not evident that the SQL adapter will actually try to connect to the databse when you create the port, so it was not obvious first-hand that this was the cause of the problem. Also, HRESULT 0x80131500 is a very generic error code, meaning just COR_E_EXCEPTION. In other words, all it tells you is that something deep down the guts of what BTSDeploy was doing threw a managed exception, but it doesn't actually go ahead and tell you the exception's error message, and it also doesn't leave anything in the machine's event log one can use to diagnose the issue. This is something I would definitely like to see improved in future versions of the product.