Power BI 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://docs.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://docs.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://docs.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

CoolCoder · Answer 1 · Feb 17, 2022

You'll need to install an on-prem gateway to the VM if you want to keep your Azure SQL settings private and avoid having to monitor the firewall.

I've also been looking for a different approach, but because the Power BI service is in the cloud, there's no way to get it to join the private Azure VNET without installing a gateway on a node inside the network.

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

If you are interested in learning more about Power BI, then check out the affordable Power BI certification cost now!