Securing your Azure SQL Database with a private endpoint

Some days ago I discussed with some partners the architecture of a possible datawarehouse system for external BI analysis connected to Dynamics 365 Business Central data. They created an Azure SQL database where a “data sync” with Dynamics 365 Business Central was in place and they perform BI analysis on this external database.

The raised question was after this work was: can we protect our Azure SQL Database without using a public endpoint and without traffic routed to Internet (so, not only with a firewall rule on the incoming IP addresses)?

The answer was: yes, now you can. Use a Private Endpoint.

Azure Private Endpoint is a network interface that connects you privately and securely to a service powered by Azure Private Link. Private Endpoint uses a private IP address from your VNet, effectively bringing the service into your VNet. With a private endpoint, traffic between your virtual network and the service travels the Microsoft backbone network, so exposing your service to the public internet is no longer necessary.

Some important things to remember (directly from the official MS guidelines):

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

To protect your Azure SQL Database with a private endpoint, select your SQL Server instance, click on the Private endpoint connections blade and here create a new Private endpoint:

Then select the name of the private endpoint and the region where it will be created:

In the next “Create a private endpoint” screen, set the options has following:

• Resource type: select Microsoft.Sql/servers
• Resource: select your server
• Target sub-resource: select sqlServer

In the next section (Networking configuration) select your Virtual Network (where you want to see the endpoint) and the subnet, then set Integrate with private DNS zone = Yes and set the private DNS zone as in the following picture:

Select Review + create. Now your configuration is validated and when you see the “Validation passed” message, select Create:

The private endpoint is now provisioned and when done you have a new Azure Private Endpoint resource:

The deployment has created some other resources. We have a new Network Interface in the selected Virtual Network:

And we have a Private DNS Zone with a A record that points to the Private IP of the Network Interface created by the Azure Private endpoint.

The Private Endpoint has created a Private IP and a Private DNS zone with an hostname. 

If now you connect with RDP to a VM in the same VNET and do something like:

nslookup demosqlsrv.privatelink.database.windows.net

you will see that the hostname is resolved with the Private IP which is created by the Private Endpoint for your Azure SQL Server.

Now you can also completely “close” your Azure SQL Server to the public Internet by going to the server’s Firewall blade and set Deny public network access = Yes (and leave the Allow Azure services and resources to access this server switch = No:

You can connect to your database with a standard connection string, in this case the private DNS Zone added will resolve the correct IP address.

Now you’ve fully secured your datawarehouse.