PBIRS Jan 2021: Error: "We couldn't connect to the Analysis Services server"

[kiwipeet]

I have a report which runs on 2 out of 3 "identical servers".

Each Server is Win2012R2, Running SQL 2016 13.0.5865.1 and PBIRS 1.10.7737.32652

The error message seems generic and unhelpful. 

We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly.: 

SSAS/OLAP is not being used at all.

The report is using Basic Authentication to make a direct query to SQL on the same server.

The query and credentials have been tested and run fine on all 3 servers.

RS log shows more detail:

 : 2021-05-13 08:52:02.2894|ERROR|69|PBI.Win.DataExtensionException|ExceptionType:Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException;DataExtensionErrorDetails:Details: Message=Failed to get CSDL., HResult=0x80004005, ErrorCode=QuerySystemError, Language=en-US, ProviderErrorCode=0xC1070000, ProviderErrorMessage=[2663DD5278984E7565A2F88E29150C3F45B0F0A259F58B6FD892970FE6450AA7], ProviderGenericMessage=[COM error: COM error.], ErrorSource=PowerBI, ErrorSourceOrigin=MsolapWrapper, OnPremErrorCode=, InnerErrorDetails=[Type=MsolapWrapper.MsolapWrapperException, Message=Failure encountered while getting schema];stackTrace: at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion)
at Microsoft.PowerBI.ReportingServicesHost.LocalReportProcessingHost.ExecuteSchemaCommand[T](ExploreHostDataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, IConnectionFactory connectionFactory, IConnectionUserImpersonator connectionUserImpersonator, QueryExecutionOptionsBase queryExecutionOptions, ITelemetryService telemetryService, Func`2 action, String failTelemetryMessage, ServiceErrorStatusCode statusCode, ExploreBaseEvent exploreEvent);message:GetModelMetadata;| RequestID = dc96695b-4835-a4fe-84fc-fdb32313db5b ClientSessionID = 34bde5db-4898-1197-fb8b-0c9756551a1a 
2021-05-13 08:52:02.2894|ERROR|69|PBI.Win.ReportingServicesHostTrace|type:Error;message:library:Throwing Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: , Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: An error occurred while loading the model for the item or data source 'b8ddd68c-e96b-4e4c-be7f-3b156adb72f1_-615167943'. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException: Failed to get CSDL. ---> MsolapWrapper.MsolapWrapperException: Failure encountered while getting schema
at MsolapWrapper.Utils.ThrowErrorIfHrFailed(Int32 hr, String message)
at MsolapWrapper.CDbSchemaRowsetWrapper.ExecuteSchemaRowset(Guid schema, Object[] restrictions, PropertySetCollection propSets)
at MsolapWrapper.SchemaCommand.ExecuteReader(Guid schema, Object[] restrictions)
at MsolapWrapper.SchemaCommand.GetModelMetadata(Object[] restrictions)
at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion)
--- End of inner exception stack trace ---
at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion)
at Microsoft.PowerBI.ReportingServicesHost.LocalReportProcessingHost.ExecuteSchemaCommand[T](ExploreHostDataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, IConnectionFactory connectionFactory, IConnectionUserImpersonator connectionUserImpersonator, QueryExecutionOptionsBase queryExecutionOptions, ITelemetryService telemetryService, Func`2 action, String failTelemetryMessage, ServiceErrorStatusCode statusCode, ExploreBaseEvent exploreEvent)
--- End of inner exception stack trace ---;;| RequestID = dc96695b-4835-a4fe-84fc-fdb32313db5b ClientSessionID = 34bde5db-4898-1197-fb8b-0c9756551a1a 
2021-05-13 08:52:02.2894|ERROR|100|Failure in conceptualschema| RequestID = dc96695b-4835-a4fe-84fc-fdb32313db5b ClientSessionID = 34bde5db-4898-1197-fb8b-0c9756551a1a Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: An error occurred while loading the model for the item or data source 'b8ddd68c-e96b-4e4c-be7f-3b156adb72f1_-615167943'. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException: Failed to get CSDL. ---> MsolapWrapper.MsolapWrapperException: Failure encountered while getting schema
2021-05-13 08:52:02.2894|INFO|50|Sending response. Response code DOMAIN\prm 200, Elapsed time 0:00:00.0641639| RequestID = dc96695b-4835-a4fe-84fc-fdb32313db5b ClientSessionID = 34bde5db-4898-1197-fb8b-0c9756551a1a 
2021-05-13 08:52:02.3519|INFO|94|Received request GET /libs/styles/InfoNav-Sprites.png| RequestID = s_e58e725f-a544-4fb8-b97c-1d3dc872e85a 
2021-05-13 08:52:02.3988|INFO|94|Received request POST /api/explore/reports/b8ddd68c-e96b-4e4c-be7f-3b156adb72f1/conceptualschema| RequestID = 4bb937bc-b2d3-8dc5-2817-4e3a03b60580 ClientSessionID = 34bde5db-4898-1197-fb8b-0c9756551a1a 
2021-05-13 08:52:02.4300|ERROR|107|Failure in conceptualschema| RequestID = 4bb937bc-b2d3-8dc5-2817-4e3a03b60580 ClientSessionID = 34bde5db-4898-1197-fb8b-0c9756551a1a Microsoft.ReportingServices.Diagnostics.Utilities.CannotRetrieveModelException: An error occurred while loading the model for the item or data source 'b8ddd68c-e96b-4e4c-be7f-3b156adb72f1_-615167943'. Verify that the connection information is correct and that you have permissions to access the data source. ---> Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException: Failed to get CSDL. ---> MsolapWrapper.MsolapWrapperException: Failure encountered while getting schema
2021-05-13 08:52:02.4300|INFO|107|Sending response. Response code DOMAIN\prm 200, Elapsed time 0:00:00.0238187| RequestID = 4bb937bc-b2d3-8dc5-2817-4e3a03b60580 ClientSessionID = 34bde5db-4898-1197-fb8b-0c9756551a1a 
2021-05-13 08:52:14.9294|INFO|109|Started clean up of embedded models
2021-05-13 08:52:14.9294|INFO|109|Finished clean up of 0 embedded models

