The Coeo Blog

Introducing Private Endpoints for Azure SQL Database

Written by Matt Robertshaw | 06-Apr-2020 11:15:00

Over the last few years there's been increasing pressure on Microsoft to remove the need for applications to connect to Azure Platform-as-a-Service (PaaS) services via public endpoints.  In February 2018 Microsoft announced the general availability of Virtual Network (VNet) Service Endpoints for Azure SQL Database.  VNet Service Endpoints allow organisations to isolate connections to Azure SQL Database from a subnet within a VNet, safe in the knowledge this traffic will always stay within the Azure backbone network.  It was a huge step forward, but it didn't remove the need for the public endpoint, which in fairness is what organisations were really asking for.  In September 2019 Microsoft announced a new preview feature called Azure Private Link which became generally available in February 2020.  Azure Private Link in combination with private endpoints introduces a new private connectivity method which should address customer concerns surrounding the public endpoint.  This blog post explores these new features, how they compare with VNet Service Endpoints and how private endpoints can be used to provide a secure method for connecting to Azure SQL Database.

The eagle-eyed amongst you may have noticed in the latest announcement that using Azure Private Link to connect to Azure SQL Database is in preview.  Fear not - earlier this month, Microsoft announced that connecting to this service using private endpoints via Azure Private Link is now generally available!  With that, let's start by looking at the role and purpose of these new features and what considerations there are for Azure SQL Database.

 

What is Azure Private Link?

Azure Private Link serves two purposes.  Firstly, if you're a service provider (e.g. a partner) that hosts a service in Azure for customers, it's possible to host that service via a service reference within Azure Private Link, known as the Azure Private Link service.  Whilst it's beyond the scope of this article to discuss this scenario here, it simply allows a providers service running behind an Azure Standard Load Balancer to be mapped to a private endpoint within the customers VNet.  The second purpose is to allow selected Azure PaaS services to be brought into the customers' own VNet.  This allows services such as Azure SQL Database and Azure Synapse (SQL Data Warehouse) to communicate with consuming services over a private endpoint (i.e. private IP). 

These scenarios ensure that traffic between published services and consumers only traverse the Azure backbone network and not the public internet.  It also means that partner or Azure services onboarded into your VNet can communicate over a private IP, without having to rely on public IP addresses at the source or destination.  Traditionally this hasn’t been possible, even over ExpressRoute or Site-to-Site (S2S) Virtual Private Network (VPN) tunnels!  In other words, this is a big deal!

 

What are Private Endpoints?

Private endpoints provide the interface to communicate with consuming applications via a private IP.  Private Link is therefore responsible for ensuring that traffic from a given private endpoint reaches its destination.  You may have seen Private endpoint connections available on the configuration blade for many Azure PaaS services within the Azure Portal already.  Microsoft has published a list of resources that support private linking with private endpoints which is certainly worth reviewing.  There you will see lots of old and new favourites including; Azure SQL Database, Azure Synapse Analytics, Azure Cosmos DB, Azure Data Lake Storage Gen2 plus many others.

Communication between private endpoints and consuming applications can be performed from within the same VNet, locally peered VNets, globally peered VNets and on-premises via VPN or ExpressRoute.  It's important to note that whilst Private Link can be deployed to a different region than the VNet and private endpoint, the private endpoint must be deployed within the same region as the VNet.  This makes sense given the endpoint is just a network interface with a private IP on the VNet behind the scenes.

 

Private Endpoints with Azure SQL Database

Now we know a bit more about how private endpoints interact with Private Link, how do we set them up for Azure SQL Database?

I'm pleased to say the process for creating private endpoint connections for Azure SQL Database is fairly straightforward and well documented by Microsoft, so I won’t dwell on that here, however there are a few gotchas with DNS to be aware of.  It's also worth noting this process doesn't apply to Azure SQL Database Managed Instance since it already has a private IP inside your subnet.  

When creating a private endpoint connection on Azure SQL Database, you'll be given the option of integrating your private endpoint with the Private DNS zone for the resource.  Microsoft has published its zone names for all Private Link resource types; for Azure SQL Database it's privatelink.database.windows.net.  Microsoft recommends integrating your private endpoint with the private DNS zone to ensure applications on your VNet can resolve the private IP address of the Azure SQL Server correctly.  For example, when connecting to a server, you will specify its logical server name; such as contoso-sql.database.windows.net.  However, by default connections to this server will resolve to the public endpoint, which is not what we want!  To connect over the private endpoint, a DNS forwarding rule needs to be created in Active Directory (AD) DNS to use the private DNS zone of the corresponding Private Link resource, thereby overriding the default DNS configuration.  To set up a DNS forwarding rule, follow these steps:

  1. Launch DNS on your Domain Controller.
  2. Right-click Conditional Forwarders and click New Conditional Forwarder…
  3. Specify the private DNS zone name in the DNS Domain field.   
  4. In my testing I added 168.63.129.16 in the IP address list of the master server configuration; which is a special IP address that provides access to the Azure recursive resolvers.  These enable your domain controllers to resolve host names for resources in your domain as well as Azure-provided host names (e.g. contoso-sql.database.windows.net) via forwarded requests to the recursive resolvers.  Alternatively, for testing purposes you can update the Hosts file on the server from where the connection is being established.

Once DNS has been configured, you can check the name resolves correctly by performing an nslookup against the logical server name; nslookup contoso-sql.database.windows.net.  If it's configured correctly, output as below will be returned.

As you can see in the example above, the lookup has returned a private IP address of 10.0.0.7 for my private linked database resource (via my domain controller on 10.0.0.4).  We've now confirmed that connections to contoso-sql.database.windows.net will be resolved to the private endpoint 10.0.0.7 (the private IP!).  Now connect to the server via SQL Server Management Studio (SSMS) and you should find it succeeds.

Finally, to deny access to the public endpoint completely, there's a new switch under the Firewalls and virtual networks blade for Azure SQL Server called Deny public network access.  Setting this to Yes will only allow connections to the private endpoint, whereas setting it to No will allow connections to the private or public endpoint.  If this setting is set to Yes and a calling application tries to connect to the server from outside of your VNet, the following message will be returned:

 

Private Endpoints vs. VNet Service Endpoints

Private endpoints provide the same routing capabilities as VNet Service Endpoints; they isolate the network traffic to the Azure backbone network, never traversing the public internet. When using VNet Service Endpoints, applications still have to use the public endpoint for connectivity to Azure PaaS services (including over ExpressRoute and VPNs) because up until now it's not been possible to provision a private IP within a VNet for those services. However, Private Links have removed the need for public endpoints because they perform some clever private IP addressing by bringing the PaaS service into the VNet, which means the PaaS service gets a private IP from a subnet on your VNet.  To summarise the high-level differences, see the diagram below.

Overall, it's a great development and I'm pleased to see Microsoft has now rolled it out.  Hope you found the article useful - until next time!