Power BI Report Server Direct Query from Spark and SQL Server database is not working

[kitti]

Hi Experts,

I just tried using Power BI Desktop and get data in direct query mode from Spark/SQL Server with success. However, I uploaded the reports on my Power BI Report Server (September 2024) and tried to view the report then I get an error below.

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 ID940c5bbb-a284-bb98-f3f4-17230d2b7b9c

TimeWed Apr 23 2025 15:25:38 GMT+0700 (Indochina Time)

Service version/powerbi/libs

As searched on the community and see the PBI_SQL_TRUSTED_SERVERS variable, I am configuring the value as Localhost but the issue still persists.

Please help.

KL

[v-sathmakuri]

Hi @kitti ,

We are following up once again regarding your query. Could you please confirm whether the issue has been resolved through your support ticket with Microsoft?

If so, we would appreciate it if you could share the resolution or any key insights here to benefit others in the community. If we don’t receive a response, we will proceed with closing this thread.

If you need further assistance in the future, feel free to start a new thread in the Microsoft Fabric Community Forum. We will be happy to support you there.

Thank you!!

[v-sathmakuri]

Hi @kitti ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-sathmakuri]

Hi @kitti ,

Thank you for reaching out to Microsoft Fabric Community.

As you have confirmed you have validated server names and also you have restarted the PBIRS service.

Please try the below steps as well.

• Go to Manage,  Data Sources, Set to Windows Authentication (Integrated),  Enable "Use as Windows credentials"

• Ensure ODBC or MSOLEDB drivers are installed on the PBIRS server for Spark/SQL Server.

• check for the report server logs in C:\Program Files\Microsoft Power BI Report Server\PBIRS\LogFiles , look for Errors related to DataExtension, OLEDB, Authentication, or AnalysisServices

• Use Power BI Desktop (Report Server version) from the PBIRS server itself to test connectivity using the same DirectQuery. This helps rule out firewall or driver-related issues or Make sure PBIRS can resolve and reach the SQL/Spark servers.

  Use ping, telnet <server> <port>, or Test-NetConnection.

• Go to Event Viewer,  Applications and Services Logs ,  Power BI Report Server and Look for errors tied to authentication or connection issues.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!

[kitti]

@v-sathmakuri  sorry for late reply. I already tried to use Power BI Desktop (Report Server version) from the PBIRS server itself to test connectivity using the same DirectQuery but got the same error.

Below is the error from the logfile. Is there any issue about permission?

