This article is contributed. See the original author and article here.

My customer wanted to lock down their Azure SQL access very, very tightly. The server should have no public endpoints whatsoever, and the only access to it should be from a specific set of trusted IP addresses. This is a common requirement, especially for customers in heavily-regulated industries such as finance and healthcare.

Instead of relying on the firewall built in to Azure SQL Server, which is specific that that instance, they preferred to rely on the Conditional Access Policy (CAP) capabilities within Azure Active Directory (AAD). This would allow them to ensure that authentication requests for all Azure resources could be validated and controlled from a single place, that being, AAD.

This article will describe the challenges of setting up this environment and provide at least one architecture to solve it. I’ll show how to have an Azure VM running SQL Server Management Studio (SSMS) that authenticates against AAD and accesses Azure SQL via a Private Endpoint. None of these services will have internet access.

Note that “Locations” in AAD entail IP ranges in CIDR format. Fully qualified domain names (FQDN) are not allowed.

One thing to remember: all network traffic that flows from one Azure service to another, whether it’s in the same region or different, flows across the private Microsoft network. For example, if a VM in one region accesses an Azure SQL Server in another region via the latter’s public IP, that traffic occurs across private Microsoft fiber, even though a “public Internet IP” is used on the Azure SQL instance.

Locking Down Azure SQL

Even though traffic between an Azure VM and Azure SQL on a public IP will stay within the Microsoft network backbone, that public IP is not guaranteed to be static. Customers are therefore encouraged to access Azure SQL Server via its FQDN only, not via the underlying IP address.

Since Locations in AAD require IPs and not FQDNs, we cannot specify our Azure SQL Server instance as a Trusted Location in AAD. We will have to use the Private Endpoint capabilities of Azure SQL Server to tie it down to a Virtual Network (VNet).

I created one VNet (10.0.0.0/16) with two subnets:

1. SQLServer (10.0.0.0/24)
2. VMs (10.0.1.0/24)

I then configured a Private Endpoint to be present in the SQLServer subnet. Lastly, I set “Deny public network access” to “Yes.”

 

 

One thing to note: DNS with Private Link can be tricky. I did it the easy way: by letting Private Link automatically create an Azure Private DNS Zone that was associated with the VNet. That way the SSMS VM could find the SQL Server on its private endpoint via its FQDN: “sql-server-gary.database.windows.net.” Do not use its internal FQDN of “sql-server-gary.privatelink.database.windows.net.”

For more information on DNS and Azure Private Link see this very helpful set of documents.

Locking Down SSMS VM

This was easy. Just disassociate the public IP from the IP configuration of the VM’s network interface. To access, I just created a jump box in the same subnet (VMs). Our SSMS VM now has access only to the internal VNet.

In addition, I added a “deny internet” outbound rule on the SMSS VM’s Network Security Group (NSG) near the top of the priority list. Before it I added a rule to allow Azure Active Directory outbound flow.

 

 

Please note that the rules in this NSG are only used as examples for this specific use case. Your application will undoubtedly have additional requirements that you should consider. The goal should be to deny all traffic except that which is mandatory, and then only between the necessary components.

Locking Down AAD

In addition to controlling access via user sign on with multi-factor authentication (MFA), the customer wanted to make sure that no rogue agent was able to access from a public IP. Therefore, they created a CAP with the following configuration:

1. All users included.

2. All locations included.

3. Trusted locations excluded.

4. Azure SQL Database included.

5. Block access.

I added the VNet’s CIDR (10.0.0.0/16) as a Trusted Location in AAD. Of course, I could have chosen “All Cloud Services” instead of just “Azure SQL Database” to provide protection to even more resources.

Allowing Access from VNet to AAD

Lastly, it’s necessary to allow requests for authentication tokens to flow from our VNet to AAD.

Azure uses an internal IP for this. Unfortunately, this IP is not static, and can change at any time.

To make the IP static we will take advantage of the NAT Gateway service. This will fix the range of IPs used for outbound traffic. I associated the NAT Gateway with both of my subnets: SQLServer and VMs.

The “IP prefix” CIDR range should be added as a Trusted Location in AAD. This will allow the token request from our VNet to AAD to flow.

 

 

Conclusion

Here is a diagram to tie it all together. In this case I don’t have a jump box in Azure, but rather access is made from an on-premises computer via a private ExpressRoute connection.

This approach is attractive for several reasons:

• It adds another layer of protection to your cloud security. Redundancy is often desirable.
• Controlling access at the Azure Active Directory layer, where authentication tokens are provided, provides a single place to add new trusted locations, as opposed to doing so for each individual service.

 

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.