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.
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.
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.
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.
# 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.
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.
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.
# 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.
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
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.
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
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.
