Forum Discussion
Power BI Report Server Direct Query from Spark and SQL Server database is not working
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.
Please try again later or contact support. If you contact support, please provide these details.
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
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!!-
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!!
12 Replies
Double-check your
PBI_SQL_TRUSTED_SERVERSEnvironment Variable-
Open
Environment Variableson 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.comRestart the PBIRS Service
After setting
PBI_SQL_TRUSTED_SERVERS, restart:
services.msc → SQL Server Reporting Services (Power BI) → RestartCheck Data Source Settings inside PBIRS
-
After uploading your
.pbixto 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 sourceOther 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 !!
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
-
Additionally, I am not able to click on Data Sources menu here when getting data from Spark in DirectQuery mode. Is it normal?
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!!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()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!!
-
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.
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!!
