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.
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.
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.
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.
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?
- 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
- 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
- The query, data source details, and credentials are sent to the VNet data gateway
- The VNet data gateway gets the query and connects to the data sources
- The query is then sent to the data source for execution
- 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.
- 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.
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.
8. You can now setup a Data Source under VNet data gateway in Power BI portal as you do for regular sources:
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.
