Forum Discussion

kitti's avatar
kitti
Helper III
1 year ago
Solved

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)...
Direct Query
  • v-sathmakuri's avatar
    v-sathmakuri
    1 year ago

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

johnbasha33's avatar
johnbasha33
Super User
1 year ago

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's avatar
    kitti
    Helper III
    1 year ago

    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