Using Linux iptables port forwarding and Point-to-Site VPN to securely connect to SQL Azure without a need to open SQL Azure firewall
I recently started using SQL Azure for one of my projects. It is very inexpensive, has way more storage than I need for my project. I use SQL Server Management studio to connect to the server, use SSL encryption to make my communication secure. However, I don’t work from the same place all the time. And every time I need to open SQL Azure firewall for the current IP of my internet connection and remove (for security reasons) these IPs as soon as I am finished using this network. As you can see - too many things to remember.
I also use a Point-to-Site VPN to connect to the rest of my resources that are sitting in the Azure Virtual Network that I created for more serious projects. Resources from within this network can access SQL Azure instances without any issue. The idea came naturally. Instead of modifying SQL Azure firewall settings, why can’t I simply use a proxy server inside of the Azure Virtual Network. All requests to this proxy on port 1433 would be forwarded to the real SQL Azure server and returned back to me.
I did a little bit of research, and suggested that a simple port forwarding using Linux iptables should do the trick.
I installed an Ubuntu 14.04.LTS VM inside of my Azure Virtual Network, assigned a static IP address to it:
Get-AzureVM -Name <vm name> -ServiceName <cloud service name> | Set-AzureStaticVNetIP -IPAddress <linux proxy ip> | Update-AzureVM
Enabled port forwarding.
Check if it is enabled
# sysctl net.ipv4.ip_forward net.ipv4.ip_forward = 0
Of course it was not enabled by default. In order to permanently enable forwarding, we need to change the value of net.ipv4.ip_forward to 1 in the file /etc/sysctl.conf. The best way is to directly edit the file. Then we will need to call sysctl -p to reread values from the conf file.
Set iptables rules.
First we need to change the destination for all packets that come on eth0 port 1433 to the IP address of our SQL Azure server:
iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 1433 -j DNAT --to <SQL Azure IP>
Next we need to allow these packets to be forwarded to SQL Azure IP if they’re new, established or related:
iptables -A FORWARD -i eth0 -d <SQL Azure IP> -p tcp --dport 1433 -m state --state NEW,ESTABLISHED,RELATED -j ACCEPT
We can also add all related and established packets to go through too:
iptables -A FORWARD -i eth0 -m state --state ESTABLISHED,RELATED -j ACCEPT
Now before the packet is sent to SQL Azure we need to change its source to this proxy, otherwise SQL Azure will try to reply directly to the caller:
iptables -t nat -A POSTROUTING -o eth0 -j SNAT --to-source <linux proxy ip>
The last thing is, because you can’t use just an IP address when you connect to the SQL Azure, we need to edit our hosts file and add the following line:
<linux proxy ip> <SQL Azure server name>.database.windows.net
That’s it. Now instead of opening SQL Azure firewall we can connect to the SQL Azure using our VPN connection.