PowerBI not able to connect to Azure SQL with private end point

Question

I have a VNET configuration in Azure with the following subnet configuration

• subnet1 - 10.16.1.0/24 - VM's

• subnet2 - 10.16.2.0/24 - Database

• subnet3 - 10.16.3.0/24 - PowerBI Access

• subnet2 has Azure SQL server with firewall no public access

• subnet3 has been enabled for PowerBI private endpoint as per steps here https://learn.microsoft.com/en-us/power-bi/admin/service-security-private-links

I login to the VM download PowerBI desktop connect to the database create a report. I publish the report to PowerBI Service.

I am able to access the Azure SQL from PowerBI Desktop and from the VM using "Microsoft SQL Server Management Studio"

When I log in to PowerBI Service and provide credentials for the database access, I cannot access the database.

The error I am getting from powerBI service

Configure database
Failed to update data source credentials: Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://learn.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://learn.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database).

If I set

• Deny Public Network Acces to No

and set

• Allow Azure services and resources to access this server to Yes

Then I can connect to the database from any subscription using the private link, userid & password

With the setting

• Deny Public Network Acces to No

Access from Azure Data Factory is not an issue, as I can create a privatelink to the database from ADF and use that connection.

Appreciate any help

How can I enable the connection from PowerBI Service

Answer 1

If you want to keep your settings on Azure SQL as private and not have to manage the firewall, you'll have to install an on-prem gateway to the VM.

I've also been trying to find another route as well, and since the Power BI service is in the cloud there's no way to have it join the private Azure VNET without have a gateway installed on a node that is inside of the network.

You can find the instructions here: https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem