Vnet IntegratedPostgreSQL Integration with Power BI App
Case Scenario
Here is the case scenario, we have a database (PostgreSQL) running on Azure that has VNET integration enabled and we need to access the same database from the Power BI.
We have summarised the process into four different sections outlined below:
1. Part 1 — Creating Virtual Network and PostgreSQL
2. Part 2 — Creating and Configuring Virtual Network Gateway (Point-to-Site)
3. Part 3 — Installing ODBC driver for remote connection to PostgreSQL DB
4. Part 4 — Configuring a Data Gateway in Power BI
Creating Virtual Network and PostgreSQL Flexible Server
Azure Database for PostgreSQL Flexible Server supports two types of mutually exclusive network connectivity methods.
- Public access (allowed IP addresses)
- Private access (VNET Integration)
In this blog, we will focus on the creation of a PostgreSQL Flexible Server with Private access (VNet integration) using the Azure portal. With Private access (VNet Integration), we can deploy our flexible server into our own Azure Virtual Network. Azure Virtual Networks provide private and secure network communication. With private access, connections to the PostgreSQL server are restricted to our virtual network.
NOTE: We can deploy our flexible server into a virtual network and subnet during server creation. After the flexible server is deployed, we cannot move it into another virtual network, subnet or Public access (allowed IP addresses).
- Create a Virtual Network
1. Sign in to the Azure portal (https://portal.azure.com/).
2. Select Create a resource in the upper left-hand corner of the portal.
3. In the search box, enter Virtual Network. Select Virtual Network in the search results.
4. On the Virtual Network page, select Create.
5. In Create virtual network, enter or select required information in the Basics tab:
6. Select the IP Addresses tab or select the Next: IP Addresses button at the bottom of the page.
7. In IPv4 address space, select the existing address space and change it to 10.1.0.0/16.
8. Select + Add subnet, then enter an appropriate Subnet name (Ex:MySubnet) for Subnet name and 10.1.0.0/24 for Subnet address range.
9. Select Save.
10. Select the Security tab or select the Next: Security button at the bottom of the page.
*BastionHost: Disable
*DDoS Protection Standard: Disable
*Firewall: Disable
11. Select the Tags tab or select the Next: Tags button at the bottom of the page.
12. Select the Review + create tab or select the Review + create button.
13. Select Create.
- Create Azure Database for PostgreSQL Flexible Server in an already existing virtual network
Prerequisites:
*The virtual network and subnet should be in the same region and subscription as our flexible server.
*Delegate a subnet to Microsoft.DBforPostgreSQL/FlexibleServers. This delegation means that only Azure Database for PostgreSQL Flexible Servers can use that subnet. No other Azure resource types can be in the delegated subnet.
*Add Microsoft.Storage to the service endpoint for the subnet delegated to Flexible servers.
- Select Create a resource (+) in the upper-left corner of the azure portal.
- Select Databases > Azure Database for PostgreSQL. We can also enter PostgreSQL in the search box to find the service.
- Select Flexible server as the deployment option.
- Fill out the Basics form.
5. Go to the Networking tab to configure connection options to our server.
6. In the Connectivity method, select Private access (VNET Integration). Go to Virtual Network and click Manage selected virtual network.
7. Select + Add subnet, then enter psql-subnet for Subnet name and 10.1.1.0/24 for Subnet address range. Click Save.
8. Under Private DNS Integration, by default, a new private DNS zone will be created using the server name. Optionally, we can choose the subscription and the Private DNS zone from the drop-down list.
9. Select Review + create to review our flexible server configuration.
10. Select Create to provision the server. Provisioning can take a few minutes.
If you found this interesting or have any feedback, please let me know in the comments section.
About the Author:
Raghavendra BN is a DevOps Engineer, currently working in Version 1’s Foundation’s team. Follow Version 1 and Raghavendra BN for more blogs around Microsoft Azure and Azure DevOps.
