BAM Pitfalls with BizTalk Server 2013
Recently I was working on implementing some BAM Views for a customer who is using BizTalk Server 2013. During the setup of the BAM Views on my development machine I ran into some issues. In this blog post I’ll point them out, give some background information and provide you with solutions.
Don’t use the BizTalk 2013 version of BAM.xla
One of the first steps you’ll take when working on BAM is defining the data you’re interested in by defining Business Activities using the BAM.xla Excel add-in. You can read more about this part of building BAM solutions on MSDN. This first step is where I ran into the first issue.
You start creating BAM solutions by opening Excel, enabling the Add-in in the Excel ribbon and start defining the desired Business activities. The BAM add-in, BAM.xla, is included in the installation bits of BizTalk Server 2013. However, when you try to enable this Add-in you’ll get the following error:
“Could not load an object because it is not available on this machine”
Even after applying BizTalk Server 2013 Cumulative Update 2 the problem still exists. Other users have reported the same issue. It can be fixed by taking the BAM.xla from BizTalk Server 2010 (or BizTalk Server 2013 Beta).
BAM.xla and Excel 64-bits doesn’t work
When you try to add the BAM.xla to the ribbon of the 64-bits version of Excel (2010 or 2013), you get the following error:
As stated, the code of the BAM.xla add-in must be updated for use on 64-bits systems. What the error message actually means is that you’re trying to use the 64-bits version of Excel. BAM.xla is not compatible with this version of Excel.
The error further states that you should update the Declare statements in the code of BAM.xla and mark them with the PtrSafe attribute. But even when you do that you will still get compilation errors, like missing references, and you still won’t able to load non available objects (see below).
The official documentation of BizTalk Server states that only the 32-bits version of Microsoft Office/Excel is supported, so obviously the simplest solution is to uninstall the 64-bits version of Excel and install the 32-bits version.
BAM Portal doesn’t like SQL Server in Mixed Authentication Mode
This issue is not exclusive to BizTalk 2013, but as I ran into it while working with BizTalk 2013 I thought I’d mentioned it anyway.
After you have installed and configured BAM and you have deployed a view, you open the BAM Portal. Even without having deployed a Tracking Profile you should be able to see the view. However in the left hand side pane the following error shows up:
“Views or Activities may be missing because one or more database(s) could not be contacted”
This behavior is described in more detail in this article, but I’ll just briefly describe the general approach to finding and fixing the root cause:
First you’ll have to check the Event Viewer for related entries. You’ll find an entry which describes that the BAMPrimaryImport database is not accessible due to a NullReferenceException.
Next you’ll have to enable tracing for the BAM Web Services. Do this by opening the web.config file in folder C:\Program Files\Microsoft BizTalk Server 2013\BAMPortal\BamManagementService and uncomment the area. After refreshing the BAM Portal the trace file will contain more information about the root cause. By default the trace file can be found in C:\Temp\BamManagementServiceTrace.log. In the trace file you’ll find the root cause:
“ThrowHelperSoapException Error 11007 Cannot determine the SID for the user”
In the background the BAM Web Services will try to find the Windows identity for the dbo user in SQL Server, as this user has permissions to all BAM Views. However this will fail if that’s a SQL account.
You can fix this problem by changing the dbo accounts for all BAM databases to Windows accounts. To do this you should run the following script:
use BAMPrimaryImport go sp_changedbowner 'domain\user' go
Now you can refresh the BAM Portal again to check if the BAM Views are now shown correctly.
Use BAM Portal with Internet Explorer 10 in Compatibility Mode
When using Internet Explorer 10 for viewing the BAM Portal you will notice that, in the Column Chooser section, you’re not able to move columns to the Items to show List Box. You should check if you are running Internet Explorer in Compatibility Mode.
Compatibility Mode is enabled by clicking on the small ‘broken page’ icon in the address bar.
This is a known issue that is described in the What’s New in BizTalk Server 2013 article on MSDN.