We have 3 servers Dev, Test & Live. Each Server is:
- Win2012R2
- SQL2016 SP2-CU15-GDR 13.0.5865.1 and
- PBIRS: Version 1.10.7737.32652 (January 2021)
We have one report, which works on Dev and Test, but fails in Live with the following error:
An unexpected error occurred
An unexpected error occurred. Please try again later.
Please try again later or contact support. If you contact support, please provide these details.
We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly.:
Request ID: cc3b3bf4-bedd-12ea-2de7-01a00766d42e
Time: Wed May 12 2021 08:49:54 GMT+1200 (New Zealand Standard Time)
Service version: /powerbi/libs
The report is connecting locally using Basic authentication. We have confirmed the Query runs fine on all three servers using the same credentials. This report is using Direct query and isn't using Analysis services at all.
Digging into the RS logs I find:
2021-05-13 08:52:02.2425|INFO|94|Received request GET /libs/scripts/pivotTableVisuals.js| RequestID = s_7a9055a3-0edd-4db1-8896-83fb3f85a5eb
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
This line is the one that has my attention:
Microsoft.PowerBI.DataExtension.Contracts.DataExtensionException: Failed to get CSDL. ---> MsolapWrapper.MsolapWrapperException: Failure encountered while getting schema
Why is it trying to run MSOLAP?
I can:
- Create a new report using the same query and credentials with a table an a map and it runs.
- Deploy the same report back to either Dev or Test and it runs. Even running it on Dev and querying Live.
RS Config, Firewall settings, SQL Settings are the same on all 3 servers
All blogs and posts I've read so far have lead to dead ends. Kerberos, Windows Defender, missing DLL's and checked and compared with the othe server and theyre the same.
Why won't this report run on this one server?
Edit: I have slowly stripped the content out of this report. I deleted extra tabs, removed all filters, visuals and slicers, it still failed. I deleted the only query and re-added the same query selecting the same data using the same creds and it now works. The error seems somehow to attached to the query/model.
