Internal Error – cannot open database “BAMStarSchema” when trying to deploy a BAM activity

When I tried to install ESB Guidance 2.0 CTP2 exception handling, I got the following error from the BAM deployment part of the installation script:

Deploying Activity… Done.
Deploying View… ERROR: The BAM deployment failed.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Server: The operation has been cancelled.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of ‘bam_ExcByApplication’, Name of ‘bam_ExcB
yApplication’.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of ‘ExcByApplication_ExcDatetime’, Name of ‘ExcByApplication_ExcDatetime’
was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Month’ attribute
of the ‘ExcByApplication_ExcDatetime’ dimension from the ‘BAMAnalysis’ database
was being processed.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of ‘bam_ExcByApplication’, Name of ‘bam_ExcB
yApplication’.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of ‘ExcByApplication_ExcDatetime’, Name of ‘ExcByApplication_ExcDatetime’
was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Day’ attribute o
f the ‘ExcByApplication_ExcDatetime’ dimension from the ‘BAMAnalysis’ database w
as being processed.
OLE DB error: OLE DB or ODBC error: Cannot open database "BAMStarSchema" request
ed by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to th
e data source with the DataSourceID of ‘bam_ExcByApplication’, Name of ‘bam_ExcB
yApplication’.
Errors in the OLAP storage engine: An error occurred while the dimension, with t
he ID of ‘ExcByApplication_ExcDatetime’, Name of ‘ExcByApplication_ExcDatetime’
was being processed.
Errors in the OLAP storage engine: An error occurred while the ‘Year’ attribute
of the ‘ExcByApplication_ExcDatetime’ dimension from the ‘BAMAnalysis’ database
was being processed.

It turned out that the SQL Server service account (“SQLServer” in my case) didn’t have read access to BAMStarSchema. Here is a script that fixed it:

USE BAMStarSchema
GO
CREATE USER SQLServer FOR LOGIN [BTS2009SQLServer]
GO
EXEC sp_addrolemember ‘db_datareader’, ‘SQLServer’
GO

Advertisements

6 thoughts on “Internal Error – cannot open database “BAMStarSchema” when trying to deploy a BAM activity”

  1. Henrik,Thanks for the solution. I had the same error, even though both my Database Engine and Analysis Services are running under Local Machine\Network Service account.Richard

  2. Henrik,Thanks for the solution. My issue was exactly same as Richard’s. I used SQL Server Management Studio to fix the issue in my environment.Mohammad

  3. This issue is happening in BizTalk 2010 also. I used SQL Server Management Studio to fix the issue.

  4. I had this same problem but the fix here was not exactly correct. I tried making sure my SQL Service account had Read rights on BAMStarSchema, but it ALREADY had the privilege. So the issue was something else.
    I looked in the SQL Error Logs in SQL Studio under Management->SQL Server Logs->Current.
    In the log I saw that the same account getting Logon Failed errors over and over, from each time I ran the BM.exe task. HOWEVER, the account it showed was not my SQL Service account, but rather my SQL Analysis Service account. SQL and SSAS run under different accounts. Once I added the Read privilege to the SSAS account, as described above, everything worked.

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