Objective: To show database connectivity from an Azure virtual machine to PaaS Azure SQL Database using SQL service endpoints in a virtual network.

Background: For IaaS environments for internal facing systems, system designers may choose Azure SQL as their database of choice versus SQL Server in a virtual machine. A question or concern may come up is how to secure the database connectivity from the virtual network so that the connection doesn’t come through the internet. Originally, Azure SQL DB had only firewall rules based on any source IP addresses but recently there are VNet service endpoints.

For this blog, I have configured a virtual machine to connect to an Azure SQL DB using virtual network endpoints and rules so that the traffic and DB connectivity is kept within the Azure backbone.

For introduction and overview read

• Use Virtual Network service endpoints and rules for Azure SQL Database
  Virtual network rules are one firewall security feature that controls whether your Azure SQL Database server accepts communications that are sent from particular subnets in virtual networks.
• Virtual Network Service Endpoints
  Virtual Network (VNet) service endpoints extend your virtual network private address space and the identity of your VNet to the Azure services, over a direct connection. Endpoints allow you to secure your critical Azure service resources to only your virtual networks. Traffic from your VNet to the Azure service always remains on the Microsoft Azure backbone network.

Implementation Overview

1. Virtual network and subnet

   Microsoft.Sql service endpoint is enabled for the subnet such that this endpoint allows secure access to any selected Azure SQL DB. By enabling this, it allows to configure a Virtual network rule in the Azure SQL Server firewall settings.
   
   Subnet configuration to add, change and delete the service endpoint. The other option currently available is Microsoft.Storage for storage accounts.
   

2. Azure SQL Server/DB
   An important note regarding Azure SQL Database Server vs Azure SQL Database,
   the virtual network rules apply at the Azure SQL Database Server level and are inherited to its Azure SQL databases. For further details read Server-level firewall rule versus a database-level firewall rule

   Allow access to Azure Services is disabled any other Azure IP address from access.
   Firewall rules: To restrict any other access even from across the internet, there is no added client IP addresses.

   Virtual network rule: A rule to allow database connections from the desired VNet/Subnet
   
   Detailed configuration of the chosen subnet
   
   With service endpoints, the traffic uses virtual network private addresses as the source IP addresses when accessing the Azure service from a virtual network. Therefore, the VM’s private IP address is used. This allows you to access the services without the need for reserved, public IP addresses used in the SQL DB firewall rules.

Final Remark

At first when I heard of this new capability, I assumed that Azure SQL DB would be assigned a private IP address into the VNet/Subnet. And that the DB connection string would simply refer to this private IP. However, this isn’t the case. A handful of my peers thought the same. My takeaway and opinion with virtual network rules is that it appears to leverage software defined networking technology to implement a type of access control from VNet/subnet to azure services.