Forum Discussion
Power BI Report Server Direct Query from Spark and SQL Server database is not working
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!!
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!!
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
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!!
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