It’s been quite a while since I’ve done a BAM installation or really worked with it in any meaningful fashion. Recently our team did an install/configure of BAM and attempted to look at the portal to find the typical “something went wrong, better check the logs” type exception. For whatever reason, it appears that we were unable to execute one of the startup stored procedures in the BAMPrimaryImport database.
When we looked into the service logs, here’s the message that we found:
Microsoft.BizTalk.Bam.Management.BamManagerException: Encountered error while executing command on SQL Server "SERVER_NAME". ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'bam_Metadata_GetConfigurationXml', database 'BAMPrimaryImport', schema 'dbo'.
When looking at the stored procedure in SQL Server, I found that only one role had execute rights: BAM_Management_WS. Our BAM Portal App Pool was running under our BizTalk server account which was the only member of the role. This was odd as the same type of configuration existed on a number of successful installs of BAM and the BAM Portal on other servers.
We solved the problem by simply adding BizTalk Server Application Users group and, for good measure, the BizTalk Server Administrators group to the membership for the BAM_Management_WS role. However, we still do not know why this was required for this particular server.
If you have any insight, please feel free to comment.