Dataset from SQL in Azure requires on-prem for SQL?

[agneum]

Hi,

I've recentlty decided to point all sql server databases to the cloud. What I did was to delete the old connection and then create exactly the same connection but with "cloud" option selected and also "allow this connection to be used with on-premises data gateways" option enabled. The connections validate and I can map them just fine in the dataset. But users cannot refresh the dataset anways, onprem connection is required. I have no idea what the issue may be. I suspect it is because on of my SQL server lives on an Azure VM and that Power BI does not recognize it as a "cloud" location.

Note the "use an On-premises or VNET data gateway" is forced to on ,and cannot be unchecked. Any ideas how to make Power BI connect to the Azure VM SQL server directly with cloud option, or is this not supported?

[v-zhangti · Accepted solution]

Hi, @agneum 

The Azure SQL managed instance behaves similarly to an On-premise data source, so you cannot connect to it directly with the cloud option.

One solution to this issue could be to use an on-premises data gateway to connect to the SQL server database. The on-premises data gateway acts as a bridge between your cloud-based data sources and your on-premises data sources, allowing Power BI to access the data without requiring an on-premises connection.

Another solution could be to check if the SQL server on the Azure VM is configured to allow remote connections. If not, you may need to enable remote connections to allow Power BI to connect to the database directly.

What is Azure SQL Managed Instance? - Azure SQL Managed Instance | Microsoft Learn

Best Regards,

Community Support Team _Charlotte

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