Have you ever wanted to establish a secure encrypted connection between a local server and say a web server running SQL Server? I have and I wanted to do it without reconfiguring anything on my database server. Here’s how:
Remote Microsoft SQL Database Server
- SQL Management Studio
- Allows Remotes connections over a port (Default Port: 1433)
- Is on the same subnet/network as the SSH Server.
Local Windows Machine
- SQL Management Studio
- Cygwin Installed
- Is your laptop, local server, etc. This is just the machine with which you will connect to the SQL server over the internet using an encrypted SSH Tunnel.
- SSH Server Installed
- Allows Remote Connections
- Rinetd Installed
- Is on the same subnet/network as the SQL Server.
First of all, we have to lay down some ground rules. Our shop will run one dedicated Management server where all external access to the servers in that environment are accessed. The management server is the gatekeeper to everything else on that network including SSH access, port forwarding for remote desktop, etc. This allows us to only allow access through the switch or local machine firewalls to vulnerable ports such as 1433 (SQL) or 22 (SSH) or 3389 (Remote Desktop) to only the management server and not the world wide web. Our Management SSH server is running basically 2 packages. sshd and rinetd.
I’m not going to get into configuring sshd or rinetd other than providing my specific config for port forwarding for SQL Management studio. Once you have this working, it can apply to remote desktop sessions, samba sharing, website access. Basically anything you want to go over an ssh tunnel that you don’t want opened to the world wide web can go over an SSH tunnel using Rinetd to forward.
Configure your SSH Server with Rinetd
1. Install rinetd
sudo apt-get install rinetd
2. Edit the rinetd.conf file
sudo emacs /etc/rinetd.conf
3. Add the following. You will need to edit for your specific environment.
# SQL Server Test
0.0.0.0 3409 10.210.23.12 1433
4. Notice that the first line has the following.
0.0.0.0 3409 10.210.23.12 1433 # (0.0.0.0) (port) (Local IP of SQL Server) (Port to connect to on SQL Server)
The first parameter is where do you want the remote session to come from. 0.0.0.0 means allow all assuming they can make a success ssh session. The second parameter is the local port on the SSH Server that you want to connect to. The third parameter is the Local IP of the SQL Server that you want to forward to. The fourth parameter is the port on the SQL server you want to connect to. 1433 is the default SQL port so we will use that.
For example if the following were your IP addresses for your SSH and SQL servers.
SQL IP: 192.168.0.10
SSH IP: 192.168.0.5
Your config would look like this.
# SQL Server Test
0.0.0.0 3409 192.168.0.10 1433
The second parameter is arbitrary. It is just a port that isn’t used by any other service. Generally anything over 3000 is open for use by other items. In the example above I used 3409.
Setup AutoSSH in Cygwin
Now, we can move on to installing AutoSSH within cygwin on your local machine to create a ssh config that will reestablish an SSH connection if it drops. We will make an assumption that you’ve installed Cygwin before that you are familiar with installing packages, poking around in configs, etc. If you need to install cygwin, you can find it here.
1. Launch the setup.exe of Cygwin and walk through the installation until you get to the packages selection. Find AutoSSH and Open-SSH and select it to be installed and then finish installation.
2. Open a Cygwin prompt and enter the following command.
cygrunsrv -I AutoSSH -p /usr/bin/autossh.exe -a "-M 20000 -N -L 3409:your-ssh-server:3409 username@your-ssh-server" -e AUTOSSH_NTSERVICE=yes
The above command creates a Windows service that uses AutoSSH to create an SSH Tunnel to your SSH Server over the port you specified earlier.
3. Go to Start>Run. Then type services.msc and hit OK. This should bring up a window with all of your Windows Service. Find AutoSSH (The service you created with the command above) right click and select properties.
4. Make sure the settings look like the following 2 screenshots.
5. IMPORTANT! You will need to be sure to select “This Account” and select your username and enter your password rather than using Local System Account. Then select OK.
6. Now, you want to start the AutoSSH service by Right Clicking on it and selecting Start.
7. To test to make sure your tunnel is up and running. Open a Cygwin prompt and type the following command
If your AutoSSH service is working properly, you should see the following line.
TCP 127.0.0.1:3409 0.0.0.0:0 LISTENING
8. Finally, launch SQL Management Studio and try connecting to your remote SQL Server.
Note: You will need to be sure that you have a user with permissions to the database SQL server you are connecting. Be sure that the Servername is your localhost ip address of 127.0.0.1,(port) that you use earlier in the Windows Service creation command.
Then you should be connected to your SQL server over-the-internet and encrypted over an SSH tunnel.
This is a long one and I am happy to provide clarification where needed. Don’t hesitate to hit me on twitter @jdcarg or post a comment below. Thanks for reading!