Forum Discussion

Ynr225's avatar
Ynr225
Helper II
1 year ago
Solved

SQL server direct query dataset refresh

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.

Need Help
Show and Tell
  • v-sdhruv's avatar
    v-sdhruv
    1 year ago

    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.

     

5 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

    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's avatar
      Ynr225
      Helper II
      1 year ago

      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.

  • Cookistador's avatar
    Cookistador
    Super User
    1 year ago

    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's avatar
      Ynr225
      Helper II
      1 year ago

      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

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

    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.