We are happy to announce a new PostgreSQL connector for the virtual network data gateway! You can now connect to all your PostgreSQL workloads in the cloud using basic authentication. Just select PostgreSQL in the connection type dropdown menu when creating a new connection to your virtual network data gateway from the manage gateways page on the PBI service.
Why use PostgreSQL through the vnet data gateway?
Use this option instead of the on-premises data gateway to avoid creating a DSN, creating a connection string, installing a driver, setting up data source specific configurations or downloading anything.
How do I use this?
To use this connector with vnet data gateways, you have a few options. With Power BI Desktop, you can connect and then publish. Then, log in online and create a dataset from your published file. You can enable this dataset with vnet data gateways. See below for step-by-step instructions on how to do this. This connector is also enabled for vnet data gateways in Power Platform Dataflows.
To connect using Power BI Desktop and datasets:
- Open PBI Desktop.
- Click on get data.
- If you opened the drop down, select more.
- Search for and select “PostgreSQL database”.
- Click Connect.
- In the new window, enter the server name and database name. If you need to, you can write a SQL statement to be run on the database in advanced options in the SQL statement box.
To find the server name and database name information:
a. Navigate to portal.azure.com.
b. Go to your postgreSQL database resource.
c. On the overview tab, in the top right, the server name is the server value.
d. Scroll down and expand available resources to find your database names.
e. Identify the database you want and type that name into the database box.
- Select OK when you are done.
- On the credentials page, input your user name and password.
- Select the level at which the credentials apply.
- Click Connect.
- From the navigator pane, select the tables you want to include.
- Select Load.
- In Power BI Desktop, select publish.
- If you haven’t saved your changes yet, a window will pop up asking you to save. Select Save.
- Select the workspace you want to publish to.
- Click select.
Now you’ve connected to the data source and published to your workspace. Next, we will publish this to the Power BI service where we can create a data set and connect it to the virtual network data gateway.
- Open the Power BI service to create your dataset.
- Navigate to your workspace using one of the options below.
- Make sure your workspace has premium per user enabled.
- In the Publish to Power BI pane, select the workspace you want to create your data set in.
Now you’ve published to Power BI and you’re ready to create your data set!
- Go to the Power BI service.
- Navigate to the workspace you selected when publishing by using one of the methods below.
- Make sure your workspace is set up for premium.
a. Navigate to your workspace.
b. Select the settings icon in the top right.
c. Select premium.
d. Select one of the premium license options.
e. Select save.
- Hover over the resource with type dataset.
- Select the three dot menu and select Settings.
- Click to expand the gateway connection section.
- Under use an on-premises or vnet data gateway click to turn the toggle on if it is off.
- Click the arrow to the right of the vnet data gateway you want to connect to.
- Identify the data source you want to add and click add to vnet.
- Make sure the status has changed to say running.
Congratulations you have now configured your data set to run through your vnet data gateway!
Feedback and comments
Please try it out and as always let us know what you think in the comments below!