Lockdown your Power BI — Azure SQL Connection with VNet Data Gateway

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that handles most of the database management functions such as upgrading, patching, backups, and monitoring without user involvement. Azure SQL Database is always running on the latest stable version of the SQL Server database engine and patched OS with 99.99% availability. (cit.)

Public Endpoint with no filter

ASQL is a cloud SQL Server instance with a public endpoint (switched on by default) that can be accessed by whitelisted IPs under its own firewall.

Cloud services like Power BI could have many IPs that change over time so it could be difficult to list and maintain all of them under the whitelist. If you wish to create a single rule that won’t need any future maintenance you could setup it to allow all the IPs.

Image showing AllowEveryone rule in ASQL firewall

Obviously this makes your firewall useless and any IP in the world will bypass it, therefore this should be used only in prototyping/test environments.

Public Endpoint + Allow Azure services

You could add some more security to the previous attempt removing the rule “AllowEveryone” and allowing only the Azure services.

Image showing Allow Azure services YES

This kind of setup it’s pretty straightforward, but in this way you’re obviously giving access to all Azure IPs that could belong also to other companies’ resources (not only Power BI IPs). We’re not in an optimal scenario yet.

VNET + On-Premises Data Gateway

At the time of writing, the only GA way to restrict access to ASQL to only selected Power BI workloads (i.e. queries coming from your Power BI tenant) is to create a private endpoint for ASQL and to install an On-Premises Data Gateway in a Azure Virtual Network able to connect to this endpoint.

With this setup the Power BI will use the Gateway to reach ASQL trough the private endpoint and ASQL firewall can be instructed to prevent any kind of access from public networks.

Image showing Deny public network access YES

The only drawback of this approach is you should consider effort to install, maintain and troubleshoot this additional server in your VNET and obviously you’ve at least 1 additional Windows License to purchase/assign.

It could make sense to have an additional server in your farm if you need to connect to On-Premises data, but there could be a better solution especially if your company is embracing a “serverless philosophy”.

VNet Data Gateway (Preview, Premium only)

In Q2 2021 Microsoft has released a new functionality called VNet Data Gateway that enables to connect to Azure data services within a VNet without the need to install an additional server running On-Premises Data Gateway.

Image showing PP VNet Service connecting to Azure data services

Azure SQL is obviously an Azure data service that means VNet Data Gateway could be used to privately connect to it leveraging private endpoint.

What do you need to embrace this scenario?

  • Power BI tenant
  • Power BI Premium subscription or Power BI Premium Per User license (at the time of writing this is a Premium Only Feature)
  • Azure SQL Database
  • Private endpoint for Azure SQL
  • Azure Virtual Network with 2 subnets
  • Subnet dedicated to VNet Data Gateway
  • Subnet that links to Azure SQL Private Endpoint

How does it work behind the scenes?

Image showing VNet data gateway architecture
  1. When you request a dataset refresh Power BI cloud service kicks off a query and sends the query, data source details, and credentials to the Microsoft Power Platform VNet service. You don’t have to worry about all the architecture, everything is managed by Microsoft at this stage
  2. The Microsoft Power Platform VNet then securely injects a container running the VNet data gateway into the subnet. In other words, Microsoft is installing the VNet data gateway unattended using a container. This VNet data gateway can now connect to data services accessible within this subnet
  3. The query, data source details, and credentials are sent to the VNet data gateway
  4. The VNet data gateway gets the query and connects to the data sources
  5. The query is then sent to the data source for execution
  6. After execution, the results are sent to the VNet data gateway and the Microsoft Power Platform VNet service securely pushes the data from the container to the cloud service

Notes (at the time of writing)

  • Every VNet data gateway has 1 CPU and 6 GB of RAM
  • Installing the container in your VNET delay your refresh by minutes, but it has 2 hours Time-to-live (TTL) that means you won’t experience “reallocation delay” performing multiple refresh in this time period. As you can see in the following image the same Dataset refresh took 3 minutes on first attempt and few seconds on other attempts

Installing a VNet Data Gateway requires some steps that are described in the docs.

I believe this is a very common scenario, and there are many companies that will adopt this as soon as it becomes GA.

Steps to complete in order to make it work aren’t too much but manual work is always slower and error prone, therefore I thought it could be useful an “infrastructure as code” (using Az PowerShell module) approach to quickly setup a testing environment or to apply VNet Data Gateway to an existing resource set.

  1. Let’s connect to the subscription

2. Let’s setup our parameters

3. We create Resource Group, Azure SQL Database and Virtual Network with a subnet dedicated to VNet Data Gateway. This subnet must delegated to Microsoft.PowerPlatform provider that must be installed in case it’s not already done

4. At this stage VNet dedicated subnet is ready to host VNet data gateway. At the time of writing the only way to setup it is via UI (follow this link)

5. Now we can setup a private endpoint for our ASQL database, and then we disable the Public Network Access. Private link can’t be in the VNet data gateway subnet, it must be in different subnet

6. To drive the connection to the right IP to your private endpoint need to setup DNS record accordingly.

Image showing DNS setup

7. The easiest way to test your setup is to provision a VM in the same subnet of the private link and perform and nslookup command to see if the name is resolved in the private network.

Image showing an nslookup commnad over private endpoint resolving internal ip

8. You can now setup a Data Source under VNet data gateway in Power BI portal as you do for regular sources:

Image showing dataset setup in Power BI portal

10. Final step is to map the report’s dataset to the gateway’s data source and finally run the refresh

Conslusions

VNet Data Gateway is a brand new feature that enables to connect to azure data services using Microsoft backbone without public internet connection. Setup could be done manually but with PowerShell Az module it can be automated.

--

--

--

Container of my adventures wrangling data

Recommended from Medium

This is How Blameless Integrates with JIRA

How to Measure Network Performance: 5 Performance Metrics

How to Measure Network Performance: 5 Performance Metrics

OpenShift 4 New URL for Console and Monitoring WEB UI

Inside the Multiracial, Intergenerational Fight to Keep 124 Families Housed in LA Chinatown

Ruby on Rails Associations and Basic Rails Project

Python Hidden Features That You Probably Never Heard Of

hidden features of python

Why OKRs fail?

[7] [ProjectX] — Automating Deployment using AWS CLI

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Perico Riccardo

Perico Riccardo

BI & Power BI Engineer since 2010 — Data and me in a complicated relationship — Hard Rock and Heavy Metal addicted

More from Medium

Handling Errors in DAX — Power BI

Introduction to PowerBI Custom Visual Development

Using a geofence in Power BI

Fixing Query Folding with SQL Server in Power BI for Incremental Refresh