When we recently configured the ESB Portal website, we encountered a number of permissions-related issues. Our initial experience was the same as that of many others who have discovered that the Portal’s included permissions script is inadequate. Once we granted additional permissions to the existing database roles the permission errors cleared up – but we couldn’t overcome one last error: Invalid object name ‘BizTalkMsgBoxDb.dbo.ProcessHeartbeats’.
As most of you know, Microsoft decided not to ship the source code for the ESB Toolkit 2.0 aside from the Management Portal “sample”. In order to diagnose this error, I pulled out Red Gate’s .NET Reflector and started digging through disassembled code. The source of this particular issue lies in the ESB.BizTalkOperationsService.
In our environment, as in most high-performance BizTalk installations, the message box database is on a different SQL Server instance than the other BizTalk databases. In a great oversight, the BizTalkOperationsService was hard-coded to expect the message box database to be present on the same server as the management database. The operations service attempts to run this SQL query on the database that holds the management database: SELECT 1 FROM BizTalkMsgBoxDb.dbo.ProcessHeartbeats with (nolock) where uidProcessID='{0}’.
You’ll note another potential issue here: the message box database name is hard-coded in the query. That has also caused trouble for people.
To solve this problem, I first used .NET Reflector to re-create Visual Studio 2008 projects for the ESB.BizTalkOperationsService ASMX web service and Microsoft.Practices.ESB.BizTalkOperations.dll class library. Once the projects were cleaned up and building successfully, I modified the code to query the management database for the primary message box database name and server using the existing stored procedure adm_MessageBox_Enum. With that information in hand, I updated the code to create a connection string to the message box database and execute the ProcessHeartbeats query there. I also removed the hard-coded database name.
I tested my version of the BizTalkOperationsService using the ESB.BizTalkOperations.Test.Client included with the Toolkit source code and verified that everything still worked as expected.
Since this was a fairly time-consuming issue to fix and it is a problem that should affect a good percentage of the installations out there, I decided to post my updated service and source code (download link at the end of this post). I cannot make any guarantees about the correctness of the code, so consider it as-is and use at your own risk. (That said, I believe that it works just fine.)
Let’s hope that Microsoft reconsiders its unfortunate decision not to ship source code.
