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’

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 track the problem but we were able to get more details on 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 was defined:

bam_Metadata_GetProperty stored procedure permissions

Cause

These problems occur 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 issues in the BizTalk Server 2013 release that was fixed in the Cumulative Update 1, especially 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 on 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 the “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
Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

2 thoughts on “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’”

  1. 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

Your email address will not be published. Required fields are marked *

turbo360

Back to Top