Configure Paginated Power BI Reports to use a VNet Gateway

Part I - Data Source and the VNet Gateway

What is a VNet data gateway? According to Microsoft:

The virtual network (VNet) data gateway helps you to connect from Microsoft Cloud services to your Azure data services within a VNet without the need of an on-premises data gateway. The VNet data gateway securely communicates with the data source, executes queries, and transmits results back to the service.

The VNet gateway went into public preview in Aug 2022. For organizations that store data in the cloud (Azure data services) and want to expose that data to other cloud services (Power BI) without a public IP, they can do that now with a VNet gateway. In this example we are connecting Power BI reports in the service back to an Azure SQL Database via the VNet gateway.

Chances are the person reading this document will not be the person setting up the VNet gateway in Azure, to get with your systems and networking teams to get the VNet gateway setup before you do anything else.

Power BI Setup

Make sure you are a Power BI admin in the tenant. Log in to app.powerbi.com with your admin account. Click the gear in the topmost right corner, up around your account avatar, and click on Manage Gateways:

You should see three options on that page, Data Sources, On-premises data gateways, and Virtual network data gateways. We want to select the Virtual network data gateways. This is the VNet gateway setup by your cloud administrators. This is what will allow us to connect our Power BI datasets to our data sources in our Azure SQL database.

If you don't see anything in the virtual gateway list, make sure you turn on the option in the top right corner, it’s a toggle named Tenant administration for gateways:

If your admins configured the VNet gateway(s) and you have the correct permissions, you should now see your VNet gateway(s) listed here. Now we are going to navigate back to the list of data sources so we can add a new one that will use our gateway.

At the very top of the screen directly about the Data sources option, click on "New" to add a new data source. That will bring up the following window on the right of your screen:

Click the dropdown for the Gateway cluster name option, you should see a list of all available VNet gateways. Select the target VNet gateway--once your select a gateway a new option will appear in the window named Data source type. Give your data source a name and then select the correct data source type from the drop-down list. We are selecting SQL Server for our Azure SQL database.

Once you have selected the type, you will now see new fields to configure the connection. Add your server connection string, the same thing you use to connect to SQL from SSMS. We are using the basic authentication method; this will allow us to use a SQL Server account we have configured in our instance. It is preferred to use OAuth2. Once you have finished setting up the data source you should now see it listed on the page. Before you can use it, you need to assign permissions to the data source. Hover over the data source name and select Manage users.

Typically, you would want to setup a security group with all users who need access to this data source. Once you add the users you are now ready to use the data source. Next, we need to link this up to the paginated report.

Part II - Configure Paginated Reports to Use Data Source on VNet Gateway

Paginated reports (rdl) are a little different than your regular Power BI reports (pbix) in that paginated reports don't have a separate dataset; the dataset and the report are a single asset in Power BI.

Once you have published your paginated report from Power BI Report Builder app, go to the target workspace and find the report you published. On your report, click the three vertical dots for more options and then select Manage. This will take you to a screen where you can configure your data source via the gateway.

There is an implicit relationship detected by Power BI. It looks at the data source you configured in your report and will match that up with a gateway that uses that data source underlying connection. So if you setup you report using a dataset that you have not yet configured a data source for, it might give you problems.

Select the option to Use an On-premises or VNet data gateway. Expand the target gateway if you haven't already and under the Maps to option, select the data source we created in the part I of this guide. It should look something like this: