Last week we announced the general availability (GA) of private endpoints for Azure SQL Managed Instance. Today, we bring you examples of private endpoints in practical scenarios, starting from the basics and building to the more complex ones to follow in the second installment of this mini-series.
In this post, we'll cover the following scenarios:
- Accessing SQL MI from another virtual network
- A better kind of public access
- Accessing SQL MI from your premises
- Making SQL MI available to managed Azure services
Note that we’ll only be discussing scenarios in which Azure SQL Managed Instance itself is made available elsewhere over a private endpoint. Going the other way round—Azure SQL Managed Instance accessing other resources over their private endpoints—is hopefully already an established practice and is documented elsewhere, for example in How to take secure, on-demand backups on SQL Managed Instance.
Okay, let’s dig in!
1. Access SQL MI from another virtual network
This is the bread-and-butter scenario of private endpoints, so it only makes sense to cover it first.
Suppose your Azure SQL Managed Instance is in one virtual network and your application is in another. How do you get the application and Azure SQL Managed Instance to talk to each other? You may have this kind of setup if:
- You want a stronger network separation between your data and our apps, or
- your apps connect to Azure via VPN or ExpressRoute from premises, or
- you have a multi-region setup with apps talking to SQL MI in a different region, or
- your apps reside in a different Azure subscription or tenant, or
- your apps are in a virtual network over which you have no direct control (if an Azure service manages this network, also check out scenario 4),
or any similar scenario.
In this scenario, we can make SQL MI available in other virtual network(s) by creating one or more private endpoints to SQL MI. Doing so means we don’t need to bother connecting the networks together—no peering or VPN!—so it’s altogether the simplest and most secure option, establishing one-way connectivity from our app’s virtual network to only the database server it needs.
That said, you should also know that by choosing private endpoints over peering or VPN you’ll be making a slight compromise in terms of performance. Private endpoints to Azure SQL Managed Instance today use the proxy connection type regardless of the instance setting. But if you’re not planning on firing thousands of connections per second through the private endpoint, you’ll appreciate the added security and simplicity of management.
2. A better kind of public access
One super useful property of a private endpoint is that it always has a static IP address, regardless of any changes in the managed instance, for example following a failover or service update. Even if you move the managed instance to a whole new subnet or virtual network, its private endpoints will retain their IP addresses!
Because we can rely on the private endpoint having a static IP address, we can configure Azure Firewall to route incoming traffic from a public endpoint to the private endpoint. This is not too dissimilar to Azure SQL Managed Instance’s public endpoint, but comes with additional security and filtering options, like application-level filtering, advanced threat protection, logging, and integrativity.
To use Azure Firewall in front of Azure SQL Managed Instance, deploy it in a virtual network together with the private endpoint to that Azure SQL Managed Instance (they each go in separate subnets). Give the Azure Firewall a public IP address to listen on and add a NAT rule translating incoming traffic from the public endpoint to the private endpoint. This NAT rule should look like this:
- Name: your choice
- Protocol: TCP
- Source type: IP address
- Source: * (that's anywhere on the Internet, or you can be more specific, as appropriate to your environment)
- Destination address: public IP address you’ve given to Azure Firewall
- Destination port: 1433 (this is the default SQL Server port, but you can also pick another)
- Translated address: IP address of the private endpoint to Azure SQL Managed Instance (view the private endpoint's details to get it)
- Translated port: 1433 (this must be 1433 for the private endpoint to recognize the incoming traffic)
Now you have a static public IP address fronting your Azure SQL Managed Instance. Just remember to set up a DNS record named <mi-name>.<dns-zone>.database.windows.net to resolve to that IP address and configure your apps to use the domain name to connect. Otherwise, your apps may be unable to establish secure connections or connect at all (for the time being, Azure SQL Managed Instance requires that incoming connections carry the name of the instance in the connection string).
Moreover, if you want outbound traffic from Azure SQL Managed Instance to originate from static IP addresses, you can associate a NAT gateway with the firewall to perform source NAT (SNAT). This is discussed in further detail in the documentation for firewall's public IP configuration.
3. Access SQL MI from your premises
In this twist to the first scenario, now our apps aren’t hosted on Azure. This means we cannot create a private endpoint in their immediate address space. What we can do though is create one virtual network that will host the private endpoint(s) to our Azure SQL Managed Instances (and possibly other Azure resources our apps need to access), then connect apps to this network instead.
There are many ways we can connect our apps to this virtual network, for example:
- Set up point-to-site VPN connections between on-premises machines hosting our apps and this virtual network, or
- set up site-to-site VPN connections between on-premises networks and this virtual network, or
- use ExpressRoute, or
- open a public endpoint for your applications to connect via Internet (if you choose this route, refer back to scenario 2)
You may wonder, why not just make the application connect into the virtual network where the Azure SQL Managed Instance is? Why create an intermediary virtual network at all? The reasoning behind this is that it is a better security practice not to mix security zones. Our application possibly connects from the Internet, so it is a good idea to have an interim virtual network with stricter security and authentication measures than the rest of our infrastructure. This network zone is commonly known as the “demilitarized zone” or DMZ. Then, we can create a private endpoint to Azure SQL Managed Instance inside the DMZ to expose the smallest surface area of our database server and to retain flexibility in revoking this connectivity path as need be.
Think of this as a much better public endpoint for SQL MI where you can make use of built-in threat intelligence, place additional rules, view metrics, set up alerts, and other powerful features of Azure Firewall.
4. Make SQL MI available to managed Azure services
So far, we’ve only discussed scenarios where we have direct control over the virtual network in which the private endpoint appears. However, some managed Azure services that don’t expose their infrastructure can be told to create private endpoints to our resources in their managed networks. Such services will typically expose an API and/or a user interface through which we can request that a private endpoint be created to our Azure SQL Managed Instance (or any other type of resource), then do the approval on our end. Such services are Azure Data Factory, Synapse, and Cognitive Search at the time of writing. This list isn’t final and will expand over time, so always refer to the latest documentation to be sure.
Closing thoughts
This concludes the first part of this post series on private endpoints in Azure SQL Managed Instance. To learn more, about private endpoints for Azure SQL Managed Instance, you can visit the following links:
- The original GA announcement post, Private endpoints GA for Azure SQL Managed Instance (microsoft.com)
- Part 2 of this series, Advanced scenarios with private endpoints
- Part 3 of this series, Non-scenarios with private endpoints
- A five-minute video on Private Link and private endpoints (there’s lots more on SQL MI’s YouTube channel)
- Official documentation at Azure Private Link and private endpoints
- Our SQLBits 2023 talk, Secure networking with Azure and Azure SQL Managed Instance
Stay tuned until next time, when we’ll publish more scenarios involving private endpoints!