Home
Microsoft

( Reviewed by Srdan Bozovic )

Today customers connect to SQL Database from on-premises by allowing outbound traffic to well-known IP addresses for Azure SQL Database gateway for your Azure region that are documented here

However, this traffic goes over the Internet which introduces with it some inherent security and reliability challenges. A common example is having to manage firewall rules for mobile clients whose SNAT addresses change frequently.

 

An alternative is to setup a private connection to Azure – via P2S VPN, S2S VPN or Express Route – and then use a TCP proxy server to forward traffic to public IP address for SQL Database.In this blog, we provide a proof-of-concept of how this can be achieved using P2S VPN and NIGNX server. 

 

We used P2S VPN as  easy way to get traffic to flow from on-premises to Azure. In real-world scenarios customers should rely on more robust peering solutions like S2S VPN or Express Route for their production workloads.

 

Here is the high-level architecture diagram of how this solution works in practice

image002.png

Step1  -  User connects from on-premises (over VPN) by specifying Private IP address for Azure VM & port 1433. Alternately, hostname can be used with custom DNS that then maps it to Private IP address.

Step 2 -  NGINX is running on Azure VM and listening for traffic on port 1433

Step 3 -  Traffic is forwarded by NGINX to the Sql Database Gateway (for the region hosting your SQL Database server) as part of normal login flow

 

Here is a step by step guide to implementing this architecture

1.       Start by adding Azure VM vnet&subnet to virtual network rule on SQL Database as described here

This will ensure that the subnet hosting Azure VM can communicate to Sql Database. The result should be a firewall rule that looks like this

      Annotation 2019-03-29 112413.png

2.       Next, assign a Private IP address to your via VM Blade à Networking à IP Configurations. For this demo I typed in 10.0.0.40

image005.png

3.       (Optional) Configure and install P2S VPN client per instructions given here and connect to it.

 

4.       Download Nginx and change only the body the nginx.conf file as follows without modifying headers

#user  nobody;

worker_processes  1;

#error_log  logs/error.log;

#error_log  logs/error.log  notice;

#error_log  logs/error.log  info;

#pid        logs/nginx.pid;

events {

    worker_connections  1024;

}

stream {

   upstream sqlvm {

         server rnsqldbsrv.database.windows.net:1433;

   }

   server {

         listen 1433;

         proxy_pass sqlvm;

   }

}

This tells NGINX to forward any traffic received on port 1433 to my SQL Database server rnsqldbsrv.database.windows.net:1433 which is located in West US datacenter ( as confirmed by a simple ping command shown below)

 

>ping rnsqldbsrv.database.windows.net
Pinging westus1-a.control.database.windows.net [104.42.238.205] with 32 bytes of data:

 

5.       Now connect via SSMS specifying the Private IP address of VM and port 1433( which Nginx is listening on)

image008.png

 

We used username@servername for the traffic to flow correctly between on-premises and SQL Database. Connection policy of SQL Database needs to be explicitly set to Proxy mode (as outlined here)  for this to work without specifying the servername.

Additionally, this method only works for SQL Authentication. Custom DNS is needed for this to work with  AAD Auth.

 

To recap, we showed you a proof-of-concept for how you can connect to Sql Database from on-premises by using a TCP proxy server to forward traffic.

 

 

 

 

 

 

3 Comments
Occasional Visitor

@Rohit Nayak Thank you for this great workaround!! SQL Authentication is working fine.

 

How to make this work with Azure AD Auth? you mentioned  "Custom DNS is needed for this to work with  AAD Auth" but not sure. Is it that we need to create DNS record for the Nginx Forwarded private IP like nginx-azuresql.contoso.com(10.0.0.40) in this case? if so then its giving an error when trying to connect via SSMS that "provider: SSL provider, error:0 - the target principal name is incorrect"

 

Please suggest.

Microsoft

Hi Guys,

 

Very nice article, but point to note though I have tested both on Linux and windows ... there is a little check box at SSMS that need to be tick in order for this to work. Otherwise, it keeps getting an error saying "target principal name is incorrect". To solve this issue, 

1) At SSMS log in prompt

2) type in log in parameter as shown in the article

3) click the options button

4) you must check trust server certificate

 

and it work like a charm

Occasional Visitor

@Rohit Nayak ; thanks for you post. have you tried your setup with a Site 2 Site?

What I found out is that for a S2S connection I had to add two routes. One for the VNet-Subnet to be able to access the proxy, and a 2nd for the actual subnet where the mssql connection is established to, e.g. 23.101.160.0/20. Without the 2nd route it would always ask me to white list my public IP.

 

Do you know if there is way, maybe a parameter, to send all traffic through the proxy?