TFS2008: Warehouse processing fails “OLE DB error: OLE DB or ODBC error: Cannot open database "TfsWarehouse" requested by the login”
OLE DB error: OLE DB or ODBC error: Cannot open database "TfsWarehouse" requested by the login. The login failed.; 42000. Error while processing TfsWarehouse cube
Here is a crisp post by our colleague Vimal,
I figured this issue while working with a customer. He kept receiving the Warehouse not processed error while browsing the TFS reports.
In an attempt to resolve the issue, we tried to process the TfsWarehouse in the analysis services. It failed with the following error:
--------------------------------------------------------------------------------------------------------
<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="https://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="https://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>TfsWarehouse</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Database 'TfsWarehouse' completed.
Start time: 6/9/2010 11:49:10 AM; End time: 6/9/2010 11:49:10 AM; Duration: 0:00:00
Processing Dimension 'Machine' completed.
Start time: 6/9/2010 11:49:10 AM; End time: 6/9/2010 11:49:10 AM; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed.
Start time: 6/9/2010 11:49:10 AM; End time: 6/9/2010 11:49:10 AM; Duration: 0:00:00
Processing Dimension Attribute 'Machine' completed.
Start time: 6/9/2010 11:49:10 AM; End time: 6/9/2010 11:49:10 AM; Duration: 0:00:00
Processing Dimension 'Person' completed.
Start time: 6/9/2010 11:49:10 AM; End time: 6/9/2010 11:49:10 AM; Duration: 0:00:00
Processing Dimension 'Tool Artifact Display Url' completed.
Start time: 6/9/2010 11:49:10 AM; End time: 6/9/2010 11:49:10 AM; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed.
Start time: 6/9/2010 11:49:10 AM; End time: 6/9/2010 11:49:10 AM; Duration: 0:00:00
Processing Dimension Attribute 'Tool Type' completed.
Start time: 6/9/2010 11:49:10 AM; End time: 6/9/2010 11:49:10 AM; Duration: 0:00:00
Errors and Warnings from Response
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Cannot open database "TfsWarehouse" requested by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'TfsWarehouseDataSource', Name of 'TfsWarehouseDataSource'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Tool Artifact Display Url', Name of 'Tool Artifact Display Url' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Tool Type' attribute of the 'Tool Artifact Display Url' dimension from the 'TfsWarehouse' database was being processed.
Server: The operation has been cancelled.
OLE DB error: OLE DB or ODBC error: Cannot open database "TfsWarehouse" requested by the login. The login failed.; 42000.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'TfsWarehouseDataSource', Name of 'TfsWarehouseDataSource'.
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Machine', Name of 'Machine' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'Machine' attribute of the 'Machine' dimension from the 'TfsWarehouse' database was being processed.
---------------------------------------------------------------------------------------------------------
While validating the roles assigned for the TfsReports account, the TfsWarehouseDataReader database role membership was enabled for the TfsWarehouse relational databse.
so everything appeared to be normal, but the error above appeared to be very logical. This made me to check the service account for SQL analysis service; it was running with “NT Authority\Network Service”.
Fix
Granting “NT Authority\Network Service” account with TfsWarehouseDataReader role membership to the TfsWarehouse relational database fixed the issue.
Content by: Vimalt
Reviewer: Lucky