SQL server direct query dataset refresh

[Ynr225]

Hi All,

Due to some reasons I have disabled the On-premises data gateway in Power BI service. As I know if I disable it the dataset refresh will fail. But one of my dataset model which is created using SQL server in Direct query mode refresh have to fail because I disabled On-premises data gateway. But my dataset is refreshing every 30 minutes as per the scheduled time. Can any one know why it is not failing to refresh in Power BI  service.

[v-sdhruv · Accepted solution]

Hi @Ynr225 ,
You try this approach to troubleshoot the root cause-

• Open Power BI Service → Dataset Settings → see the Data Source Credentials section.

• If it says "OAuth2" or "Anonymous" without a gateway, it's using a direct internet route.

• If it says "Windows Authentication" and Gateway is missing, it should fail — if not, something else (like public SQL Server) is helping.
  or

Maybe during dataset creation, you connected to a cloud SQL source without realizing it (example: Azure SQL Database, or hosted virtual machines exposed to internet).
Hope this helps!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Cookistador]

If you open the report, did you see some information?

If I remember well, a day my gateway was down, the DQ refresh was working (when I clicked on the refresh button) but if I opened my report, I got an error message

[Ynr225]

I am able to open the report and don't see any error message. When i open the report i can see the updated data in the report

[pankajnamekar25]

Hello @Ynr225 

If your Power BI dataset using SQL Server in DirectQuery mode is still refreshing without the On-premises data gateway, it's likely because:

The SQL Server source is accessible through a cloud-hosted environment (e.g., Azure SQL Database or an accessible public IP), so it doesn’t require the gateway.

DirectQuery datasets only need the gateway if the data source is on-premises and not publicly accessible

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Ynr225]

Hello @pankajnamekar25, Thanks for the reply. But i checked with user who has developed this report, he is saying that it is On-premises SQL server database. If it is on-premise SQL server then refresh should fail after disabling the On-premises data gateway in Power BI service but it's not failing.