Sql-server – Allowing access to MS SQL database through VPN

azurePROXYsql servervpnwindows-server-2012-r2

My setup involves a Microsoft Azure VM instance running Server 2012, connected to a Azure SQL Database V12 server on another instance.

I've set up an SSTP VPN on the server, and it works. Clients connecting to it do not use the server gateway, since its purpose is to host network shares and not to proxy traffic. The database server is set to refuse connections from anything but the VPN server.

I would like to have the database accessible through the VPN, but am having trouble routing the traffic from the VPN server to the database — specifically, I'd like to have the database accessible on 192.168.26.1. I've opened port 1433, and added a TCP port forward with:

netsh add v4tov4 listenaddress=192.168.26.1 listenport=1433 connectaddress=<database hostname> connectport=1433

When I attempt to connect to the database through 192.168.26.1 on a client machine, netstat on the VPN server shows:

> netstat -an | findstr 1433
  TCP    10.0.0.4:51056           <database ip>:1433      TIME_WAIT
  TCP    192.168.26.1:1433        0.0.0.0:0               LISTENING

Similarly, netstat on the client (a Windows 10 machine) shows a brief connection to 192.168.26.1:1433.

From this, I am led to believe the connection to the database is being made through the VPN, however when attempting to connect to the database through the 192.168.26.1 proxy, SSMS says:

Your client IP address does not have access to the server. Sign in to an Azure account and create a new firewall rule to enable access.

Following the instructions leads Azure to wanting to add my client IP to an allowed firewall rule. However, shouldn't the connection have been made through the VPN? netstat seems to say so, so why am I being asked to add my client IP?

For my SQL credentials, I use 192.168.26.1 as the server name, and <username>@<database hostname> as the login. This works from a Remote Desktop Connection on the VPN server.

What's going on?

Best Answer

Your client IP address does not have access to the server. Sign in to an Azure account and create a new firewall rule to enable access.

You need to allow the public IP address of your VPN server to access the Azure SQL database. Allowing the private IP address won't work.

According to the output of netstat, your VPN server has 2 NICs, you need to allow the Public address which connects to the Azure SQL database.

Related Topic