BAM Deploy Error: OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\useraccount’.; 42000. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’

When trying to deploy the BAM definition by:

  • Open a command prompt as follows: Click Start, click Run, type cmd, and then click OK.
  • Navigate to the tracking folder by typing “C:\Program Files (x86)\Microsoft BizTalk Server <version>\Tracking” at the command prompt. Press ENTER.
  • Type “bm deploy-all -DefinitionFile:<BAM definition file>”
  • Press ENTER.

I got the following error:

C:\Program Files (x86)\Microsoft BizTalk Server 2013\Tracking>bm.exe deploy-all -DefinitionFile:Tracking.xls
Microsoft (R) Business Activity Monitoring Utility Version 3.10.229.0
Copyright (C) Microsoft Corporation. All rights reserved.

Using ‘BAMPrimaryImport’ BAM Primary Import database on server ‘BIZTALK’…
Deploying Activity… Done.
Deploying View… ERROR: The BAM deployment failed.

Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ProcessDate’, Name of ‘MessageView_ProcessDate’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Hour’ attribute of the ‘MessageView_ProcessDate’ dimension from the ‘BAMAnalysis’ database was being processed.

OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ProcessDate’, Name of ‘MessageView_ProcessDate’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Week’ attribute of the ‘MessageView_ProcessDate’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_DestinationParty’, Name of ‘MessageView_DestinationParty’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘DestinationPartyName’ attribute of the ‘MessageView_DestinationParty’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_PipCode’, Name of ‘MessageView_PipCode’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘PipCode’ attribute of the ‘MessageView_PipCode’ dimension from the ‘BAMAnalysis’ database was being processed.
Server: The current operation was cancelled because another operation in the transaction failed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ProcessDate’, Name of ‘MessageView_ProcessDate’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Year’ attributeof the ‘MessageView_ProcessDate’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ”Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ActivityName’, Name of ‘MessageView_ActivityName’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘ActivityName’ attribute of the ‘MessageView_ActivityName’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ProcessDate’, Name of ‘MessageView_ProcessDate’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Day’ attribute of the ‘MessageView_ProcessDate’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_SourceParty’, Name of ‘MessageView_SourceParty’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘SourcePartyName’  attribute of the ‘MessageView_SourceParty’ dimension from the ‘BAMAnalysis’ database was being processed.
OLE DB error: OLE DB or ODBC error: Login failed for user ‘Domain\UserAccount’.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘bam_MessageView’, Name of ‘bam_MessageView’.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘MessageView_ProcessDate’, Name of ‘MessageView_ProcessDate’ was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Minute’ attribute of the ‘MessageView_ProcessDate’ dimension from the ‘BAMAnalysis’ database was being processed.

BAM Deploy OLE DB error or ODBC error Login failed for user

Cause

The main reason for the error above happen is when the SQL Server service account does not have read access to BAMStarSchema database.

Solution

To fix this issue you have two options.

First option:

  • Open SQL Server Management Studio and expand BAMStarSchema database
  • Expand “Security”, right-click on the “User” option, And select “New User…”
  • Configure the SQL Server service account to have db_datareader Role access as shown below
BAM BAMStarSchema database db_datareader Role access

Second Option:

  • Open SQL Server Management Studio and execute the following script (Replacing the SQL Server service account)
USE BAMStarSchema
GO
EXEC sp_addrolemember 'db_datareader', 'Domain\SQL Server service account'
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.

Leave a Reply

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

turbo360

Back to Top