2025-05-05 17:19:32.5359|ERROR|74|Failure in conceptualschema catalogObjectId=45878ad6-259f-499d-a629-594b48f1041b| RequestID = 188c061f-f34e-07a4-466d-502d7bb295ed ClientSessionID = 601884a8-13d0-d24d-9084-2a3d68ec1765 Microsoft.PowerBI.ReportingServicesHost.CannotRetrieveModelException: An error occurred while loading the model. 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, TranslationsBehavior translationsBehavior)
--- End of inner exception stack trace ---
at Microsoft.PowerBI.DataExtension.Msolap.SchemaCommand.GetModelMetadata(String catalogName, String perspectiveName, String supportedVersion, TranslationsBehavior translationsBehavior)
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.<>c__DisplayClass10_0.<GetModelMetadata>b__0(IDbSchemaCommand command)
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.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 ---
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.ExecuteSchemaCommand[T](ExploreHostDataSourceInfo dataSourceInfo, IDbConnectionPool connectionPool, IConnectionFactory connectionFactory, IConnectionUserImpersonator connectionUserImpersonator, QueryExecutionOptionsBase queryExecutionOptions, ITelemetryService telemetryService, Func`2 action, String failTelemetryMessage, ServiceErrorStatusCode statusCode, ExploreBaseEvent exploreEvent)
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.<>c__DisplayClass10_1.<GetModelMetadata>b__1(ExploreBaseEvent timedEvent)
at Microsoft.PowerBI.ReportingServicesHost.ExploreTelemetryService.<>c__DisplayClass4_0`1.<RunInActivity>b__0(ExploreBaseEvent activity)
at Microsoft.PowerBI.ReportingServicesHost.ExploreTelemetryService.RunInActivity(String activityName, Action`1 action)
at Microsoft.PowerBI.ReportingServicesHost.ExploreTelemetryService.RunInActivity[T](String activityName, Func`2 action)
at Microsoft.PowerBI.ReportingServicesHost.ModelMetadataProvider.GetModelMetadata(ModelMetadataRequest request)
at Microsoft.PowerBI.ReportServer.ExploreHost.RSPowerViewHandler.GetModelCSDL(String id, String maxModelMetadataVersion, IASConnectionInfo& asConnectionInfo)
at Microsoft.PowerBI.ReportServer.ExploreHost.RSPowerViewHandler.<>c__DisplayClass41_0.<GetOrAddModel>b__0()
at System.Lazy`1.CreateValue()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Lazy`1.get_Value()
at Microsoft.PowerBI.ReportServer.ExploreHost.RSModelCache.GetOrAddModel(ModelKey key, Func`1 modelInfoFunc)
at Microsoft.PowerBI.ReportServer.ExploreHost.RSPowerViewHandler.EnsureSession(String modelId)
at Microsoft.PowerBI.ExploreHost.ExploreClientHandlerBaseFlow.<RunAsync>b__9_0()
at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.ExploreHost.ExploreClientHandlerBaseFlow.<RunAsync>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.ExploreHost.ExploreClient.<GetClientConceptualSchemaAsync>d__13.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.ReportServer.ExploreHost.RSExploreHost.<GetConceptualSchemaAsync>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.ReportServer.WebApi.PbiApi.PbiApiController.<GetConceptualSchemaAsync>d__25.MoveNext()

[v-sathmakuri]

Hi @kitti  ,

The error message below indicates a potential permissions issue when accessing the data source:

Microsoft.PowerBI.ReportingServicesHost.CannotRetrieveModelException: An error occurred while loading the model. 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

To troubleshoot this, consider the following steps: 

• Verify that the user or service account has the necessary permissions on the underlying data source.
• From the same server, test connectivity to the data source using Excel or SQL Server Management Studio (SSMS) with the same credentials or connection string.
• Recreate and republish the report using the exact version of Power BI Desktop for Report Server that matches your server deployment.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!

[kitti]

@v-sathmakuri  thank you for your response. I am using Power BI Desktop September 2024, which is the same version as the report server. And I can access the SQL Server using SSMS tool on the report server, also I already configured the credential, but I got the error when I access the report on the server.

Regards,

KL

[v-sathmakuri]

Hi @kitti  ,

Thank you for your response!!

Since the test connection succeeds in Power BI Desktop, try testing the connection to SQL Server from the machine hosting PBIRS using PowerShell or SQLCMD using the same account.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!

[kitti]

@v-sathmakuri  Thank you for your response. I just tried SQLCMD on the report server and it can access the SQL Server DB.

Regards,

KL

[v-sathmakuri · Accepted solution]

Hi @kitti ,

Everything seems to be correctly configured as per your comments. 

Can you please raise a Microsoft support ticket for further investigation. You can create a Microsoft support ticket using the link below.

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Hope this works for you!

Thank you!!

[kitti]

Additionally, I am not able to click on Data Sources menu here when getting data from Spark in DirectQuery mode. Is it normal?

[johnbasha33]

@kitti 

Double-check your PBI_SQL_TRUSTED_SERVERS Environment Variable

• Open Environment Variables on your PBIRS server

• Under System Variables, create or edit:
  
  PBI_SQL_TRUSTED_SERVERS
  
  

• The value should include the server names you want to allow connections to, not just localhost.
  (e.g., your SQL Server name, Spark Server address, etc.)

• Example:
  
  localhost;yourservername;yourserver.domain.com
  
  

  Restart the PBIRS Service

  After setting PBI_SQL_TRUSTED_SERVERS, restart:
  
  services.msc → SQL Server Reporting Services (Power BI) → Restart
  
  

  Check Data Source Settings inside PBIRS

  • After uploading your .pbix to Report Server,

  • Go to Manage > Data Sources

  • Make sure the authentication is set to "Windows Authentication (Integrated)" if you expect delegation.

  Example setting:
  Use as Windows credentials when connecting to the data source
  
  

  Other Things to Double-Check

  • Make sure your PBIRS is updated (September 2024 build is good, but double-check no patches are missing).

  • If you're connecting to Spark via ODBC or Direct TCP, make sure the driver is also installed on the PBIRS server itself.

Kerberos Delegation is needed if you go beyond local connections.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[kitti]

@johnbasha33 

Thank you for your suggestion. However, I already put all the server names related separated by semicolon (;) and reboot the server but the issue still persists. Not only Spark, also, I access a report that gets data from SQL Server DB using direct query and face the same issue.

Could you please help.

KL