Move from On Premise SQL to Azure SQL db - still needs on-premise gateway

[djillbunters]

I wanted to publish my report to be automatically refreshed every day.

In my power bi desktop report i changed my data source to the azure datasource (xxxxxxx.database.windows.net).
Everything works as it should, however in Power Bi service I still need to use an on-premise gateway.

I'm assuming I should have changed my datasource in a different way, as it seems like it's now setup to an on-premise SQL instance that just happens to have an Azure SQL database as a name.

[NandanHegde]

A slight correction @v-kongfanf-msft .
In case if the Azure SQL database and its server is hosted within a VNaet; you would need a gateway to connect the Azure SQL database from Powerbi.

No need of gateway is Azure SQL database is hosted normally on Azure cloud service.

[djillbunters]

Luckily no VNET, just normally hosted.

There are some IP restrictions (my home and office IP) but they do not apply when working with a deployed power bi report in the power bi web app.

[NandanHegde]

Agree, as long the Server is not hosted within the VNet and the below property is enabled on the server, 

there is no need of gateway

[djillbunters]

Thanks for the heads up, I'm going to check.
I'm not hosting the Azure SQL server myself.

For context: I'm doing a few power bi projects freelance for a customer, and have a service provider for data extraction and hosting (all ISO certified).
The fee for the service provider is so low, it would be crazy to get our own subscription and get an azure sql database and azure functions to extract data. It was more expensive to do myself and I would also be responsible for any issues, which are now part of the contract 🙂

[v-kongfanf-msft]

Hi @NandanHegde ,

Thank you for the addition.

Best Regards,
Adamk Kong

[v-kongfanf-msft · Accepted solution]

Hi @djillbunters ,

For Azure SQL databases, the refresh scenario does not require a local data gateway because Azure SQL databases are a cloud service. However, make sure that the dataset in Power BI Services is set to use a direct query connection to the Azure SQL database.

In Power BI Services, go to Dataset Settings and check the data source credentials. Make sure it is set to use the appropriate authentication method (e.g. OAuth2) for the Azure SQL database.

Also note: To connect to an Azure SQL database using DirectQuery, you must use Power BI Desktop and then publish the created report to the Power BI service.

For more details, you can read below document:

Azure SQL Database with DirectQuery - Power BI | Microsoft Learn

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,
Adamk Kong

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

[djillbunters]

Thank you very much!

I will check tonight and think this indeed will resolve.
I will mark as solved tonight 🙂