Scheduled Refresh - Azure SQL Database behind VNET

[Anonymous]

Hi all, we would like to set Scheduled Refresh for our dashboard on PowerBI Pro Service.

As far as we know, if the Azure SQL database server is not within a vNet,

it can be done as simple as enable the "Allow access to Azure Services" in the server option and the PowerBI service will be able to connect to it.

However, our Azure SQL database server is going to be within a vNet (PowerBI Data Connectivity Mode: Import).

Will the "Allow access to Azure Services" option still work in that case?

If it will not work (we know Direct Query will not work in this case, unless use the gateway with a VM in the vNet),

please advise how can we connect our PowerBI service to the Azure SQL database server.

Thank you.

[GilbertQ · Accepted solution]

Hi @Anonymous 

In order for this to work you will need to download and install the Power BI Gateway and configure it to connect to the Power BI Service.

The reason for this as far as I understand is that you need the Gateway within your VNET, so it can then communicate with the Power BI Service.

Here are the details on how to achieve this: Set the Azure Relay for on-premises data gateway | Microsoft Docs

[Anonymous · Accepted solution]

Hi  @Anonymous ,

1. To configure the plan refresh, you need to configure the gateway and set the credentials of the gateway

You can view the configuration plan refresh here:

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh

2. “Allow access to Azure services”,  your server allows communications from all resources inside the Azure boundary.

When set to ON，other resources within the Azure boundary, for example an Azure Virtual Machine, can access SQL Database，There is another one that has the same function as "allow access to azure services" and is more restrictive: IP firewall rules or virtual network firewall rules

This is a related document:

https://docs.microsoft.com/en-us/azure/azure-sql/database/network-access-controls-overview#allow-azure-services

3. Related documents of powerbi service connecting to azure SQL database server:

https://www.mssqltips.com/sqlservertip/5953/create-power-bi-connection-to-azure-sql-database/

https://azure.microsoft.com/en-us/blog/securing-the-connection-between-power-bi-and-azure-sql-database/

Best Regards

Liu Yang

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

[GilbertQ · Accepted solution]

Hi @Anonymous 

In order for this to work you will need to download and install the Power BI Gateway and configure it to connect to the Power BI Service.

The reason for this as far as I understand is that you need the Gateway within your VNET, so it can then communicate with the Power BI Service.

Here are the details on how to achieve this: Set the Azure Relay for on-premises data gateway | Microsoft Docs