Access from Power BI Service to Managed Database which is connected to VNet via Private Endpoint
[As of June 20, 2021]
The original article is here in Japanese.
Inquiry
Some person asked me about the following topic.
We use Azure Database for PostgreSQL (Single Server) to store several data, and plan to use Power BI Service to analyze the stored data. Our database instance is connected to VNet via Private Endpoint, so Power BI Service cannot connect to our database directly. How should we configure? And we would like to enable on-demand data refresh …
On premise Data Gateway would work for this. It runs on Windows, so we have to deploy and configure each service like the following diagram in case of Azure. Even if using service endpoint instead of private endpoint, the same configuration is applicable.
Configuration
Drivers
We have several options to connect to database. In this case, we choose ADO.NET Data Provider (Npgsql) and ODBC driver (psqlodbc). If DirectQuery is required, the only option is Npgsql, since most ODBC drivers including psqlodbc do not support DirectQuery.
We should install database driver(s) before installing and configuring Power BI Desktop and On premise Data Gateway, especially Npgsql.
- We have to choose Npgsql 4.0.10 or earlier version due to .NET runtime (as of June 20, 2021). If using the later version, connection from On premise Data Gateway fails. (see below).
- On premise Data Gateway loads Npgsql when starting the gateway. If installinig Npgsql while the gateway runs, the error “Npgsql is not installed” might happen. In this case, we have to stop and restart On premise Data Gateway.
- The same version of psqlodbc/Npgsql should be installed to nodes where Power BI Desktop and On premise Data Gateway run.
- In case of using ODBC driver, data source for Power BI Desktop/On premise Data Gateway should be created with ODBC data source configurator.
Power BI Desktop
When using Npgsql based dataset, click Azure > Azure Database for PostgreSQL or Database > PostgreSQL database to configure connection. If needed, we also choose “DirectQuery” instead of “Import”. In this case, we choose “DirectQuery”.
When using ODBC based dataset, we have to set data source name (DSN) dropdown to (None) and specify DSN with connection string (see below).
On premise Data Gateway
The latest On premise Data Gateway should be installed. For personal use, we can use personal mode, but we choose standard On premise Data Gateway. After installation and association with Power BI Service, we can configure connections for Power BI Service via On premise Data Gateway.
Power BI Service
Clicking menu and navigating Settings > Manage gateways, the gateway associated with Power BI Service is shown. In this case, we can see “gw-pgql-powerbi” associated with this Power BI Service.
If needed, we can download On premise Data Gateway from Download > Data Gateway.
The procedure to add data source is listed below.
- Click menu, which appears right side of On premise Data Gateway.
- Click “ADD DATA SOURCE”.
- Depending upon drivers, choose PostgreSQL (when using Npgsql) or ODBC (when using psqlodbc).
When using Npgsql, the following configuration is required.
- Specify server (FQDN), database, username, and password.
- Click “Apply”, and connection test runs. We have to verify connection is successful.
When using psqlodbc, the following configuration is required.
- DSN which was already configured via ODBC data source on Windows is specified. If DSN is PostgreSQL35W, the following format should be used.
dsn=PostgreSQL35W- Choose Windows authentication, and specify username and password.
- Verify connection.
Finally, we associate published dataset(s) with the deployed gateway. Click menu which appears right side of data source (in this case, “testreport”), and select “Settings” to open configuration page.
In Gateway connection page, we choose data source and gateway, and click “Apply”. From this page, we can see data source configuration vary depending upon drivers.
That’s it!
Test
Open published report on Power BI Service. If DirectQuery is enabled, we can see report(s) shown with the latest data.
As I mentioned, most ODBC drivers do not support DirectQuery. After refreshing data (on demand or scheduled), we can see report(s) based on the latest data.
Conclusion
We can see Power BI Service connect managed database through On premise Data Gateway. Some configurations are tricky, but they are not difficult.
