Azure SQL DB Private Link / Private Endpoint – Connectivity Troubleshooting

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

In the article Azure SQL DB Connectivity Troubleshooting we have explored regular connectivity troubleshooting to Azure SQL DB, on this one we will explore connectivity troubleshooting using Private Link

 

In this article we are going to explore

1. What is the Private Endpoint for Azure DB?
2. Creation of Private Endpoint
3. Azure VM > Private Link
4. OnPrem VM > VPN (P2S) > Private Link
5. Azure Function > VNET integration > Private Link

 

1 - What is the Private Endpoint for Azure DB?

Azure Private Link enables you to access Azure PaaS Services (for example, Azure Storage and SQL Database) and Azure hosted customer-owned/partner services over a private endpoint in your virtual network.

 

Traffic between your virtual network and the service travels the Microsoft backbone network. Exposing your service to the public internet is no longer necessary

 

Some important information

• Private Link service can be accessed from approved private endpoints in the same region. The private endpoint can be reached from the same virtual network, regionally peered VNets, globally peered VNets and on premises using private VPN or ExpressRoute connections.

• When creating a Private Link Service, a network interface is created for the lifecycle of the resource. This interface is not manageable by the customer.

• The Private Link Service must be deployed in the same region as the virtual network.

• A single Private Link Service can be accessed from multiple Private Endpoints belonging to different VNets, subscriptions and/or Active Directory tenants. The connection is established through a connection workflow.

 

In the image, below we can see how the connection using private endpoint works. And as mentioned on the other article Azure SQL DB Connectivity Troubleshooting the Azure SQL DB clients by default will all go to the Shared public IP of the regional gateway. 

With the private endpoint you can close this public path and users can only connect from private endpoint.

 

If you have multiple Azure VNETs you will need to use VNET peering or VNET VPN between two Azure VNETs, or P2S,S2S or Express Route to connect your onprem to Azure Network

 

 

2 - Creation of Private Endpoint

To create a Private endpoint just follow up the procedure documented at https://docs.microsoft.com/en-us/azure/private-link/create-private-endpoint-portal

 

You may also want to close all public network access to make sure all connection must flow from Private Endpoint. Just need to go to Azure SQL DB instance and "Firewall and Virtual Networks" and Deny public network access.

 

Find below the procedure I've used to create a Private Endpoint

• Just need to go to "Private Endpoint Connections" and then add a Private endpoint

 

• Select the region that should be the same as the VNET region as mentioned above.
• Select the resource type "Microsoft.Sql/servers" for Azure SQL DB instance
• Select the Azure SQL DB instance you want to connect
• Select the VNET / Subnet. Notice also that during the creation you can already create a private DNS zone, that will work for Azure resources that uses the Azure DNS. We will talk more about that when doing the tests

 

 

3 - Azure VM > Private Endpoint

From an Azure VM deployed to same VNET, if we test command below on command prompt before you create the Private Endpoint.

nslookup fonsecanet-westeu.database.windows.net 

 

You will get result like below that shows that this server is using public gateway IP: 40.68.37.158. So NOT using any private IP

Server: UnKnown
Address: 168.63.129.16
Non-authoritative answer:
Name: westeurope1-a.control.database.windows.net
Address: 40.68.37.158
Aliases: fonsecanet-westeu.database.windows.net

 

After you create the Private Endpoint using the same command above you are expected to see the results below

Server: UnKnown
Address: 168.63.129.16
Non-authoritative answer:
Name: fonsecanet-westeu.privatelink.database.windows.net
Address: 10.0.1.4
Aliases: fonsecanet-westeu.database.windows.net

 

Be sure also to open outbound communication from Azure VM VNET to Private Endpoint on Local Firewall, Corporate Firewall, or Azure NSGs. For this test I've opened to allow all communication inside VNET.

*Currently (Status on 2020-04-06) redirect is not supported, so only needed 1433 port

 

 

You must use the FQDN to connect to Azure SQL DB as documented at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-private-endpoint-overview#check-connectivity-using-sql-server-management-studio-ssms

 

Use the Fully Qualified Domain Name (FQDN) of the server in connection strings for your clients. Any login attempts made directly to the IP address shall fail. This behavior is by design, since private endpoint routes traffic to the SQL Gateway in the region and the FQDN needs to be specified for logins to succeed.

 

You can also check if connection is correct using TSQL below. And we will see the client IP is the private IP assigned to Azure VM.

 

SELECT client_net_address 
FROM sys.dm_exec_connections 
where session_id = @@SPID

 

 

 

You must use the FQDN to connect to Azure SQL DB. Azure SQL DB gateway use the name to route correctly your connection to the SQL host, when information is not provided it will fail

 

If you try to connect using private endpoint IP you are going to get error like below

===================================
Cannot connect to 10.0.1.4.
===================================
A connection was successfully established with the server, but then an error occurred during the login process. 
(provider: SSL Provider, error: 0 - The target principal name is incorrect.) (.Net SqlClient Data Provider)
------------------------------
Server Name: 10.0.1.4
Error Number: -2146893022
Severity: 20
State: 0

 

You should also NOT use DB.privatelink.database.windows.net

===================================
Cannot connect to fonsecanet-westeu.privatelink.database.windows.net.
===================================
A connection was successfully established with the server, but then an error occurred during the login process. 
(provider: SSL Provider, error: 0 - The target principal name is incorrect.) (.Net SqlClient Data Provider)
------------------------------
Server Name: fonsecanet-westeu.privatelink.database.windows.net
Error Number: -2146893022
Severity: 20
State: 0

 

 

 

4 - OnPrem VM > VPN (P2S) > Private Endpoint

 

You can use some template like below to create the VPN Point to Site

• https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-configure-p2s
• https://docs.microsoft.com/en-us/azure/vpn-gateway/vpn-gateway-howto-point-to-site-resource-manager-portal

After creating the VPN, downloading and installing the VPN client and connecting to it

 

If I check name resolution

nslookup fonsecanet-westeu.database.windows.net

We can see that it is still using Microsoft internal Corporate DNS where this VM is located

Server: XXXXXXXXXXX.corp.microsoft.com
Address: 10.221.xx.xx 
Non-authoritative answer:
Name: westeurope1-a.control.database.windows.net
Address: 40.68.37.158
Aliases: fonsecanet-westeu.database.windows.net
fonsecanet-westeu.privatelink.database.windows.net

 

For this scenario you will need to use your corporate DNS to have the name resolution

https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-overview#dns-configuration

 

When connecting to a private link resource using a fully qualified domain name (FQDN) as part of the connection string, it's important to correctly configure your DNS settings to resolve to the allocated private IP address. Existing Azure services might already have a DNS configuration to use when connecting over a public endpoint. This needs to be overridden to connect using your private endpoint.

The network interface associated with the private endpoint contains the complete set of information required to configure your DNS, including FQDN and private IP addresses allocated for a given private link resource.

You can use the following options to configure your DNS settings for private endpoints:

• Use the Host file (only recommended for testing). You can use the host file on a virtual machine to override the DNS.
• Use a private DNS zone. You can use private DNS zones to override the DNS resolution for a given private endpoint. A private DNS zone can be linked to your virtual network to resolve specific domains.
• Use your custom DNS server. You can use your own DNS server to override the DNS resolution for a given private link resource. If your DNS server is hosted on a virtual network, you can create a DNS forwarding rule to use a private DNS zone to simplify the configuration for all private link resources.

Private Link resource type	Subresource	Zone name
SQL DB (Microsoft.Sql/servers)	Sql Server (sqlServer)	privatelink.database.windows.net

 

Azure will create a canonical name DNS record (CNAME) on the public DNS to redirect the resolution to the suggested domain names. You'll be able to override the resolution with the private IP address of your private endpoints.

Your applications don't need to change the connection URL. When attempting to resolve using a public DNS, the DNS server will now resolve to your private endpoints. The process does not impact your applications.

 

For this test will use HOST file solution

 

And we are able to connect fine. And as before you will be able to check you get the VPN ip range using TSQL below

 

SELECT client_net_address 
FROM sys.dm_exec_connections 
where session_id = @@SPID

 

5 - Azure Function > VNET integration > Private Endpoint

To make Azure Function to connect to private endpoint you will need to use VNET integration

https://docs.microsoft.com/en-us/azure/azure-functions/functions-create-vnet

https://docs.microsoft.com/en-us/azure/app-service/web-sites-integrate-with-vnet

 

However there are two limitation or current limitations

 

*Status on (2020-04-06)

-----------------------------------------------------------------------------------------------------------------

• There is a know limitation related to Azure DNS Private Zone where it will be needed a custom DNS
  • https://docs.microsoft.com/en-us/azure/app-service/web-sites-integrate-with-vnet#enable-vnet-integration

"After your app is integrated with your virtual network, it uses the same DNS server that your virtual network is configured with, unless it's Azure DNS Private Zones. Currently, you can't use VNet Integration with Azure DNS Private Zones."

 

-----------------------------------------------------------------------------------------------------------------

• By default the Regional Vnet integration will route traffic to a peered VNet if it is using the RFC 1918 address space  (10.0.0.0/8, 172.16.0.0/12, 192.168.0.0/16). 

You will need to add the app setting WEBSITE_VNET_ROUTE_ALL and set the value to 1. This will route all of the outbound traffic into the virtual network, it's subject to the NSGs and UDRs that are applied to your integration subnet.

-----------------------------------------------------------------------------------------------------------------

 

In this test I've created a VM with Active Directory + DNS. And on DNS I've created a Forward Lookup Zone and added manually the host

And on Virtual Network I've replaced default Azure DNS with local DNS 10.0.0.5. I've added also 168.63.129.16 (Azure DNS) as secondary DNS just in case 1st is off

 

And I've also created a sample Azure Function app that accept the connection string as parameter and was able to connect fine

 

 

You can check the source code at https://github.com/FonsecaSergio/AzureFunctionAppTestConnectivity

 

 

 

 

REF and LINKS

• https://docs.microsoft.com/en-us/azure/private-link/private-link-service-overview
• https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-overview
• https://docs.microsoft.com/en-us/azure/sql-database/sql-database-private-endpoint-overview
• https://docs.microsoft.com/en-us/azure/private-link/
• https://docs.microsoft.com/en-us/azure/app-service/web-sites-integrate-with-vnet#enable-vnet-integration