Skip to content
/ Michaël Hompus

, , , , , , , , ,
In a previous article I described how to configure an Azure SQL database failover group for high availability across multiple regions. But what if you want to limit network traffic to a database in this failover group to only your private networks? In this article I show how to make a SQL database failover group reachable via the Private Link service and make sure the database stays reachable after a failover.

In a previous article I described how to configure an Azure SQL database failover group for high availability across multiple regions.

But what if you want to limit network traffic to a database in this failover group to only your private networks?

In this article I show how to make a SQL database failover group reachable via the Private Link service and make sure the database stays reachable after a failover.

Note

This article will continue on the environment created in the previous article where I described how to create an Azure SQL Database Failover Group with the Azure CLI.
The examples are using the Azure CLI in a bash shell.

Creating a virtual network

First create a virtual network which can be used to access the SQL database privately.

Azure CLI
az group create --name "hompus-vnet-we-rg" \
                --location "westeurope"


az network vnet create --resource-group "hompus-vnet-we-rg" \
                       --name "hompus-we-vnet" \
                       --location "westeurope" \
                       --address-prefix "10.164.0.0/16" \
                       --subnet-name "default" \
                       --subnet-prefix "10.164.0.0/24"

Controlling traffic from and to private endpoints is not possible with network security groups (NSG).

However, to connect a private endpoint to a subnet, the private endpoint network policy has to be disabled explicitly. This is done with the az network vnet subnet update command.

Azure CLI
az network vnet subnet update --resource-group "hompus-vnet-we-rg" \
                              --name "default" \
                              --vnet-name "hompus-we-vnet" \
                              --disable-private-endpoint-network-policies true

Creating the private endpoint

For each of the SQL servers in the failover group we need to add a private link. First retrieve the id of the server using the az sql server show command.

To create the private endpoint, use the az network private-endpoint create command. Both the name and connection name need to be provided, which can be the same.

Specify the SQL server resource id and the name of the subnet that are on both sides of the connection.

Do the same for the other SQL server in the failover group.

Azure CLI
# West Europe
sqlServerIdWE=$(az sql server show --resource-group "hompus-db-we-rg" \
                                   --name "hompus-db-we-server" \
                                   --query "id" \
                                   --output "tsv")


az network private-endpoint create --resource-group "hompus-vnet-we-rg" \
                                   --name "hompus-db-we-server-pe" \
                                   --connection-name "hompus-db-we-server-pe" \
                                   --private-connection-resource-id $sqlServerIdWE \
                                   --group-id "sqlServer" \
                                   --vnet-name "hompus-we-vnet" \
                                   --subnet "default"


# North Europe
sqlServerIdNE=$(az sql server show --resource-group "hompus-db-ne-rg" \
                                   --name "hompus-db-ne-server" \
                                   --query "id" \
                                   --output "tsv")


az network private-endpoint create --resource-group "hompus-vnet-we-rg" \
                                   --name "hompus-db-ne-server-pe" \
                                   --connection-name "hompus-db-ne-server-pe" \
                                   --private-connection-resource-id $sqlServerIdNE \
                                   --group-id "sqlServer" \
                                   --vnet-name "hompus-we-vnet" \
                                   --subnet "default"

Creating the private DNS zone

When any application connected to the private network resolves the DNS name hompus-db-group.database.windows.net the response will contain the external IP-address. This defeats the purpose of the network and endpoint.

bash
michael@hompus-we-vm:~$ nslookup hompus-db-group.database.windows.net


Non-authoritative answer:
hompus-db-group.database.windows.net
                canonical name = hompus-db-we-server.database.windows.net.
hompus-db-we-server.database.windows.net
                canonical name = hompus-db-we-server.privatelink.database.windows.net.
hompus-db-we-server.privatelink.database.windows.net
                canonical name = dataslice9.westeurope.database.windows.net.
dataslice9.westeurope.database.windows.net
                canonical name = dataslice9westeurope.trafficmanager.net.
dataslice9westeurope.trafficmanager.net
                canonical name = cr7.westeurope1-a.control.database.windows.net.
Name:   cr7.westeurope1-a.control.database.windows.net
Address: 52.236.184.163

