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

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

kitti — Wed, 23 Apr 2025 08:34:33 GMT

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.

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

johnbasha33 — Wed, 23 Apr 2025 16:30:00 GMT

@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 !!

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

kitti — Thu, 24 Apr 2025 01:17:46 GMT

@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.

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

kitti — Fri, 25 Apr 2025 01:10:46 GMT

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

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

v-sathmakuri — Mon, 28 Apr 2025 16:08:58 GMT

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!!

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

v-sathmakuri — Fri, 02 May 2025 11:00:20 GMT

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.

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

kitti — Mon, 05 May 2025 10:25:05 GMT

@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()

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

v-sathmakuri — Tue, 06 May 2025 09:35:23 GMT

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!!

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

kitti — Thu, 08 May 2025 06:28:55 GMT

@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,

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

v-sathmakuri — Thu, 08 May 2025 12:06:03 GMT

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!!

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

kitti — Fri, 09 May 2025 02:32:01 GMT

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

Regards,

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

v-sathmakuri — Fri, 09 May 2025 10:10:42 GMT

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!!

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

v-sathmakuri — Thu, 29 May 2025 13:24:41 GMT

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!!