Monday, March 18, 2013

Execute Permission denied on object 'bts_ProcessHeartbeat_....'

It's always worth reposting a useful tip. This one was written by Paul Edwards in 2010 and I came across the problem recently. It occurs when you are using a Host Instance for an Isolated Host.  The full text of the error is

The following stored procedure call failed: " { call [dbo].[bts_ProcessHeartbeat_BizTalkServerIsolatedHost]( ?, ?, ?)}". SQL Server returned error string: "EXECUTE permission denied on object 'bts_ProcessHeartbeat_BizTalkServerIsolatedHost', database 'BizTalkMsgBoxDb', schema 'dbo'.".

The background is when you create a new Host then not only is the Host added to the BizTalk Management database but it also creates a stored procedure called 'bts_ProcessHeartbeat_'.
A new Database Role is also created called _USERS which includes the AD Group Name you specified when creating the Host.  When you create a Host Instance the Login account you use needs to be a member of this group in order to be granted Execute permissions.

But here's the issue: when using an Isolated Host (for recieve handlers of SOAP and HTTP), the stored procedure is called by the Application Pool account that your web site or virtual directory is running under. So you either need to make this account the same as the Host Instance login account or add it to the group you used when creating the Host. 

No comments: