We were seeing this exception on two servers when we tried to access the BAM Portal... after having to reconfigure the BAM Portal and Tools for reasons unrelated to the error:
---
Log Name: Application
Source: Bam Web Service
Date: 2/18/2011 10:24:07 AM
Event ID: 1534
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: yyyyyyyyyyyyyyyyyyyy
Description:
Current User: yy\yyyyyyyy
EXCEPTION:
Microsoft.BizTalk.Bam.Management.BamManagerException: Encountered error while executing command on SQL Server "yyyyyyyyyyyyyyy". ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'bam_Metadata_GetConfigurationXml', database 'BAMPrimaryImport', schema 'dbo'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.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.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.BizTalk.Bam.Management.SqlHelper.ExecuteQuery(String cmdText, CommandType cmdType, Transaction transaction)
--- End of inner exception stack trace ---
at Microsoft.BizTalk.Bam.Management.SqlHelper.ExecuteQuery(String cmdText, CommandType cmdType, Transaction transaction)
at Microsoft.BizTalk.Bam.Management.BamConfigurationManager.GetConfigurationXmlFromPrimaryImportDb()
at Microsoft.BizTalk.Bam.Management.BamConfigurationManager..ctor(String piServer, String piDatabase, Int32 sqlHelperCmdTimeout, Boolean validateServerNames)
at Microsoft.BizTalk.Bam.Management.BamManager..ctor(String primaryImportServer, String primaryImportDatabase, Int32 sqlCmdTimeout, Boolean validateServerNames)
at Microsoft.BizTalk.Bam.Management.BamManager..ctor(String primaryImportServer, String primaryImportDatabase)
at Microsoft.BizTalk.Bam.WebServices.Utilities.FetchBamManager()
at Microsoft.BizTalk.Bam.WebServices.Management.BamManagementService.GetViewSummaryForCurrentUser()
EXCEPTION:
Microsoft.BizTalk.Bam.Management.BamManagerException: Encountered error while executing command on SQL Server "yyyyyyyyyyyyyyyyyy". ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'bam_Metadata_GetConfigurationXml', database 'BAMPrimaryImport', schema 'dbo'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.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.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.BizTalk.Bam.Management.SqlHelper.ExecuteQuery(String cmdText, CommandType cmdType, Transaction transaction)
--- End of inner exception stack trace ---
at Microsoft.BizTalk.Bam.Management.SqlHelper.ExecuteQuery(String cmdText, CommandType cmdType, Transaction transaction)
at Microsoft.BizTalk.Bam.Management.BamConfigurationManager.GetConfigurationXmlFromPrimaryImportDb()
at Microsoft.BizTalk.Bam.Management.BamConfigurationManager..ctor(String piServer, String piDatabase, Int32 sqlHelperCmdTimeout, Boolean validateServerNames)
at Microsoft.BizTalk.Bam.Management.BamManager..ctor(String primaryImportServer, String primaryImportDatabase, Int32 sqlCmdTimeout, Boolean validateServerNames)
at Microsoft.BizTalk.Bam.Management.BamManager..ctor(String primaryImportServer, String primaryImportDatabase)
at Microsoft.BizTalk.Bam.WebServices.Utilities.FetchBamManager()
at Microsoft.BizTalk.Bam.WebServices.Management.BamManagementService.GetViewSummaryForCurrentUser()
---
We reconfigured the BAM Portal and Tools multiple times, trying to fix this issue, but kept getting the exception. The fix was to add the BizTalk Server Administrators and BizTalk Application Users to the BAM_ManagementWS role in the BAMPrimaryImport database. (Note that these two groups do not appear to be added to this role in a "clean" configuration.
Thanks go to Ed at http://talentedmonkeys.wordpress.com/ for figuring out a solution.
