Two SQL Managed Instances in separate virtual networks, two VPNs needed - how to make 1 VPN sufficient for connecting to both SQL Managed Instances' local endpoints?

Aleksey Vitsko 20

Hello,

We have two SQL Managed Instances (MI-1 and MI-2) in two virtual networks (vNet-1 and vNet-2) in separate regions.

Global virtual network peering is configured between two virtual networks.

SQL Managed Instances are joined into Failover Group.

MI-1 is primary and MI-2 is secondary.

Failover Group's endpoints are FG and FG.secondary respectively.

Both virtual networks have each own VPN gateways (vNet-gateway-1 and vNet-gateway-2).

Both VPN gateways have point-to-site VPNs (VPN-1 and VPN-2) configured, with "Azure Active Directory" authentication.

Issue:

When we connect to VPN-1, we can connect to MI-1 and FG local (internal) endpoints in SSMS.

But we cannot connect to MI-2 and FG.secondary local (internal) endpoints while connected to VPN-1. Error is:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 -

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Framework Microsoft SqlClient Data Provider)

Vice versa, when we are connected to VPN-2 - we can connect to MI-2 and FG.secondary local (internal) endpoints in SSMS.

But we cannot connect to MI-1 and FG local (internal) endpoints while connected to VPN-2.

Question:

How can we make MI-2 and FG.secondary be accessible over VPN-1, and/or make MI-1 and FG be accessible over VPN-2 ?

The ultimate goal is to make just one VPN be sufficient for connections to both MI-1 and MI-2 (FG and FG.secondary) in different regions.

Silvia Wibowo 3,166 Reputation points Microsoft Employee

2024-05-10T04:57:14.5633333+00:00
Hi @Aleksey Vitsko , did you use domain name or IP address when connecting? If you use domain name, please try connecting to IP address. If IP address works, then it's a DNS issue. If IP address does not work, it could be NSG or routing issue.

Other things to check:

Virtual network peering – global: Virtual network peering connectivity across Azure regions doesn't work for instances of SQL Managed Instance that are placed in subnets that were created before September 9, 2020.

Virtual network peering – configuration: When establishing virtual network peering between virtual networks that contain subnets with SQL Managed Instances, such subnets must use different route tables and network security groups (NSG). Reusing the route table and NSG in two or more subnets participating in virtual network peering will cause connectivity issues in all subnets using those route tables or NSG, and cause SQL Managed Instance's management operations to fail.
Aleksey Vitsko 20 Reputation points

2024-05-10T14:48:36.34+00:00

Hi @Silvia Wibowo , local IP address does not work for me, on any VPN and any MI.

When I am connected to VPN-1, I can connect to local endpoint over FQDN mi-1.dnszone.database.windows.net in SSMS, but when I am trying to connect to its internal IP address (to which this FQDN resolves - 10.xx.x.9 (port 1433) or 10.xx.x.12 (port 11005) port ) - throws an error:

Cannot connect to 10.yy.y.9.

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.)

Tried above with both Microsoft Entra MFA and SQL Server auth types - same error.

Also, related to your comment, my subnets were created in 2024, and peered networks use separate NSGs and RTs for their subnets
Aleksey Vitsko 20 Reputation points

2024-05-10T15:00:43.0133333+00:00

Hi @Silvia Wibowo when I am connected to VPN-1, I can not connect to MI-2, regardless of using domain name or IP address of local endpoint (10.xx.x.9).

Interesting is that when I am connected to VPN-1, I can connect to MI-1 domain name of local endpoint (as I already mentioned in the Question), but I can't connect to MI-1 IP address (10.yy.y.9 over port 1433 or 10.yy.y.12 over port 11005) - it throws an error:

Cannot connect to 10.yy.y.9.

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The target principal name is incorrect.)

Regardless of using Microsoft Entra MFA or SQL Server auth.

Regarding to your comment (other things to check), MI subnets were created in 2024, and MI-1 and MI-2 are using separate NSGs and RTs.

I can post a screenshot of NSGs and RTs with their inbound/outbound rules, if needed, as well as VPN config.
Silvia Wibowo 3,166 Reputation points Microsoft Employee

2024-05-13T02:23:37.76+00:00
Hi @Aleksey Vitsko , what error did you get when you're connected to VPN-1 and try to connect to MI-2:

using domain name

using private IP address

There's a mechanism to check hostname in SQL MI, so it's expected to have the error message "A connection was successfully established with the server, but then an error occurred during the login process." -> it means that network is connected. We're trying to first isolate the problem, whether it's a routing issue or DNS issue.
Aleksey Vitsko 20 Reputation points

2024-05-13T21:26:52.11+00:00

@Silvia Wibowo when connected to VPN-1 and try connect to MI-2, it generates similar but different errors:

using domain name:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

(provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

using private IP address:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The network path was not found.
Silvia Wibowo 3,166 Reputation points Microsoft Employee

2024-05-14T02:51:05.1533333+00:00
It could be routing or NSG issue. Let's focus on the scenario: client is connected to VPN-1, trying to connect to MI-2.

What's the route table applied on VPN-1 GatewaySubnet?

What's the effective routes observed at MI-2 NIC (network interface card)?

What's the NSG applied to MI-2 subnet or NIC, if any?
Aleksey Vitsko 20 Reputation points

2024-05-14T14:18:49.59+00:00
@Silvia Wibowo

What's the route table applied on VPN-1 GatewaySubnet? - no route table applied to GatewaySubnet for VPN-1

What's the effective routes observed at MI-2 NIC (network interface card)? - below screen for route table of MI-2 subnet:

What's the NSG applied to MI-2 subnet or NIC, if any? - it is MI-2-NSG, screen below:
KapilAnanth-MSFT 36,396 Reputation points Microsoft Employee

2024-05-15T08:06:16.33+00:00
@Aleksey Vitsko ,

Welcome to the Microsoft Q&A Platform. Thank you for reaching out & I hope you are doing well.

Silvia Wibowo 's answer is correct.

You can either,

#1

You should use a single VPN Gateway and connect the other VNET to this VPN VNET and enable transit on the peerings.

OR #2

Connect the 2 VPN Gateways via VNet-to-VNet VPN connection and enable BGP.

This scenario is covered in Multiple VNets connected using an S2S VPN with BGP

See : Advertise custom routes for P2S VPN clients

Kindly let us know if this helps or you need further assistance on this issue.

Thanks,

Kapil

Please don’t forget to close the thread by clicking "Accept the answer" wherever the information provided helps you, as this can be beneficial to other community members.
Aleksey Vitsko 20 Reputation points

2024-05-16T13:33:14.7733333+00:00

@KapilAnanth-MSFT I've tried to connect two VPN gateways, and one of two connections says "Not Connected".

How can I make it "Connected" ?
Aleksey Vitsko 20 Reputation points

2024-05-16T13:35:26.1833333+00:00

@KapilAnanth-MSFT now it is Connected, it took some time (10-15 min) to actually connect.
KapilAnanth-MSFT 36,396 Reputation points Microsoft Employee

2024-05-16T15:04:42.3266667+00:00

@Aleksey Vitsko Wonderful.

Glad you are able to successfully set up the configuration.

Cheers,

Kapil

Accepted answer

Silvia Wibowo 3,166 Reputation points Microsoft Employee

2024-05-15T02:19:40.6333333+00:00
Hi @Aleksey Vitsko , from your screenshots of NIC routes and NSG:

You have NSG inbound entry on MI-2: source 172.16.x.x -> is this your on-premises?

There is no route for 172.16.x.x, so MI-2 does not know where to route the reply traffic.

What you can do, either:

Enable BGP if your VPN Gateway SKU is not Basic (Basic does not support BGP), or

Create an entry on the route table applied to MI-2 subnet: 172.16.x.x (your on-premises CIDR) next hop: Virtual Network Gateway.

If you go with #2, these are the pre-requisites:

you can't have a Virtual Network Gateway (VPN Gateway) locally at MI-2 vNet

do not peer MI-2 vNet with any other vNet that has a VPN Gateway, make sure that MI-2 vNet is only peered with one vNet that has a vNet gateway (VPN vNet)

enable transit on the peerings:

peering of VPN vNet to MI-2 vNet -> Allow both traffic settings (traffic to remote virtual network and traffic forwarded from remote virtual network), Use this virtual network's gateway

peering of MI-2 vNet to VPN vNet -> Allow both traffic settings (traffic to remote virtual network and traffic forwarded from remote virtual network), Use remote virtual network's gateway

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.
Please sign in to rate this answer.
Aleksey Vitsko 20 Reputation points

2024-05-15T16:46:32.17+00:00

Hello @Silvia Wibowo 172.16.x.x/24 is address pool of VPN-1 (point-to-site configuration), and for VPN-2 as well. They have same address pools. So this is not an on-premises, but Azure VPN address pool. Not sure if that helps. NSG inbound entry on MI-2 - this is me experimenting with rules, hoping to fix the issue, so this rule was created by me and did not help, and can be removed.

Also we have a vNet-3 (there are no SQL Managed Instances), which has a lot of App Services, which are integrated into vNet, so they can connect to MI's local endpoints. vNet-3 is peered with vNet-1 and vNet-2 (where VPN-1 and VPN-2 are configured. There is peering configured from vNet-3 to vNet-1 and vNet-2 so Apps can connect to MI FG. We are planning to leave peering from vNet-3 to vNet-1 and 2.

No VPN Gateway at vNet-3.

Let me know if this provided information changes your answer anyhow. Or if answer is still relevant and I can go ahead and try it.

Aleksey Vitsko 20 Reputation points

2024-05-15T17:18:06.4+00:00

@Silvia Wibowo Ok today I've tried simply enabling BGP on both VPN Gateways (Configuration at VPN-1 and VPN-2) (with different ASNs), but the issue still persists.

Aleksey Vitsko 20 Reputation points

2024-05-15T21:32:43.8833333+00:00

We will try your #2 in next few days, and let you know if it worked

KapilAnanth-MSFT 36,396 Reputation points Microsoft Employee

2024-05-16T09:43:39.1766667+00:00

@Aleksey Vitsko ,

I would suggest you consider BGP option only.

You should connect the two VPN Gateways as well, enabling the BGP alone will not be sufficient.

Cheers,

Kapil

Aleksey Vitsko 20 Reputation points

2024-05-16T13:32:12.9866667+00:00

@Silvia Wibowo Ok now I've tried to connect two VPN gateways, and one of two connections says "Not Connected".

How can I make it "Connected" ?

Aleksey Vitsko 20 Reputation points

2024-05-16T13:35:02.13+00:00

@Silvia Wibowo now it is Connected, it took some time (10-15 min) to actually connect.

Aleksey Vitsko 20 Reputation points

2024-05-16T14:06:30.8233333+00:00

@Silvia Wibowo after connecting two VPN gateways, it worked! I can connect to MI-2 while using VPN-1.

I did not even have to enable BGP.

Thank you very much!
Sign in to comment

Share via

Two SQL Managed Instances in separate virtual networks, two VPNs needed - how to make 1 VPN sufficient for connecting to both SQL Managed Instances' local endpoints?

0 additional answers