Production-Ready BAM Security and Deployment

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:

bm.exe add-account -AccountName:”machine\ProjectBAMUsers” -View:viewname

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:

Author: Richard Seroter

Richard Seroter is Director of Developer Relations and Outbound Product Management at Google Cloud. He’s also an instructor at Pluralsight, a frequent public speaker, the author of multiple books on software design and development, and a former editor plus former 12-time Microsoft MVP for cloud. As Director of Developer Relations and Outbound Product Management, Richard leads an organization of Google Cloud developer advocates, engineers, platform builders, and outbound product managers that help customers find success in their cloud journey. Richard maintains a regularly updated blog on topics of architecture and solution design and can be found on Twitter as @rseroter.

11 thoughts

  1. 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!

  2. 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.

    Raja Kumaravel

  3. 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.

  4. 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.

  5. 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.

  6. 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?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.