Microsoft.BizTalk.Bam.Management.BamManagerException: Encountered error while executing command on SQL Server "servername". —> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘bam_Metadata_GetProperty’, database ‘BAMPrimaryImport’, schema ‘dbo’

Posted: February 5, 2014 in BizTalk
Tags: , , , ,

Last week while making a simple BizTalk Server 2013 multi-computer environment (1 SQL Server and 1 BizTalk Server both running Windows Server 2012) configuration I faced several issues to properly configure BAM Portal, this was the first one was described in my previous blog post. This is the second one:

By solving the problem described in my previous post I was able to properly configure all the features in BizTalk Server: Enterprise SSO, Group, BizTalk Runtime, Business Rules Engine, BizTalk EDI/AS2 Runtime, BAM Tools and BAM Portal without any more problems, however we notice that the BAMAlerts service was logging several errors in the Event Viewer and was constantly stopping.

BAMAlerts-Service-stopped

When we check the Event Viewer we were able to see the following messages:

Encountered error while executing command on SQL Server “server name”.
Event ID 0

Of course this was the generic message and doesn’t give us the real cause and doesn’t help us tracking the problem but we were able to get more details in the rest of the logs registered in the Event Viewer:

at Microsoft.BizTalk.Bam.AlertHost.Utility.GetProperty(String dbServer, String dbName, String propertyName, String scope)
at Microsoft.BizTalk.Bam.AlertHost.AlertHost.GetBAMMetadataProperties()
at Microsoft.BizTalk.Bam.AlertHost.AlertHost.Run()

Unhandled exception caught. Stoppping the service.

And finally:

Microsoft.BizTalk.Bam.Management.BamManagerException: Encountered error while executing command on SQL Server “servername”. —> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object ‘bam_Metadata_GetProperty’, database ‘BAMPrimaryImport’, schema ‘dbo’.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at Microsoft.BizTalk.Bam.Management.SqlHelper.ExecuteScalar(String cmdText, CommandType cmdType)

When looking at the “bam_Metadata_GetProperty” stored procedure in the database “BAMPrimaryImport” database, we found that no role were defined:

bam_Metadata_GetProperty-stored-procedure-permissions

CAUSE

These problems occurs because the BizTalk Server Configuration Tool didn’t properly configure the right permissions in this stored procedure.

As I described in my previous post, there are some known issue in the BizTalk Server 2013 release that was fixed in the Cumulative Update 1 specially this one regarding BAM Tools: KB article 2832137 – FIX: BAM tools cannot be configured in a multi-node BizTalk Server 2013 environment. I know is not exactly the same error, nevertheless, I think these problems are related to this configuration issue. So again I advise that before you try to configure BizTalk Server 2013 you should apply the CU’s available.

SOLUTION

Again I advise that before you try to configure BizTalk Server 2013 you should apply the CU’s available.

However in my case to solve the problem I had to manually configure the correct permissions on this stored procedure, luckily I have other environments where I can check to see the correct permissions, so to solve this you need to:

  • Expand a server group, and then expand a server.
  • Expand “Databases”, expand the BAMPrimaryImport database, and then expand “Programmability”.
  • Expand “Stored Procedures”, right-click in the “bam_Metadata_GetProperty” procedure to grant permissions on, and then select “Properties” option.
  • From Stored Procedure Properties, select the “Permissions” page.
  • And to grant permissions to a user, database role, or application role you must click “Search” button.
    • In this case we need to give execution privileges to the database role “BAM_ManagementNSReader”

bam_Metadata_GetProperty-stored-procedure-right-permissions

  • Click “OK”.

Alternative you can use the following SQL Query:

USE BAMPrimaryImport
GRANT EXECUTE ON OBJECT::bam_Metadata_GetProperty
    TO BAM_ManagementNSReader;
GO
Comments
  1. Divya says:

    This article is great. thanks for posting it.
    I spent a whole day searching for the solution. Your solution worked for me. Thank you

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s