In our previous article published on this forum, we described main characteristics of two new features known as Virtual Network service endpoints and Azure SQL Database virtual network rules. Their purpose is to restrict inbound traffic targeting an Azure SQL Database server to individual Azure virtual network subnets, according to your specifications. This not only allows you to block traffic originating from the Internet or other Azure services, but in addition it ensures that even though the server remains associated with a public IP address, traffic from private IP addresses of Azure virtual machines on the subnets you specify is routed via the Microsoft backbone network. In this article, we will focus on the details of implementing this functionality. Note that at the time of authoring of this article, Virtual Network service endpoints and Azure SQL Database virtual network rules are in public preview, but they are available in all regions of Azure public cloud.
For the sake of clarity, let’s review some of the principles that govern the behavior of these two features. To start, note that both Azure SQL Database virtual network service endpoints and virtual network rules are required in order to implement the intended functionality. Virtual Network service endpoints represent designated virtual network subnets, whose attributes include the name of the Azure service that virtual machines on these subnets are able to communicate with. In the case of Azure SQL Database, the value of the attribute is set to Microsoft.Sql. Virtual network rules are part of the configuration of the corresponding service, which in our case, are individual instances of Azure SQL Database servers. (There are equivalent configurations available for Azure Storage and Azure SQL Data Warehouse). In order to configure the rules, the corresponding service endpoints must already exist.
As a quick reminder, note that it is actually possible to restrict access to an Azure SQL Database from individual virtual machines on an Azure virtual network by relying on the traditional IP-based firewall rules, which are part of the configuration settings supported by Azure SQL Database instances and their servers. However, this requires assigning public IP addresses either directly to these virtual machines or to an Azure load balancer in front of them. In this scenario, the Azure platform would rely on Source Network Address Translation (SNAT) between the private IP addresses assigned to each virtual machine and the corresponding public IP address. As a result, you could create a database or server-level firewall rule that would allow inbound traffic originating from that public IP address. Unfortunately this approach was not scalable. For one, SNAT imposes the limit on the size of number of SNAT ports (for more information regarding this topic, refer to the Microsoft article Understanding outbound connections in Azure). In addition, in order to ensure that rules remain valid, you would have to configure public IP addresses as reserved, which number is limited to 20 per subscription.
Azure SQL Database virtual network service endpoints and virtual network rules address these challenges. However, it is important to realize that both the virtual network containing the designated subnets and the Azure SQL Database server must reside in the same Azure region. In addition, unlike IP-based firewall rules that can be applied to both individual databases and servers, the virtual network rules are supported only on the server level.
In our walkthrough, we will start by creating an Azure virtual network. You can easily perform this task from the Azure portal (alternatively, you can use an Azure QuickStart template available from GitHub or use scripting via Azure PowerShell or Azure CLI for this purpose). When using the Azure portal, click New in the upper left corner, on the New blade, click Networking, and in the list of featured networking services click Virtual network. On the Create virtual network blade, specify the following settings and click Create:
- Name: any valid name (it must be unique within the same resource group). Set this value to VNet1
- Address space: an IP space (in general, you should choose a unique IP address space, not overlapping with IP address spaces of other virtual networks). In this case, use 10.0.0.0/20
- Subscription: the name of your Azure subscription
- Resource group: create a new resource group or use an existing one. Create a new resource group named VNets-RG
- Location: an Azure region that will be hosting the Azure SQL Database server
- Subnet name: any valid name. Set this value to subnet-0
- Address range: an IP address range within the IP address space of the virtual network. Use 10.0.0.0/24
- Service endpoints (preview): Enabled
- Services: select the Microsoft.Sql checkbox
Once the deployment completes, create another subnet within the newly created virtual network, but this time without enabling the service endpoint. To accomplish this, in the hub menu on the left-hand side of the portal, click Virtual networks. On the Virtual networks blade, click VNet1 and, on the VNet1 blade, click Subnets. On the VNet1 – Subnets blade, click + Subnet. On the Add subnet blade, specify the following settings and click OK:
- Name: subnet-1
- Address range: 10.0.1.0/24
- Service endpoints (Preview): ensure that 0 selected appears in the Services drop-down list.
Next, create an Azure SQL Database, with the server located in the same Azure region as the virtual network. To accomplish this, in the Azure portal click New in the upper left corner, on the New blade, click Databases, and in the list of featured database services click SQL Database. On the SQL Database blade, specify the following settings and click Create:
- Database name: any valid name (it must be unique within the same server). Set this value to db1
- Subscription: the name of the same Azure subscription where you created the virtual network. You can actually use different subscriptions, as long as both of them are associated with the same Azure Active Directory tenant.
- Resource group: create a new resource group or use an existing one. To simplify delegation of management via Role Based Access Control (RBAC), create a new resource group named SqlDBs-RG
- Server: create a new server with the following settings:
- Server name: a valid, unique name in the .database.windows.net DNS namespace
- Server admin login: srvadmin
- Password: Pa55w.rd1234
- Location: the same Azure region hosting the newly created virtual network
- Want to use SQL elastic pool?: No
- Pricing tier: Basic
- Collation: accept the default setting of SQL_Latin1_General_CP1_CI_AS
Now it is time to configure the Azure SQL Database server firewall rules. In the hub menu of the Azure portal, click SQL databases, on the SQL databases blade click db1, and on the db1 blade click Set server firewall. On the Firewall settings blade, click Add existing virtual network (note that you also have the option of creating a new virtual network directly from this interface). On the Create/Update blade, specify the following settings and click OK:
- Name: any valid name. Set this value to VNet1-rule
- Subscription: the name of your Azure subscription
- Virtual network: VNet1 in the VNets-RG resource group
- Subnet name: subnet-0 (10.0.0.0/24). Note that you also have the option of selecting subnet-1 (10.0.1.0/24). This will automatically enable the Microsoft.Sql service endpoint on that subnet, as long as you have sufficient permissions to perform this task. However, keep in mind that enabling a service point this way is subject to an up to a 15-minute delay.
This completes the walkthrough. At this point, any virtual machine that you deploy to the VNet1 virtual network will have direct network connectivity to any database hosted on the newly deployed server.