Why does this run on 2 servers but not the 3rd?

Why is it trying to use MSOLAP?

I have also posted on StackOverflow

Any suggestions or ideas would be greatly apopreciated.

Edit: To explain:  We have 3 servers to seperate Development, Testing and Production. We have a processes for UAT, version and change control. We spend hundreds of hours developing and testing content. So it is incredibly frustrating to have a report that has been thoroughly tested and accepted by the business and we can't get it to run in Live. Yet everything else works.

[kiwipeet · Accepted solution]

We ended up creating a new blank report and cut+paste everything into it from the old report. And it now runs. 

We still don't know why the last one was failing. An incredibly frustrating waste of time.

[v-janeyg-msft]

Hi, @kiwipeet 

According to your description and my search, It may be a kerberos error when you report server and sql server is not in the same sever. You can follow the steps to configure: Configure Kerberos to use Power BI reports - Power BI | Microsoft Docs

If it doesn't solve your problem, Please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kiwipeet]

Thanks for your reply. The report should not be using Kerberos as RS & SQL are both running on the same server. Also: my RS Authentication config settings are the same on all three servers.

[v-janeyg-msft]

Hi, @kiwipeet 

Do you use a powerbi RS on the three servers? I don't understand your approach. You only need one RS, add permissions, then you can share the report with other users in the domain. Why do you use three servers? Have you tried to view the report on only one server?

Best Regards

Janey Guo

[kiwipeet]

We have 3 servers to seperate Development, Testing and Production.

[v-janeyg-msft]

Hi, @kiwipeet 

Do you try to run it in Internet Explorer as an administrator? Maybe it helps.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HashamNiaz]

Hi !

There are 2 parts to it, 1st authentication is done for your Report Server to authenticate you, 2nd authentication is for your SQL datasource.

Try, replicating a scenario where you publish a dummy report with no SQL datasource, just a text box Saying this is dummy. And see if you are able to render it on all 3 server.

Regards,

Hasham

[kiwipeet]

Thanks for your suggestion. That's pretty much what we have been doing. We can build a new report using the same query and creds and it runs ok.  We've also stripped this report down piece by piece as a process of elimination. I got down to a single visual (table) showing a single column and it still fails. I deleted the Datasource and created a new one with the same query and creds and it worked. There is something weird & sticky in that pbix datasource which our live server doesn't like. (but the other 2 are ok with).  I wish I could view the complete source like I could with an .rdl.