I recently went through the process of deploying a BAM model to our new BizTalk infrastructure, and learned a few things about BAM security and deployment along the way.
Given that most BizTalk architects/developers probably play with BAM on a single fully installed machine (BizTalk, SQL, IIS, etc) while running with highest-level permissions, sometimes certain steps can be taken for granted.
To start with, my production environment contains clustered SQL Server 2005 servers and a specific SQL instance created for the BizTalk databases. Both SQL Server Analysis Services and SQL Server Integration Services are installed in the cluster as well. If you have this sort of environment, you’ll need to modify SSIS before continuing. Specifically, you must change the MsDtsSrvr.ini.xml file so that the SSIS points to a named instance of SQL Server (see the Microsoft documentation for details). If you do NOT do this, then running the bm.exe BAM tool will result in everything LOOKING fine, but in fact, no SSIS/DTS packages get created anywhere.
Now, to run the bm.exe, which builds up all the BAM infrastructure (tables, procedures, triggers, packages, cubes, etc), you have to have SSIS on the same machine as the tool itself. Got that? So you can’t run this from a standalone BizTalk box and expect it to work. Another option (instead of installing SQL tools on the BizTalk box) is to install the BAM tools alone on the SQL box. I’ve reviewed a few things, and am fairly sure this is the case, but if anyone wants to challenge that assertion, feel free.
Let’s talk about security. Specifically, adding users to views. Again, most times when you’re developing BAM solutions, you take for granted that you can go to the BAM Portal and views magically appear. But when you’re not running as an Administrator, those views aren’t visible by default. What I did (as practice before doing this in production), was create a local group in my development environment. Then, I ran the following command:
This command does a few things. First, it adds that user/group to the BAMPrimaryImport table as a “user.”
This allows anyone in that group to look at data in the BAM view. If your BAM model contains aggregations, then SQL Analysis cubes get created by the bm.exe tool. A new “role” gets created for you in SQL Analysis services as well …
Now by default, this group is empty. But when you run the “add-account” command, the designated user/group ALSO gets added here.
Nice! So instead of doing “add-account” for every individual user, you should require a group to be set up for a particular BAM deployment. If a user tries to view the BAM Portal and is NOT in the designated group, they’d see this …
As soon as I add the logged on user to the pre-defined Windows group (with explicit BAM access), the same user sees this …
Cool stuff that I haven’t found demonstrated much. I personally spent hours trying to find out why my freakin’ SSIS jobs wouldn’t get created, so the whole “change the obscure SSIS configuration file” might save someone time later on. Any other BAM deployment points folks want to add?
Technorati Tags: BizTalk
Richard Seroter's Architecture Musings 
Nice Tip
This information is very helpful. I am stuck in similair situation with a client. We have SQL database on cluster and Biztalk servers on web farm. You correctly stated that these issues do not show up in a normal stand alone scenario. It will definitely save some time. Thanks!
Richard,
We created the BAM wizard for deployments and security purpose for production. It is awesome. Hoping new BAM beginners would get good ideas from here.
Do you have any article for integrating BAM with SharePoint?
Please suggest me.
Thanks,
Raja Kumaravel
Hi Raja,
I don’t have any specific articles on the topic, but consider looking at using SharePoint plus SQL Reporting Services to surface up the data. You certainly don’t need the BAM portal, and can instead use the rich feature set of Reporting Services instead.
One Note: in the blog you show using a “Local” or machine based account. This is a bad thing to do. Since BAM can deploy parts of a view to SQL and OLAP on different machines you will run into problems.
Always use Domain accounts with BAM..
And to your point that you should use Groups as the main security principle (i.e add people to group, and grant access to the group), that is exactly correct. I have seen issues with granting access to a user, removing that user from AD without first removing them from the view access. If you use a group, AD will hanle those icky bits.
Hi Brad,
Thanks for the comment. Indeed, since this was a demo on a non-networked machine, my screenshots use local accounts. Our actual deployments of course use AD domain accounts.
Once things are deployed, and you want to change the length of a data item in a view: there’s an unsupported way to do it. Check out
http://social.msdn.microsoft.com/Forums/en-US/biztalkinterceptors/thread/bbd516f9-0045-4731-86d1-3d712cb9adff/
This piece also gives a nice overview of what is happening under the hood at the database level. Tinker at your own risk!
Hi Richard,
I actually am running through the same problem but with BizTalk 2010 and SQL Server 2008 SP2. The environment is a little bit different, I have one sql server for Database Server and another for Analysis Server, so when I configure BAM, the BAMAnalysis Database gets created on the Analysis Server. I am very confused with the SSIS, because I don’t know which one uses the bm.exe command, since the SSIS is installed on both, the Database Server and the Analysis Server, so in which server do I have to change the MsDtsSrvr.ini.xml file? I have named instances for both also (database and Analysis). Also, what happens with the actual packages running in the default instance if I point to a named Instance?
Hi Sonia,
It’s been a while since I”ve looked at this, but wouldn’t it be the SSIS on the machine that you run bm.exe? You may also want to try asking on the BizTalk forums http://social.msdn.microsoft.com/forums/en-US/biztalkgeneral/threads/