The flexibility to connect various data services within Azure and Power BI is a powerful feature, however this flexibility does introduce risk in terms of securely connecting these data services. We can configure secure connections using authentication methods, but what happens if we want to “lockdown” an Azure data resource so that no traffic from the public internet or even within the Azure network itself can attempt a connection? We can use Private Endpoints within supported services and configure Power BI to connect to these private endpoints.

In this blog post we’ll walkthrough the steps necessary to allow a Power BI dataset/report deployed to the Power BI Service to connect to an Azure SQL Database which has no access via the public internet and also no access to Azure services. This process uses a VNet (virtual network), a Private Endpoint on the Azure SQL Server, and a VNet Data Gateway configured in the Power Platform environment.

Considerations

Please note that this walkthrough contains default settings when creating the VNet and subnets, these default settings may not conform to a particular organisations networking strategy. Therefore an Azure Network Engineer should be consulted to ensure any specific settings are configured correctly for the specific environment. The walkthrough contains generic settings, which may need modifying.

Basic Sequence

In the walkthrough we’ll be going through several steps to allow the Power BI Service to communicate with an Azure SQL Server which has been configured with a Private Endpoint and all other traffic disabled.

  • Create Virtual Network (VNet)
  • Create Private Endpoint in Azure SQL Server and configure Network settings
  • Configure VNet Subnet for Power Platform
  • Create VNet Data Gateway in Power Platform Admin Centre
  • Configure VNet Data Gateway in Power BI and add Azure SQL Database as a data source
  • Create and upload Power BI report to PBI Service
  • Configure Power BI Dataset to use VNet Data Gateway
  • Test data refresh

Basic Architecture

The following diagram shows a simple architectural flow from Power BI to Azure SQL using the Power Platform VNet Data Gateway and a VNet.


Walkthrough

We’ll now walkthrough the steps to create a VNet and secure the connection between Power BI and the Azure SQL Database.

Create VNet

First, we’ll create a VNet which will be used to isolate the data services.

  • Login to https://portal.azure.com
  • Search for Virtual networks and click on the service
  • Click Create and enter the following information
    • Basics tab
      • Select a Resource Group or create a new one
      • Provide a name E.G. dhpowerbivnet
      • Select the appropriate region E.G. UK South
    • IP Addresses
      • Accept the default IPv4 address space or if not VNets exist then change to 10.1.0.0/16
      • Click Add Subnet, provide the name for the subnet E.G. PowerBISubnet and ensure the IP Address is the same as the VNet with /24 E.G. 10.1.0.0/24 then click Add
    • Click Review & Create

You should now have a VNet configured as follows (or similar depending on the IP range).


Configure Azure SQL Networking

Now that the VNet has been created, we can configure the Azure SQL Server. For this section we’ll be using an existing Azure SQL Server rather than creating a new server. However, the process will be the same if you need to create a new Azure SQL Server.

  • In the Azure portal, browse to an Azure SQL Server (not an individual SQL Database)
  • Under the Security section, select the Firewalls and Virtual Networks sub-section
  • Set the following:
    • Deny public network access: Yes
    • Minimum TLS Version: 1.2
    • Connection Policy: Redirect
    • Allow Azure services and resources to access this server: No
    • Click Save

Create Private Endpoint in Azure SQL

  • In the Azure portal, browse to an Azure SQL Server (not an individual SQL Database)
  • Under the Security section, select Private Endpoint Connections
  • Click + Private Endpoint to create a new private endpoint
  • On the Basics tab, enter the following information:
    • Select a Resource Group or create a new one
    • Provide a name E.G. dhsqlprivateendpoint
    • Select the appropriate region E.G. UK South
  • On the Resource tab, enter the following information:
    • Connection Method: Connect to an Azure resource in my directory
    • Resource Type: Microsoft.Sql.Servers
    • Resource: Select the appropriate Azure SQL Server
    • Target Sub-Resource: sqlServer
  • On the Configuration tab, , enter the following information:
    • Virtual Network: Select the VNet created in the first step
    • Subnet: The default subnet should be automatically selected
    • Integrate With Private DNS Zone: Yes
  • Click Review & Create

