Tag Archive: BizTalk ODBC Adapter

In the last week I have already helped out with two issues on a BizTalk 2010 to 2013 upgrade project that had the same root cause and I thought it was worth documenting this so as to save others the headache of having to figure this out themselves.

BizTalk 2013 contains a new feature in that you can now nominate which send handler is used for which adapter on a given dynamic send port.  This has resulted in the creation of a new SQL table, bts_dynamic_sendport_handlers in the BizTalkMgmtDb database, and with a fresh BizTalk 2013 installation the necessary SQL roles are given the appropriate permissions on this table.  That said things might (I say might because on in some upgrade scenarios we have seen all the permissions applied correctly and in some we haven’t, thus far we can’t account for the different behavior) go a bit differently when doing an upgrade of BizTalk 2010 to 2013… while the new table is created, none of the existing roles appear to be extended to have permissions on this new table (though as I stated on some upgraded environments this was not an issue at all).  Microsoft have written an article about this problem but unfortunately unless you know exactly what the problem is chances are you will not be able to find it (they mention another table as well but thus far that has not been a problem for us).

This issue can have major effects on multiple aspects of BizTalk such as the ESB portal, the BizTalk Community ODBC adapter, or pretty much any utility that makes use of classes (in our specific case it was the BtsCatalogExplorer class) in the Microsoft.BizTalk.ExplorerOM assembly which allows for the traversal of the BizTalk object model. This in theory would extend to the BizTalk documenter, the Orchestration Profiler, and the BizTalk PowerShell provider though I haven’t proven this yet.  Unfortunately the error messages aren’t always very obvious and understanding the root cause of the problem with the ODBC adapter required us to debug the code and it was only when we stepped into the lowest levels that we realized that the BtsCatalogExplorer was failing to populate itself.

On the ESB Portal front the error message says “Server was unable to process request. —> The source was not found, but some or all event logs could not be searched.  Inaccessible logs: Security.

ESB Portal Failure

On the community ODBC adapter the problem would present itself with a cryptic error message stating “The type initializer for ‘Microsoft.BizTalk.Adapters.ODBC.RunTime.ODBCTransmitAdapterProperties’ threw an exception”.  Note that this problem was only seen by us on ODBC send ports.  Receive locations (at least one way receive locations) appeared to work just fine .

ODBC error

The permissions that are required against the bts_dynamic_sendport_handlers table are delete, insert, select, and update for the BTS_ADMIN_USERS role, and select only for the BTS_HOST_USERS and BTS_OPERATORS role.

It is also quite possible that post the upgrade none of your existing applications will suffer any impacts to their functionality if there are no dependencies on dynamic send ports or the Microsoft.BizTalk.ExplorerOM assembly and it might be a while before you actually encounter this issue.  The problem might also be hidden on development environments if the user in question (be it your BizTalk host instance user / application pool identity / application credentials) is in the sysadmin SQL server role on the BizTalk SQL server and thus the problem might not be observed till an application is migrated to the next environment.

The moral of the story is that if you are going to upgrade your BizTalk 2010 environment to 2013 do not forget to provide the relevant permissions to the bts_dynamic_sendport_handlers table.

We recently hit a problem on a project we were working on whereby we were forced to switch from using a native BizTalk database adapter to using the third-party Community Adapter for ODBC which has been refactored for BizTalk 2010 by TwoConnect.  Having access to the source code allowed us the opportunity to work around a very specific issue while we hold out the hope that Microsoft applies a permanent fix to the problem for us (I’ll blog about this issue soon).

This has been my first time dealing with the ODBC adapter for BizTalk and I must say that while it is far from perfect and does have limitations (just search for “TODO” in the source code and you’ll see that there is much room for improvement), it has far exceeded my initial expectations and contains a rather elegant project structure which enabled me to make my required changes in a structured and sensible manner, and also contains some pretty competitive schema generation and testing tools.

One major issue I had however was that when I deployed the adapter using the MSI that was built using the install project that is included with the source code, I started seeing the below error every time I tried to start my receive locations.

The Messaging Engine failed to create the receive adapter “ODBC”.
InboundAssemblyPath: “NULL”
InboundTypeName: “Microsoft.BizTalk.Adapters.ODBC.RunTime.ODBCReceiveAdapter.ODBCReceiver, ODBCAdapterManagement, Version=, Culture=neutral, PublicKeyToken=0ad1f077efbaab97, processorArchitecture=MSIL”
Exception Details: “Could not load type ‘Microsoft.BizTalk.Adapters.ODBC.RunTime.ODBCReceiveAdapter.ODBCReceiver’ from assembly ‘ODBCAdapterManagement, Version=, Culture=neutral, PublicKeyToken=0ad1f077efbaab97, processorArchitecture=MSIL’.”

Luckily I managed to find a comment on a blog post (thank god for the blogosphere) which pointed me in the right direction.  A user called “dawa” pointed out that the wrong InboundTypeName and OutboundTypeName were registered in the dbo.adm_Adapter table in the BizTalkManagementDb database.  The InboundTypeName really should be “Microsoft.BizTalk.Adapters.ODBC.RunTime.ODBCReceiveAdapter.ODBCReceiver, ODBCReceiveAdapter, Version=, Culture=neutral, PublicKeyToken=0ad1f077efbaab97, processorArchitecture=MSIL” but is instead “Microsoft.BizTalk.Adapters.ODBC.RunTime.ODBCReceiveAdapter.ODBCReceiver, ODBCAdapterManagement, Version=, Culture=neutral, PublicKeyToken=0ad1f077efbaab97, processorArchitecture=MSIL”.  Likewise the OutboundTypeName should be “Microsoft.BizTalk.Adapters.ODBC.RunTime.ODBCTransmitAdapter.ODBCTransmitter, ODBCTransmitAdapter, Version=, Culture=neutral, PublicKeyToken=0ad1f077efbaab97, processorArchitecture=MSIL” but is instead “Microsoft.BizTalk.Adapters.ODBC.RunTime.ODBCTransmitAdapter.ODBCTransmitter, ODBCAdapterManagement, Version=, Culture=neutral, PublicKeyToken=0ad1f077efbaab97, processorArchitecture=MSIL”.

Now I’m as brave as the next person, but making changes to the BizTalk management databases on non-development environments should really be a no-no.  If you actually open the Install.vdproj file using a text editor you’ll find that you can adjust the InboundTypeName and OutboundTypeName.  Once this is done you can rebuild the MSI and it will now behave correctly.  If however you have already previously installed the ODBC Adapter and have encountered this error, you will need to uninstall the ODBC Adapter from the Add/Remove Programs control panel and you will also need to deregister the ODBC Adapter in the BizTalk Administration Console by browsing to Adapters within Platform Settings and deleting the ODBC Adapter (you will need to ensure that all receive locations and send ports that use the ODBC adapter have been deleted first).  You can then install the fixed MSI and register the ODBC adapter again, and it will now contain the correct InboundTypeName and OutboundTypeName.

I don’t believe that this problem exists with the MSI file that is published on TwoConnects website as I didn’t face this problem until I installed an MSI built from the source code (I had originally just installed the MSI from their website).  It only appears to be in the source code that they have published.  I will advise them of this problem but thought I better document it before it catches the next unwary developer off guard.

%d bloggers like this: