BAM Alerts Failure Setting Up

Many people, including many experienced BizTalk Developer, do not know about or fully understand BAM and in particular BAM Alerts.

If you know your stuff, you will know it’s VERY useful, not only because it is out of the BOX, but it is something that can be very useful.

I have experienced some problems setting it up, or getting it actually working on BizTalk 2013, finally they are using the right version of SQL, and the right version of everything else, sadly the BizTalk Team, are not so great at developing, and they cause issues, or rather don’t develop and then test their stuff correctly.

So I found some bugs, and I will provide you the Fixes.

When creating a BAM Alert, you may get a failure on the screen, something didn’t work. On closer inspection to the event log you see something like the following.

System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 'bam_Metadata_GetUserAlertInstances', database 'BAMAlertsApplication', schema 'dbo'.

It seems the setup of the BAMAlerts database was not fantastic, In that no one has access to it, to create alerts, not just to this stored procedure, but to several….

A quick solution is to grant permissions to these stored procedures, as you go you find that this one now works, and the next one fails, so to avoid this problem, here is the script I used, and ALL of the offending stored procedures, as our BAM system is open to the public user base, we grant everyone access, as the information is very valuable.

Against the BAMAlertsApplication database.

GRANT EXEC ON [dbo].[bam_Metadata_GetUserAlertInstances] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_GetAlertInstanceByViewName] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_InsertAlertInstance] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_InsertAlertOwner] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_GetAlertInstanceID] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_GetUserAlertInstance] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_GetAlertSubscriptions] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_CreateSubscription] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_UpdateAlertInstanceXml] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_IsAlertOwner] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_GetAlertSubscriptionIDfromNames] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_GetSubscription] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_DeleteSubscription] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_GetAlertOwners] TO PUBLIC

GRANT EXEC ON [dbo].[bam_Metadata_UpdateAlertInstance] TO PUBLIC

 

This will now enable you to create and edit an alert, add a subscriber, and remove a subscriber. As you can see there are MANY procedures that permission was simply not granted, very annoying.

Once this is done, you find you have yet another problem…… if your domain, like mine, has usernames that contain spaces, in that {Domain}\Paul Somers. The BAM alerts system does not like you. It will complain because it can’t handle a space, even if I put this in quotes, it still does not work. 

The following owners could not be resolved: {Domain}\Paul; Somers. Correct the list of owners and try saving the alert again.


Use the navigation bar on the left to access Business Activity Monitoring views.
If the problem persists, contact your System Administrator.

Again VERY annoying, this I could not find a fix for, but the work around is to use a user with no spaces, to create the alert; our service account users have no spaces, so I used this rather.

All in all, the BizTalk team is making what is a fantastic feature, and very valuable ability of BizTalk, less than acceptable. If I did not know the power of BAM, and the true awesomeness of BAM, BAM Portal, and BAM alerts, I would be put off from ever using it….

I say to all, use it, it’s truly awesome, if you need help come talk to me, my contact details are paul@somers.com.