Securing Azure SQL (Part 3): Azure SQL Database Service Endpoint

solarwinds_worldwide_llc over 2 years ago 8 minute read time

In previous installments of my “Securing Azure SQL Database” series, I covered Azure SQL Database firewall rules and private endpoints—the first of which is a way to help reduce the public exposure of your database endpoint and the second being a means to remove all public access if necessary. Each option has unique benefits, and some scenarios might call for a mix of the two options.

In this blog post, I’ll cover a third option for securing Azure SQL Database—service endpoints. This option is similar to private endpoints in that you restrict public access and only grant access to the database through your Virtual Network (VNet).

By the time you reach the end of this blog post, you’ll have the knowledge you need to make good decisions when securing Azure SQL Database.

What Are Service Endpoints?

Service endpoints are a networking feature in Azure designed to help you better identify traffic coming into your Azure SQL Database as originating from one or more of your VNets. To lock down your Azure SQL Database prior to the features of private or service endpoints, your only choice was the Azure SQL Database firewall. You would need to get a public IP address determined for your Azure services and then allow any valid IP through via the Azure SQL Database firewall rules.

One of the downsides of this older method is that the public endpoint is exposed, albeit behind the Azure SQL Database firewall, and traffic to/from your applications hosted in Azure could potentially end up going across public routes as it travels to the public endpoint. For example, if a VM inside one of your subnets needed to access the database, the traffic would leave the subnet and then come into the public endpoint, as shown in the diagram below.

Securing Azure SQL Database Part 3 Service Endpoints_Image 1

VM in VNet accessing Azure SQL Database via public routes and protected by only the Azure SQL Database firewall

By adding a service endpoint to a subnet on your Virtual Network, you’re effectively setting up a connection between the subnet and a type of resource in Azure—in our case, it’s an Azure SQL Database resource.

Many Azure services support service endpoints. On the Azure SQL Database side, you set up virtual network rules, which then define which subnets you’re allowing into that specific Azure SQL Database logical server. By setting up the combination of the service endpoint on the subnet and the virtual network rules rule on the Azure SQL Database logical server, you’ve created the pathway for access. Check out the walkthrough of how to set up a virtual network rule in the Microsoft documentation.

Once this combination is set up, consumers on the Virtual Network, such as a virtual machine (VM), would be able to access the Azure SQL Database. As you can see in the diagram below, the traffic is still passing out of the subnet, across the service endpoint, and hitting the public endpoint of the Azure SQL Database.

Securing Azure SQL Database Part 3 Service Endpoints_Image 2

VM in VNet accessing Azure SQL Database via a service endpoint—it still passes through a public endpoint, but on the Azure backbone routes only

Although the traffic is still hitting the public endpoint, as far as the Azure SQL Database is concerned, the VM will be connected to it using the VM’s private IP address. This means you no longer need the public IP address attached to a VM or other service like you used to. You can then take the extra step of stripping out any Azure SQL Database firewall rules, and access would be locked down only to the Virtual Network.

How Do Service Endpoints Work?

When a service endpoint is created, you must choose the type of service you’re attaching to the subnet. In this case, it will be a Microsoft.Sql service. Behind the scenes this will create some routes on the subnet that will direct anything attached to the subnet that attempts to reach any Azure SQL Server resource (in any region) to take a different path rather than just traveling out to the internet first. You can see this if you look up the Network Interface Card (NIC) resource on a VM attached to the subnet and check the Effective Routes tool, which is under the Support + Troubleshooting tab on the NIC.

The routing of this traffic means you gain some efficiency, as it’s routed over the Azure backbone and not across the public internet. This should reduce latency and make the traffic a little more secure because it won’t go across public channels.

However, it’s very important to remember that with service endpoints, although the traffic is routed over the Azure backbone only, it’s still being delivered to the public endpoint of the Azure SQL Database. Therefore, the public endpoint is still exposed and could be vulnerable.

Although the service endpoint helps direct the traffic from the subnet over to the right service, you still need to allow the traffic into the Azure SQL Database. This is where the Virtual Network firewalls come into play just like the Azure SQL Database firewall rules I discussed in the first blog post in this series.

Instead of providing public IP addresses or ranges like you do for the Azure SQL Database firewall rules, you can scope the access down to one or more subnets. This has the side effect of not having to assign a public IP address to the consumers of the Azure SQL Database on the subnet or hide them behind a gateway with a public IP. Instead, the definition of the Virtual Network firewall rules will recognize traffic that comes across the service endpoint as coming from the subnet and then allow it in. Also, through this bit of magic, the Azure SQL Database will see the private IP address of the resource accessing it. You can confirm this by checking the client_net_address value on connections in the sys.dm_exec_connections dynamic management view by using the following query run in a connection from within the subnet:

select client_net_address from sys.dm_exec_connections where session_id = @@SPID

Comparing Service and Private Endpoints

Service endpoints have many similarities with private endpoints at first glance, but one of the biggest differences is service endpoint traffic still connects to the public endpoint of the Azure SQL Database. Therefore, you can’t turn off all public network access to your Azure SQL Database and still use service endpoints. With private endpoints, the traffic is routed to a private endpoint on the back end, so the public endpoint can be completely turned off.

Both endpoint types support turning off the “Allow Azure service and resources to access this server” option on your Azure SQL Database logical server. With either Azure SQL Database firewall rules or Virtual Network firewall rules in place, you can effectively block out all other traffic. You would need to make sure any resource you have running in Azure that needs access to the databases would be able to do so via either type of endpoint.

At this time, service endpoints are supported on more Azure services than private endpoints, but the latter is catching up. In the Microsoft documentation, you can view both a list of supported Azure services offering service endpoints and a list of services offering private endpoints.

One final, important note: service endpoints are FREE. You’ll be charged (an hourly fee) for private endpoints based on the lifetime of the private endpoint itself, as well as the inbound/outbound data processed over that endpoint. As of the time of writing this blog post, the charge is the same for both directions, but it’s metered separately. The direction is based on the perspective of the subnet, so writing to a database is considered outbound. You’re paying for an extra level of security to ensure your traffic never leaves the subnet.

Things to Keep in Mind

As always, there are benefits and considerations when choosing options or features. Here are a few things to keep in mind when using service endpoints with Azure SQL Database:

Service endpoints can’t be used for securing access from on-premises directly.
In Azure SQL Database, service endpoints must reside in the same region as the Azure SQL Database they’re accessing. This isn’t a restriction for other service types.
DNS entries for Azure services continue to resolve to the public IP address to the service.
Just like private endpoints, service endpoints work at an Azure SQL Database logical server level, not the individual database level.
If a private endpoint is set on the server and you deny public network access, service endpoints will fail (active connections won’t close, but any new ones will be blocked).

Wrap-Up

In this series, I covered three ways you can secure traffic to your Azure SQL Database and shared tips for managing your Azure SQL Database firewall rules. I hope you’ve enjoyed the series and this knowledge will help you make good choices on how best to secure Azure SQL Database.

Interested in learning more about SQL Sentry? Check out the interactive demo today.