I am in the process of revamping server setups for my office as a part of which I am establishing different Windows servers for Sql Server Developer 2008, Sql Server Express and Svn Server. Considering that I often work late nights from my home as well as when I am travelling, I wanted to be able to connect securely to any of these servers as and when required, even when I am outside my office’s local network.

I received advice to use Tunnlr for the purpose (from someone whose advice I listen to very carefully). And so I decided to evaluate Tunnlr. After a brief research I was almost about to go with Tunnlr’s services when I read this on their website:

Tunnlr only allows you to send inbound HTTP traffic to your machine.

Hmmm.. so it seems like Tunnlr would only support Http traffic and many of the traffic I would generate would be non-http protocol traffic. Clearly I had to look for other options. This web page helped me a lot in arriving at a conclusion that the ideal and most maintainable scenario would be to install a proxy SSH server in my office network which would route all incoming data securely to its actual destination.

The next step was to find a suitable SSH Server for Windows. This ServerFault thread helped me to choose BitVise’s WinSSHD SSH server for Windows (and I must say, I have been elated at the choice, WinSSHD is worth every penny it costs).

So I setup WinSSHD on one of the Windows Server 2008 machines, WinSSHD installation went smoothly. The configuration thereafter was also easy (with lots of intuitive help available for each configuration option in the admin GUI itself). You must definitely ensure WinSSHD is reasonably secured and all non-required features are turned off.

One good thing to do would be to configure WinSSHD to listen to connections on a non-standard port other than 22 (atleast you keep those prying eyes guessing for the port). Let’s say I chose to have WinSSHD listen to port 5000.

You would now want to test that WinSSHD has been setup correctly for which I used Putty and the connection was fine.

Next I opened up the 5000 port on my office’s network’s router (including in the firewall) and forwarded the port to the Win 2008 machine running my WinSSHD server. I tested SSH connectivity from a machine on another network and that too was fine. So far so good 🙂

The final step was to get Sql Express 2008 to be accessible over a SSH tunnel. And this step really took me sometime to configure properly. Here are the steps I eventually followed to get the tunneling working:

1. Enable remote connections for Sql Server. I was using Sql Server Express 2008 and did the following to enable remote connections:
   1. Enabled TCP/IP protocol in Sql Server Configuration Manager.
   2. Set the port for Sql Server TCP/IP incoming connection to 8840.
   3. Enabled Sql Server Browser and set its Start Mode to Automatic to have it start with Windows.
2. Ensured that WinSSHD has a Connect Rule to forward incoming requests on port 8840. In default WinSSHD configuration, this is enabled automatically as a single Connect Rule exists which permits all destinations. So if you haven’t changed that, you can skip this step.
3. Add Client-to-server Tunneling rules in Putty (or whichever SSH client you use).

The last step above (Step 3) is what took me most time to get it working fine. These two pages helped:
http://souptonuts.sourceforge.net/sshtips.htm
http://oldsite.precedence.co.uk/nc/putty.html 

In the end the following port forwarding rule in Putty helped me see through the Sql connection:

L2100    192.168.1.2:8840

Here L2100 means that you would connect to local 2100 port when connecting to Sql Server, 192.168.1.2 was my Win Server 2008’s internal IP on office’s network and 8840 was the port Sql Server was listening to (as mentioned above).

Viola, everything is set-up. To test, open Sql Server Management Studio, give “127.0.0.1,2100” as the server name, give Sql Server credentials and click Connect (Windows Authentication won’t work most probably as you would be connecting from a machine on another network, and notice I am not giving the instance name of SqlExpess while connecting). If everything is setup as it should have been (including configuration on router for port forwarding on 5000, firewall rules to allow outside connections on port 5000 and suitable WinSSHD configuration as discussed above), you should be through to your sql server databases.

I would be setting up tunneling tomorrow for SVN (and maybe other office applications). Stay tuned for more posts if I face issues there that I feel can be discussed.