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.
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.
This blog is part of Azure Week. Check it out for more great content!
About the Author:
Microsoft MVP, Microsoft Certified Solution Developer (MCSD), Microsoft Certified DevOps Engineer, MCSA on Azure Development and Administration, MCAD, MCTS on Microsoft Dynamics NAV/Dynamis 365 Business Central, MCTS on Sharepoint, MCTS on SQL Server, and a long-time expert on other Microsoft-related technologies.
He has a Master Degree in Computer Engineering from Politecnico of Turin. He works as a Chief Technical Officer and Solution Architect for EID NAVLAB. His main activity is architecting and developing enterprise solutions based on the entire stack of Microsoft technologies (Microsoft Dynamics 365 Business Central, Azure cloud and distributed .NET applications).
He works as a full-time senior consultant (15+ years of international Dynamics NAV/Dynamics 365 Business Central projects) and he is available for architecting solutions based on Microsoft’s ERP, as well as for database tuning and optimization (performance and locking management) and for architecting cloud solutions and distributed applications on the Azure platform.
He’s the author of various Microsoft-related books for Packt Publishing, he’s official trainer for Microsoft Italy and Microsoft Western Europe and he’s often a speaker on technical conferences around Europe.
Stefano is one of the moderators of the official Microsoft Dynamics 365 Community (NAV and Business Central) and one of the main contributors. In 2018 he was awarded as a Microsoft Dynamics Community Star.
Reference:
Demiliani, S. (2021). Securing your Azure SQL Database with a private endpoint. Available at: https://demiliani.com/2021/01/22/securing-your-azure-sql-database-with-a-private-endpoint/ [Accessed: 7th July 2021].
