Azure SQL DB & Managed Instance - Connectivity &Network Security

This blog explains the significance and important points with respect to network security and connectivity to a Azure SQL database and Managed Instance, both of which are a DB PaaS offering from Microsoft Azure.

Azure SQL Managed Instance:

• Please follow this link to Connect to Azure SQL MI with Virtual Network peering option:
• https://techcommunity.microsoft.com/t5/azure-database-support-blog/connect-to-azure-sql-database-managed-instance-with-virtual/ba-p/369077
• Please follow below links to connect to MI from On-Prem:
• Point2Site - https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/point-to-site-p2s-configure
• Site2Site - https://learn.microsoft.com/en-us/azure/vpn-gateway/tutorial-site-to-site-portal
• ExpressRoute - https://learn.microsoft.com/en-us/azure/expressroute/expressroute-introduction
• https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/connect-application-instance
• For ON-Prem to Azure connection to MI, check if On-Prem firewall has an open outbound connection on SQL port 1433 as well as the 11000-11999 range of ports for redirection
• Besides SQL endpoint, it has also got a Management endpoint.
• MI is deployed in a virtual cluster and the services which are outside this cluster but within Azure uses Management endpoint to connect to MI.
• Management endpoint is a public endpoint protected by public firewalls
• When you use portal or CLI to manage MI, it uses management endpoint to connect to SQL
• https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/connectivity-architecture-overview#management-endpoint
• If you enable public endpoint for an Azure MI, it is exposed over port 3342 and not 1433.
• Additionally, you can use NSG to setup firewall on the port.
• https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/public-endpoint-configure
• Connection Type - Proxy vs. Redirect
• Even if a MI endpoint is in private VNet, Proxy connection is more secure because all traffic is routed through a Gateway.
• With Redirect, the initial connection is routed through a Gateway and all subsequent traffic goes to MI node directly.
• Redirect can hence be much faster
• When public endpoint is enabled, it always uses proxy.
• https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture#connection-policy

• Azure SQL DB:
• Using Public Endpoint
• Allow access to Azure services - VM, application or cloud shell to access public endpoint
• Create an Azure SQL DB
• Create a Azure VM
• RDP to VM and try connecting to Azure SQL DB using SSMS
• SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID
• The returned IP address would be public IP address of the VM - this shows that VM is connecting using its public IP even without configuring SQL firewall. The connection is possible because of the option - Allow access to Azure services
• Nslookup corpxenosql.database.windows.net
• 168.63.129.16 - a special virtual IP used for Azure communication
• https://docs.microsoft.com/en-us/azure/virtual-network/what-is-ip-address-168-63-129-16
• Result also includes DNS hierarchy of the logical server which includes DNS server within Gateways(control ring) of Azure
• Firewall rules
• For resources outside of Azure
• VNet service endpoint
• Allows resources in a specific Vnet to connect to the logical server
• This is still a public endpoint connection but strictly limited to resources from a specific Vnet
• https://docs.microsoft.com/en-us/azure/azure-sql/database/vnet-service-endpoint-rule-overview
• Private Link
• Recommended when you don't want to allow any public endpoint access to Azure SQL DB regardless whether connections come from within or outside of Azure
• Private link allows PaaS services to restrict access only through private endpoint
• https://docs.microsoft.com/en-us/azure/private-link/private-link-overview
• PL exposes a private endpoint in an existing Azure Vnet corresponding to PaaS service.
• DNS name for the logical server is no longer in a public DNS hierarchy
• In order to enable private link, first check below settings via Firewall and Network rule page of Azure SQL DB virtual server:
• Deny public network access = YES
• Allow Azure services = NO
• Note - PL must be created in same region as of Vnet, however resources attempting to connect to DB can be in other Vnets (communicating via peering) or ON-Prem (communicating via Vnet Gateways)
• https://docs.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview
• Execute below query from VM:

SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID

· You will find the private IP address of VM as client address

· Nslookup corpxenosql.database.windows.net

In order to connect from ON-prem:

https://docs.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview#connecting-from-an-on-premises-environment-over-vpn

· Note - PL uses proxy as connection type