Forum Discussion

rodneyc8063's avatar
rodneyc8063
Advocate I
2 years ago

Direct Query Report in Power BI Service Not Working with Error but Desktop Works?

Howdy folks,   Been a while since I worked with PBI and am hitting a very odd issue. So we have an enterprise gateway where we set up a connection to a remote Azure MS SQL Database. We created...
Need Help
  • rodneyc8063's avatar
    rodneyc8063
    Advocate I
    2 years ago

    Hi GilbertQ 

     

    I had to go back to double check and I apologize but I stand corrected as we are actually not using an Azure SQL Server database - We are connecting to a remote SQL Server connection hence the gateway. 

     

    I tried digging around (Google) and cant quite seem to find anything conclusive as the error is lacking details. 

     

    The odd thing is that IMPORT works fine all around, Direct Query works in the desktop but just when we try to go to the service we hit the error.

     

    Does the workspace possible need to be put into a Premium Capacity?

     

    I dont think its a permission issue as I literally created a new report/connection on the desktop, published and re-created the connection. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, rodneyc8063 

      First, make sure that the data source is configured correctly in the gateway. Sometimes, even a small misconfiguration can cause problems. Double-check the settings under Manage gateways in the Power BI service. Then make sure that the privacy level of the data source is set correctly. A mismatched level of privacy can lead to data access issues.

      Although you skipped the test connection step, it might be worth going back and running it to make sure the connection works.

      While Premium capacity can provide additional features and performance benefits, Direct Query isn't absolutely necessary. However, if you have access to Premium capacity, it might be worth trying to see if it solves the problem. You can check the following link:

      Troubleshoot DirectQuery models in Power BI Desktop - Power BI | Microsoft Learn
      Verify that the account used for the gateway has the required permissions to access the data source. This includes SQL Server and Power BI services. You can refer to the following post:

      Solved: This report couldnt access the datasource. Contact... - Microsoft Fabric Community

       

      Check the dataset settings in the Power BI service. Make sure that the dataset is properly mapped to the gateway and that all settings are configured correctly.

      DirectQuery for Power BI dataset: How does it work? - RADACAD

       

      How to Get Your Question Answered Quickly

      Best Regards

      Yongkang Hua

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • rodneyc8063's avatar
        rodneyc8063
        Advocate I
        2 years ago

        So I have been quite stumped trying to test all this.

         

        What I have tried is

         

        1) Created a new report, single table with 10 rows connecting to an on prem Oracle database in direct query mode on the desktop

        2) Took this exact same report and changed the mode from direct query to import on the desktop

        3) Uploaded to power bi service - Tested both import and direct query and everything works.

         

        So this was to test to make sure our gateway is working as expected with both import and direct query and it does. 

         

        Now I try the same steps as above but with my problem SQL Server connection.

         

        1) Create a brand new single table report with only 10 rows in a table in direct query mode on the desktop - Everything seems to work just fine so far

         

        2) Took this exact same report and resaved it with Import mode so total 2 reports so far. 

         

        3) Uploaded both of these to power bi service

        4) Delete the old gateway connection and created a brand new one from scratch.

        5) Did a "Refresh Now" for the Import report and it was successful

         

        Great - So far it looks like the connection is working as expected. I am able to import and refresh the dataset as needed. 

         

        Now when I try to open the Direct Query version of this report I hit the following error again

         

        There was a data source access error. Please contact the gateway administrator.

         

        I dont think its an issue with our gateway as I was able to do an import and direct query on a different Oracle database. Also I was able to do an import on the SQL server database no issue. Its only when I do a Direct Query with the SQL server database that I hit the above error.  

         

        The reports are as basic as can be, single table 10 rows from a single source.

         

        The other thing that I find incredibly odd is if I go to the semantic model and click on it to view it here

         

        There is a "Refresh" button which I tried and when I check the refresh history it says "Completed" but no errors??

         

        So it LOOKS like its working sort of? 

         

        Its just quite odd and not sure where else to look