Secure Access to Azure SQL Servers for Power BI

Premier Developer

Premier

Premier Developer Consultant Jean Hayes outlines a strategy for controlling access to Azure SQL Servers used by Power BI.


When provisioning an Azure SQL Server for Azure SQL DB or Azure Synapse Analytics (formerly known as Azure SQL Data Warehouse), organizations can allow all or no access from other Azure resources. This configuration setting is available on the Firewall and virtual networks settings on the Azure SQL server:

When using Azure SQL DB or Azure Synapse as a Power BI data source, configuring this setting to “ON” allows the Power BI service to refresh data from the server’s databases without configuring a gateway. However, it allows traffic from all Azure resources and services to your database(s), not just resources in your Azure subscription. Database credentials restrict access to your databases, but the inability to restrict traffic to only your Azure resources may not be acceptable to some organizations.

So how do you refresh Power BI datasets in the Power BI service from Azure SQL server resources while restricting traffic to specific resources? By using the On-Premises Data Gateway and SQL endpoints. This allows you to lock down traffic to Azure SQL resources by setting “Allow Azure services and resources to access this server” to OFF, and use the gateway to provide secure access between Power BI and Azure SQL database. This is similar to the way you would provide Power BI access to on-premises SQL Server instances.

This requires the ability to create and configure a VM in Azure and to configure data gateways in the Power BI service.

A screenshot of a cell phone Description automatically generated

The steps involved are:

  1. Provisioning the VM in a VNet. See recommendations and requirements for the gateway server.
  2. Adding a Service endpoint on your VNet for SQL Server

A screenshot of a cell phone Description automatically generated

  1. Adding an Outbound Rule on the Network Security Group to allow outbound traffic to the Power BI Service. The allowed destination port ranges are 443, 5671, 5672, and 9350-9354.

A screenshot of a computer Description automatically generated

A screenshot of a cell phone Description automatically generated

A picture containing screenshot Description automatically generated

  1. Installing the On-Premises Gateway on the VM
  2. Configuring the gateway in the Power BI Service as you would for an on-premises SQL Server instance.

And that’s it! You can now access your Azure SQL server resources to Power BI while limiting traffic to other Azure resources.

2 comments

Leave a comment

  • Ivan Maria
    Ivan Maria

    Thanks for the post Jean,

    This is really useful, but I think there should be a cleaner way to allow PBI dedicated capacity to connect to Azure SQL without allowing all Azure services, similar to the VNET integration feature in App Services.

    Also, regarding the VM with the PowerBI gateway, have you found any way to automate the configuration of the VM? In my tests I found no way of performing an unattended installation of the gateway, meaning each VM needs to be configured manually. I included two VMs in the gateway cluster to provide some reliability, but without any way to automate the gateway configuration it still feels too shaky.