Dataset Issues - DirectQuery & Import

[rwcampbell]

Hi, 

I have a dataset that is connected via both import and DQ to a SQL server with the latest gateway.

When i open any reports today it fails on the DQ side and works fine on the import side.

This feature is not supported. Please make sure you have installed the latest version of the gateway. is the error we are getting.

The gateway is also using dataflows and they are working as normal, any ideas or suggestions welcome.

Thanks.

[Anonymous · Accepted solution]

Hi @rwcampbell 

When facing issues with Direct Query (DQ) in Power BI while connecting to a SQL Server, especially with an error message

1. Check Gateway Version:

   • You mentioned in the question that you have installed the latest version, but your error message tells you to check if it is the latest version. Ensure that the On-premises Data Gateway is indeed the latest version. Power BI regularly updates, and keeping the gateway updated is crucial for compatibility. You can check the version of your gateway and compare it with the latest version available on the official Power BI website.

2. Update Gateway:

   • If the gateway is not the latest version, update it. This is often a critical step since newer versions of Power BI might introduce features or dependencies that require the latest gateway version.
     
     

3. Review Gateway Configuration:

   • Verify that the gateway is correctly configured for Direct Query. This includes checking the data source settings and ensuring that the credentials are correctly set up.
     
     

4. Check SQL Server Compatibility:

   • Ensure that your SQL Server version is compatible with the current version of Power BI and the gateway. Sometimes, newer updates in Power BI or the gateway might have specific requirements for the SQL Server version.
     
     

5. Inspect Data Source Settings in Power BI:

   • In your Power BI report, check the settings for the Direct Query source. Ensure that the connection details are correct and that there aren't any changes required due to updates or network changes.
     
     

6. Review Error Logs:

   • Check the error logs for the On-premises Data Gateway. These logs can provide more detailed information about why the connection is failing.
     
     

7. Network and Firewall Settings:

   • Ensure that any network or firewall settings are not blocking the connection between Power BI and the SQL Server through the gateway.
     
     

8. Test Direct Query Independently:

   • Try setting up a new Direct Query connection to the SQL Server in a separate Power BI report to see if the issue is with a specific report or a broader connection issue.
     
     

9. Consult Documentation and Support:

   • Review the official Power BI and Microsoft documentation for any known issues or additional troubleshooting steps.
   • If the issue persists, consider reaching out to Microsoft support for more in-depth assistance.
     
     

10. Check Dataflows:

• Since you mentioned dataflows are working normally, compare the configuration of the dataflows with your Direct Query setup to identify any discrepancies.
  
  

Best Regards,

Jayleny

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

[rwcampbell]

Hi Jayleny (@v-jialongy-msft),

We have checked everything and left the workspace alone for 24 hours with no refreshes on any datasets to see if it was the refresh limits were being the issue.

Since then the workspace has come back to normal and we didnt need to reinstall the gateway as it is already at the latest version as each month we down systems for an hour when the new version is released and install it.

thanks.