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

2
votes

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

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

1
sql-serverazurepowerbiazure-private-link
According to your description, I think your VM cannot access azure SQL. Please refer to docs.microsoft.com/en-us/azure/azure-sql/database/… to configure it. – Jim Xu
@JimXu no it is not VM From the VM I am able to access; I am having issues connecting from PowerBI Service – DataGuru
@marc_s - Thank you for the edit – DataGuru
According to my understanding, you install powerbi desktop in one azure vm. Now you cannot access SQL server in the powerbi desktop. So I think your VM cannot connect the SQL server. – Jim Xu
From PowerBI desktop on the VM I am fine I create my report publish to PowerBI Service and that is where my problem starts I go to PowerBI Service to set the credentials and I am not able to access the database – DataGuru

1 Answers

1
votes

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://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem