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.

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:

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”

• Click “OK”.

Alternative you can use the following SQL Query:

USE BAMPrimaryImport
GRANT EXECUTE ON OBJECT::bam_Metadata_GetProperty
    TO BAM_ManagementNSReader;
GO