Secure connectivity from On-Premise to Azure Database for PostgreSQL using Point-to-Site Gateway

[ramkychan]

Ensuring secure connectivity to database resource is an important requirement and consideration for customers running in cloud environment. Today customers want to connect to their Azure Database for PostgreSQL from both inside/outside of Azure, and based on the security and compliance requirements, organizations can choose one of the options provided by Azure Database for PostgreSQL. In this blog post, we will outline detailed steps on how to connect securely from an On-Premise VM to Azure Database for PostgreSQL using Point-to-Site Gateway.

A Point-to-Site (P2S) VPN gateway connection lets you create a secure connection to your virtual network from an individual client computer. A P2S connection is established by starting it from the client computer. This solution is useful for telecommuters who want to connect to Azure VNets from a remote location, such as from home or a conference. P2S VPN is also a useful solution to use instead of S2S VPN or ExpressRoute when you have only a few clients that need to connect to a VNet.

Here is the high-level architecture diagram of how this solution works in practice:

Step1 - User connects from on-premises (over Point-to-Site VPN) by specifying Private IP address for Azure VM & port 5432.

Step 2 - NGINX is running on Azure VM and listening for traffic on port 5432

Step 3 - Traffic is forwarded by NGINX to the Azure Database for PostgreSQL as part of normal login flow.

Step by step guide to implementing this architecture

We have created one-click-deployable ARM Templates to provision the above architecture quickly.

P2S VPN clients are authenticated using native Azure Certificate Authentication. Instructions to generate and export certificates for Point-to-Site using Powershell are here. You can either generate a self-signed root certificate or use Enterprise CA solution.

Instructions to use self-signed root certificate  

1. Create a self-signed root certificate: From a computer running Windows 10 or Windows Server 2016, open a Windows PowerShell console with elevated privileges and run the following command

$cert = New-SelfSignedCertificate -Type Custom -KeySpec Signature -Subject "CN=P2SRootCert" -KeyExportPolicy Exportable -HashAlgorithm sha256 -KeyLength 2048 -CertStoreLocation "Cert:\CurrentUser\My" -KeyUsageProperty Sign -KeyUsage CertSign

NOTE: Root certificate name “P2SRootCert” should be provided as input for ARM Template parameter ‘clientRootCertName’

2. Export the root certificate public key (.cer)

[System.Convert]::ToBase64String($cert.RawData, 'InsertLineBreaks')

NOTE: The output of the above command should be provided as input for ARM Template parameter ‘clientRootCertData’

3. Generate a client certificate: Each client computer that connects to a VNet using Point-to-Site must have a client certificate installed. You generate a client certificate from the self-signed root certificate, and then export and install the client certificate. If the client certificate is not installed, authentication fails.

# Generate a client certificate from the self-signed root certificate
$clientCert = New-SelfSignedCertificate -Type Custom -DnsName P2SChildCert -KeySpec Signature -Subject "CN=P2SChildCert" -KeyExportPolicy Exportable -HashAlgorithm sha256 -KeyLength 2048 -CertStoreLocation "Cert:\CurrentUser\My" -Signer $cert -TextExtension @("2.5.29.37={text}1.3.6.1.5.5.7.3.2")

# Protect the pfx file using a password
$mypwd = ConvertTo-SecureString -String "1234" -Force -AsPlainText

# Export the client certificate as PFX
$clientCert | Export-PfxCertificate -FilePath C:\clientcertificate.pfx -Password $mypwd

NOTE: If you wish to connect to VNET using P2S from another client VM, copy paste clientceritifcate.pfx and install it using the password.

Once the template is deployed successfully, you will see the following resources in your resource group:

 

Connectivity from On-Premise Client VM

You will need to install the client certificates on the On-Premise Client VM for authenticating the P2S connections

• Click on the Virtual Network Gateway(p2sdemoGW) and click Point-to-Site Configurations.
• Click on Download VPN Client to download the VPN Client Configuration Files.

• Once the VPN Client is downloaded, extract the folder and run the following program as Administrator: WindowsAmd64\VpnClientSetupAmd64.exe. (For a 64-bit processor architecture, choose the 'VpnClientSetupAmd64' installer package. For a 32-bit processor architecture, choose the 'VpnClientSetupX86' installer package.)
• If you see a SmartScreen popup, click More info, then Run anyway.
• Click Yes on the following prompt

• On the client computer, navigate to Network Settings and click VPN. The VPN connection shows the name of the virtual network that it connects to. Click Connect.

• Click Connect

• Select the checkbox and Click Continue

• If connected successfully, you will see “Connected” status

• The ARM template deployment output provides the psql connection string to connect to the PostgreSQL Server

• Verify connection is successful

If you have trouble deploying the ARM Template, please let us know by opening an issue

Feel free to contribute any updates or bug fixes by creating a pull request

Thank you!