Once we have created the Private Endpoint we can see the internal IP address that the VNet has assigned.

  • Browse to the Azure SQL Server resource in the Azure portal
  • Under the Security section, select Private Endpoint Connections
  • Click on the Private endpoint name
  • Then click on the Network interface name
  • You will now see the internal VNet IP assigned to the Private endpoint.



Configure VNet Subnet for Power Platform

We must now add a new Subnet to our new VNet configured for use with the Power Platform.

Add Microsoft.PowerPlatform as a Resource Provider

  • In the Azure portal, browse to the subscription that you are using in the Subscriptions area
  • Under the Settings category, select Resource Providers
  • Search for Microsoft.PowerPlatform then click Register

Add New Subnet

  • In the Azure portal, browse to the Virtual network created in the first step
  • Under the Settings section, click Subnets
  • Click + Subnet to add a new subnet
    • Give the subnet a name E.G. dhpowerbisubnet
    • Set the Delegate Subnet to a service drop-down to Microsoft.PowerPlatform/vnetaccesslinks
    • Click Save

Please note that the GatewaySubnet is a subnet created by a Virtual Network Gateway to allow VPN connections from local computers to the VNet. This is out of scope for this blog.


Create VNet Data Gateway in Power Platform Admin Centre

We must now login to the Power Platform Admin Centre and create a new VNet Data Gateway connected to the new Power Platform-specific subnet created earlier.

  • Login as as admin user to the Power Platform Admin Centre
  • Click on Data (Preview) in the left-hand menu
  • Click on the Virtual Network Data Gateways tab
  • Ensure the Tenant Administration toggle switch is set to off (top-right of the Admin Centre)
  • Click on + New and enter the following information
    • Select the appropriate subscription
    • Select the resource group that the virtual network was created in
    • Select the virtual network that was created earlier
    • Select the Power Platform specific subnet created earlier
    • Provide a name for the VNet Data Gateway
    • Click Save

Once the VNet Data Gateway has been created, click the icon under the Status column. This can take a few minutes to return the gateway’s status, if all has been successful during creation then the status should show as Online.

Configure VNet Data Gateway in Power BI

We now turn our attention to Power BI where we must add the Azure SQL Database as a data source to the VNet Data Gateway.

  • Login to https://app.powerbi.com
  • Under Settings, click Manage Gateways
  • Click the ellipsis to the right of the VNet Data Gateway and select ADD DATA SOURCE
  • Provide the following information:
    • Enter an appropriate name for the data source E.G. AzureSQLDatabaseReporting
    • Select SQL Server as the data source type
    • Enter the full URL of the Azure SQL Server E.G. dhsqlserverreporting.database.windows.net
    • Enter the database name E.G. dhsqlreporting
    • Select the appropriate authentication method, E.G. OAuth.
    • Click Apply

Configure Power BI Dataset to use VNet Data Gateway

We can now configure the specific Power BI dataset to use the VNet Data Gateway. Please note that steps to create a report and upload to the Power BI Service is out of scope of this blog post. The Power BI report deployed to the service has an import connection to the Azure SQL Database.

  • In the Power BI Service, browse to the workspace containing the relevant Power BI dataset.
  • Select the ellipsis to the right of the relevant Power BI dataset and select Settings
  • Expand the Gateway Connection section and switch Use an On-premises or VNet data gateway to On
  • In the Maps to: drop-down, select the appropriate data source connection. In this case, the Azure SQL Database.
  • Click Apply

Test Data Refresh

We can now browse to the Power BI dataset and attempt a refresh, if all has been configured correctly then the dataset will refresh successfully. If the Power BI Service is unable to connect to the Azure SQL Database successfully, it is likely to result in this error message:


Conclusion

In this blog post we have walked through the steps necessary to disable access to an Azure SQL Database, configure a Virtual Network and Private Endpoint, create a VNet Data Gateway and finally to connect a Power BI dataset to the Azure SQL Database via the VNet Data Gateway. As previously noted in the Considerations section, care must be taken when securing Azure resources and an Azure Network Engineer is best placed to confirm configurations.


References