Public Endpoint for Azure Managed Instance and SSMS connectivity

VPN throughput or intermittent connection disruption could be an issue while connecting to an Azure Managed Instance. To avoid VPN connection-related disruptions to an Azure Managed Instance,  Microsoft has introduced Public Endpoint Link to connect an Azure managed instance directly from an on-premises SSMS. Public Endpoint is also helpful while managing both on-premises SQL Servers as well as Cloud based Azure SQL offerings through SSMS.

Steps to follow: (using PowerShell or using Azure Portal)

• Enable Public Endpoint for the managed instance.
• Configure the managed instance Network Security Group (NSG).
• Obtain the managed instance Public Endpoint host name.

Managed Instance Note:

• The value for the priority of the “Inbound Traffic Rule” must be higher than the “deny_all_inbound” rule.
• The default port of the managed instance is 3342, it is fixed and can’t be changed.
• To connect from an on-premises SSMS, use the following host naming method:

<mi_name>.public.<dns_zone>.database.windows.net,3342    

In our example, the managed instance host name is the following:

shb-mi-01.public.aaf67be1d0fe.database.windows.net,3342

Using PowerShell:

# Connect to the Azure Cloud
Connect-AzAccount

# Initiate Resource groups where managed instance belongs to
$rgname ='shb-db-rg-01'
# Managed instance name
$miname = 'shb-mi-01' 

# Enable the public endpoint
$misql = Get-AzSqlInstance -ResourceGroupName $rgname -Name $miname
$misql = $misql | Set-AzSqlInstance -PublicDataEndpointEnabled $true -force

# Modify NSG (Network security Group) by adding Inbound security rule
Get-AzNetworkSecurityGroup -Name nsg-shb-mi-01 -ResourceGroupName shb-db-rg-01 | `
    Add-AzNetworkSecurityRuleConfig -Name public_endpoint_inbound_sql -Description "Allow SQL TCP" -Access Allow `
    -Protocol Tcp -Direction Inbound -Priority 1210 -SourceAddressPrefix * -SourcePortRange * `
    -DestinationAddressPrefix * -DestinationPortRange 3342 | Set-AzNetworkSecurityGroup

 Inbound Traffic:

Connection to an Managed Instance:

References:

• Configure public endpoint in Azure SQL Managed Instance: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/public-endpoint-configure
• T-SQL differences between SQL Server & Azure SQL Managed Instance: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server