To resolve the correct IP-address, create a private DNS zone using the az network private-dns zone create command. The name must be privatelink.database.windows.net. Connect the created DNS zone to the virtual network with the az network private-dns link vnet create command.

A linked private DNS zone allows virtual machines on the virtual network to be automatic added as entries in the DNS zone. This is not relevant for the private link DNS zone, so this option can be disabled.

Azure CLI
az network private-dns zone create --resource-group "hompus-vnet-we-rg" \
                                   --name "privatelink.database.windows.net"


az network private-dns link vnet create \
                                --resource-group "hompus-vnet-we-rg" \
                                --name "pdns-link-hompus-we-vnet" \
                                --zone-name "privatelink.database.windows.net" \
                                --virtual-network "hompus-we-vnet" \
                                --registration-enabled false

The next step is to add the private endpoints to the DNS zone. This does not require knowledge of the actual IP-addresses that have been assigned on the network. With the az network private-endpoint dns-zone-group create command the endpoint is registered, and the private IP-address will be resolved.

Terminal window
# West Europe
az network private-endpoint dns-zone-group create \
                          --resource-group "hompus-vnet-we-rg" \
                          --name "hompus-db-zone" \
                          --endpoint-name "hompus-db-we-server-pe" \
                          --private-dns-zone "privatelink.database.windows.net" \
                          --zone-name "hompus-db-group"


# North Europe
az network private-endpoint dns-zone-group create \
                          --resource-group "hompus-vnet-we-rg" \
                          --name "hompus-db-zone" \
                          --endpoint-name "hompus-db-ne-server-pe" \
                          --private-dns-zone "privatelink.database.windows.net" \
                          --zone-name "hompus-db-group"

Resolving the private endpoint

After the registration is done, any application connected to the private network will resolve the DNS name hompus-db-group.database.windows.net to the internal IP-address.

The DNS name for hompus-db-group.secondary.database.windows.net will point to the other server in the failover group.

bash
michael@hompus-we-vm:~$ nslookup hompus-db-group.database.windows.net
Server:         127.0.0.53
Address:        127.0.0.53#53


Non-authoritative answer:
hompus-db-group.database.windows.net
                canonical name = hompus-db-we-server.database.windows.net.
hompus-db-we-server.database.windows.net
                canonical name = hompus-db-we-server.privatelink.database.windows.net.
Name:   hompus-db-we-server.privatelink.database.windows.net
Address: 10.164.0.4
bash
michael@hompus-we-vm:~$ nslookup hompus-db-group.secondary.database.windows.net
Server:         127.0.0.53
Address:        127.0.0.53#53


Non-authoritative answer:
hompus-db-group.secondary.database.windows.net
                canonical name = hompus-db-ne-server.database.windows.net.
hompus-db-ne-server.database.windows.net
                canonical name = hompus-db-ne-server.privatelink.database.windows.net.
Name:   hompus-db-ne-server.privatelink.database.windows.net
Address: 10.164.0.5

After executing a failover, to DNS entries have swapped servers.

bash
michael@hompus-we-vm:~$ nslookup hompus-db-group.database.windows.net
Server:         127.0.0.53
Address:        127.0.0.53#53


Non-authoritative answer:
hompus-db-group.database.windows.net
                canonical name = hompus-db-ne-server.database.windows.net.
hompus-db-ne-server.database.windows.net
                canonical name = hompus-db-ne-server.privatelink.database.windows.net.
Name:   hompus-db-ne-server.privatelink.database.windows.net
Address: 10.164.0.5
bash
michael@hompus-we-vm:~$ nslookup hompus-db-group.secondary.database.windows.net
Server:         127.0.0.53
Address:        127.0.0.53#53


Non-authoritative answer:
hompus-db-group.secondary.database.windows.net
                canonical name = hompus-db-we-server.database.windows.net.
hompus-db-we-server.database.windows.net
                canonical name = hompus-db-we-server.privatelink.database.windows.net.
Name:   hompus-db-we-server.privatelink.database.windows.net
Address: 10.164.0.4

This proves that connections on the virtual network will use the private link to the SQL database, no matter which one is the current primary or secondary in the failover group.

It is also possible to connect virtual networks from other regions to the same SQL failover group, just iterate the same steps for each region.

Filed under Azure, SQL
Last update: