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

[rodneyc8063]

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 a few reports on the desktop using IMPORT, uploaded to PBI service, scheduled refreshes and all good everyone is happy its working.

Next we tried to create another desktop report with Direct Query - As expected on the desktop we are all good. Completed the report and uploaded to the same workspace to PBI service BUT we hit the following error

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

When I click on Edit in the PBI service I dont see any tables at all. 

I tried to delete the connection details in PBI desktop and also in PBI service. Created a new desktop report, added in the connection details again fresh. Created the connection in PBI service and still no dice. 

I set up the gateway with the following details

On-Premises

->Chose Gateway Cluster name

->Authentication method - Basic

-> Skipped Test connection (these were as per the instructions we were given)

->No single sign on options at all, no SSO etc

->Privacy level is organizational

Bit stumped here as the gateway is online and the IMPORT reports on the PBI service all work no issues.

Any insight would be very much appreciated

[GilbertQ]

Hi @rodneyc8063 

If you are using an azure sql database, you should be able to connect directly to that database without going through the gateway. Azure SQL Database with DirectQuery - Power BI | Microsoft Learn

[rodneyc8063]

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]

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]

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 

[GilbertQ]

Hi @rodneyc8063 

Can you confirm that you have got the latest version of the Gateway installed?

[rodneyc8063]

We have the "June 2024 update (3000.226.5)" installed currently.

One thing I did notice though is that for our gateway connection we were advised to check off "Skip Test Connection" as otherwise it would "always fail" as per our vendor. 

Not sure if this may have an impact on Direct Query working or not since it seems with Import it works just fine. Its just when we try Direct Query we are hitting issues with this particular data source