Lesson Learned #496:Verifying Network Route to Azure SQL Database via VPN in Windows

[Guest Jose_Manuel_Jurado]

Today, I worked on a service request where our customer was using a VPN, but the network routes seemed to indicate it was not working and was using the ISP IP instead of the VPN IP.

 

 

 

I found that can be challenging to verify whether your connection is indeed passing through the VPN or directly via your ISP. In this article, I would like to share my lessons learned to determine which route your connection to Azure SQL Database is taking using PowerShell in Windows.

 

 

 

First, running in PowerShell ping servername.database.windows.net we are going to have the IP address of gateway. Remember, that Azure gateway might be more than 1 IPs.

 

 

 

 

 

ping servername.database.windows.net

 

 

 

 

 

The output will display the IP address of your Azure SQL Database (example).

 

 

 

 

 

Pinging servername.database.windows.net [1.1.1.0] with 32 bytes of data:

 

 

 

 

 

With the IP address in hand, I can now use the Find-NetRoute cmdlet to determine the route the system is using to connect to the Azure SQL Database.

 

 

 

Execute the following command, replacing 1.1.1.0 with the IP address obtained. This command will output details about the route being used, including the interface and the next hop.

 

 

 

The output will include several important fields:

 

 

 

• InterfaceIndex: The index of the network interface used for the connection.
  
• DestinationPrefix: The destination prefix for the route.
  
• NextHop: The next hop in the route (which could be the gateway of your VPN or your ISP).
  
• RouteMetric: The metric of the route.
  
• InterfaceAlias: The alias of the interface used.
  
• IPAddress: will be the IP address of ISP or VPN.
  

 

 

 

Example:

 

 

 

 

 

IPAddress         : 100.x.x.x
InterfaceIndex    : 91
InterfaceAlias    : MyVPN
AddressFamily     : IPv4
Type              : Unicast
PrefixLength      : 32
PrefixOrigin      : Other
SuffixOrigin      : Other
AddressState      : Preferred
ValidLifetime     :
PreferredLifetime :

 

Continue reading...