Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted November 6, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Azure SQL Database Virtual Network Service Endpoints and Rules

By Marcin Policht

One of the most commonly requested Azure SQL Database features has been support for more granular control of server-level firewall settings. While it is possible to restrict their scope down to individual IP addresses for traffic routed via public Internet, the mechanism to manage it for traffic originating from Azure-resident services was implemented simply as an on/off switch. Effectively, you were forced to either disable it altogether or enable it in non-discriminative manner, regardless of the source IP address. Obviously, there were additional mitigating factors protecting your databases, starting with server-based authentication, however, considering the importance of the defense in depth information assurance approach, frequently that was deemed to be insufficient. This was particularly problematic when attempting to secure communication between Azure virtual machines hosting database client applications and Azure SQL Database instances, forcing many customers to resort to implementing SQL Server in Azure virtual machines instead. Fortunately, a recently announced public preview of Azure SQL Database and Data Warehouse VNET service endpoints and rules promises to address this limitation.

The new functionality allows you to restrict inbound traffic to an Azure SQL Database server to specific Azure virtual network subnets that you designate. Additionally, even though the server remains associated with a public IP address, traffic from private IP addresses of Azure virtual machines on the designated subnets is routed via the Microsoft backbone network, rather than via public Internet.

From the architectural standpoint, the solution consists of two separate components:

  • Virtual network service endpoints - these represent subnet-level configuration within an Azure virtual network that specify types of Azure services to which you intend to allow inbound connections. Note that this is limited to the services of the specified type within the same Azure region as the virtual network. Virtual networks and services can be in different Azure subscriptions, as long as the subscriptions are associated with the same Azure Active Directory tenant. During the preview, service endpoints can be configured for Azure SQL Database and Azure Storage.
  • SQL Database server-level virtual network rules (also referred to as Access Control List entries or simply ACLs) - these represent the service-level (Azure SQL Database server or an Azure storage account) firewall rules, which reference one or more subnets that you configured as virtual network service endpoints and for which you allowed this particular service type.

You have the option of delegating the task of implementing this functionality by leveraging Azure Role Based Access Control (RBAC). By default, the ability to manage virtual network service endpoints is part of privileges assigned to the Network Admin role while the ability to manage SQL Database server-level virtual network rules is part of the privileges assigned to the Database Admin role. Since both of these built-in roles provide a number of other privileges, you might want to consider creating a custom role if you want to delegate configuration of virtual network service endpoints and service virtual network rules to the same team or individual and, at the same time comply with the principle of least privilege.

As with any resource in a public cloud, you need to be aware of limits applicable in this case. In particular, the number of virtual network-based ACL entries for a given virtual network cannot exceed 128. It is also worth noting that this solution is available only when using Azure Resource Manager-based virtual networks (i.e. it cannot be applied to legacy virtual networks provisioned by using the classic deployment model). In addition, you cannot rely on service endpoints to control traffic flow from on-premises systems via Site-to-Site VPN or via private peering of Azure ExpressRoute. However, when using the latter, you can take advantage of its public peering capabilities. In such cases, you have the option of implementing the traditional server-level IP address-based firewall ACLs and restrict incoming traffic by creating an allow rule scoped to the public IP addresses associated with your ExpressRoute circuit.

It is important to point out that at the time of publishing this article, there is no support for using Network Security Groups (NSGs) to restrict outbound connectivity from Azure virtual network subnets or virtual machines to IP addresses representing Azure SQL Database server endpoints. Instead, you have to make sure that NSGs permit all Internet-bound traffic. This is supposed to change by allowing the use of service tags that designate groups of IP addresses associated with a particular service type (more specifically, in order to restrict outbound traffic to Azure SQL Database servers, you would create an NSG rule with the SQL service tag). Finally, keep in mind that at the time of publication of this article, Azure SQL Database Virtual Network Service Endpoints and Rules are in public preview. Effectively, they are available only in a limited number of Azure regions (East US, West US2, and West Central US) and their functionality might change before reaching general availability.

In our upcoming articles, we will step through the process of implementing this functionality.

See all articles by Marcin Policht



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM