Microsoft Azure
Published in

Microsoft Azure

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.

Resources

--

--

Any language. Any platform. Our team is focused on making the world more amazing for developers and IT operations communities with the best that Microsoft Azure can provide. If you want to contribute in this journey with us, contact us at medium@microsoft.com

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
Akihiro Nishikawa

Akihiro Nishikawa

53 Followers

Cloud Solution Architect @ Microsoft. Passionate about Java (JVM/GraalVM) and open source technologies. All views are my own.