SSH Tunnels, SOCKS proxies, and Sticking It to IT

Today I achieved perhaps my greatest triumph in my eternal struggle against the shackles my IT department places on my laptop. Aside from the inability to install programs needing admin privileges, the greatest strife I have with IT is the restrictions in places on certain websites. To name the most basic example, I cannot access Facebook. Not that I want to, I just want the ability to. I also can’t access various hacking blogs, anything having to do with brewing beer – anything to do with alcohol at all, for that matter – even some personal blogs are restricted as they are not necessary for my job function. Nuts to that!

This post is the result of many steps that didn’t really have the ultimate goal of sticking it to IT, so it may be a little tough to get through. There may be other ways of achieving the same things, for instance. This is just how I did it, and I’m posting it here in case I need to do it again – for instance, if IT replaces this laptop, or if I have to recreate my server at home.

Software that will be mentioned in this post:
CopSSH (
MyEnTunnel (
Google Chrome (

Get SSH on to that Windows server

I used Linux on my home server for a long time. Then I got an AMD-based GPU and realized that Linux drivers currently suck and things would just be a helluvalot easier for me to install Windows 7. So I did. Then I went to connect to the terminal via SSH and realized there is no SSH server on Windows as there is on every default installation of Linux out there right now. I used CopSSH to recreate the experience of logging into a terminal remotely for server maintenance.

I can’t remember if there were any problems with this install. As I remember, everything went very smoothly. A couple of caveats:

1) Forward port 22 on your router in order to access your SSH server from outside your network. Port forwarding is beyond the scope of this post.

2) CopSSH starts an SSH service that allows you to connect to a Cygwin interface. In order to access your standard windows drives from the terminal once you log in, navigate to /cygdrive. There you will see folders named after your drive letters (c, d, e, etc.), and you can access your files there.

The IT department at my office blocks a lot of ports, but not 22 for some reason. I suppose it might be a necessary port for them to use for pushing program installs or something. For whatever the reason, with CopSSH installed at home, I can navigate my hard drive from work and stop/start services, create/edit/remove files, copy files to/from (with WinSCP), and, most importantly for this post, perform some SSH tunneling.

Tunnels and the Men Who Love Them

SSH tunneling is something I discovered (and fell in love with) only recently. Therefore, it is a bit tough to describe since I know so little about it. Essentially, you attach a rider to your SSH connection that says for x port on the remote server, treat it as y port locally. For instance, let’s say (since I do) that you have SABnzbd+ installed and running at home, and that the web frontend is running on port 8080. With an SSH tunnel, I tell my work computer to bridge port 9080 to port 8080 on my home computer. Once that bridge is made (bridges, tunnels, what’s the difference), I can open a web browser to localhost:9080 and receive the content that I would normally see at home using localhost:8080. This is particularly important to me, because the only external port I am able to reach at work through a web browser is port 80. Since tunneling treats it as a local port, I am free to access my SAB queue from work.

So how do you do it? Well, first you need to install PuTTY ( I’m sure there are other programs out there that do this, but just use PuTTY. It’s free, awesome, and ALWAYS works. You just need to download the Putty.exe file and place it in your path somewhere. While you’re there, download Plink.exe as well, since we’ll be using that later.

Here are the important pieces to configure when setting up a server in PuTTY:
1) host name is your external IP address at home (or your DNS name if you have one registered)
2) port is (almost) always 22
3) Tunneling is done under Connection->SSH->Tunnels
a) Source Port is what you want your local computer to use (9080 in the SAB example)
b) Destination is IP:RemotePort ( in the SAB example)
c) Click “Add” and this rule will be placed in the listbox under “Forwarded ports”
4) After you set up tunneling, go back to Session (at the top of the list box on the left) and save this connection as something memorable. “Tunnels” is usually a pretty good name.

Tunneling the Internet

So now you know how to tunnel a specific port from one computer to another. But what about using your home computer to do your web browsing and forwarding that content back to your work computer? For instance, what if I want to enter into my browser at work and have my home computer be the one looking up that web site? What if I want to do this for ALL internet content so that the IT department can never go snooping through logs of what I’ve been browsing on the web?

Simple. Go back into PuTTY and load up the Tunnels saved session you just created. Go back to Tunnels and add this:

Source Port: 9870
Destination: (blank)
Dynamic checked (not Local or Remote)

This will forward all dynamic traffic from the home to port 9870 at work. You can use a different port if you want, just make sure you remember what it is. Now, if you have access to your proxy settings or use Firefox (which ignores Windows proxy settings), you can go ahead and set those up as you normally would. You’re using a SOCKS connection, and the address is But if you don’t have access to your proxy settings (like me), and you like to use Chrome (like I do) instead of FireFox, you have to do a little extra work.

First, install Chrome. Most of you already have, I’m sure. Find your Chrome installation folder (might have to do a little googling to discover where Chrome installs). Create a new shortcut for that executable and add the following parameters to the end of it:


For me, the whole shortcut looks like this (in Windows XP)

“C:\Documents and Settings\(USER NAME)\Local Settings\Application Data\Google\Chrome\Application\chrome.exe” –proxy-server=”socks5://″

Add that to your desktop, start menu, whatever, and viola, you have proxy-fied internet every time you open Chrome. Of course, you will first have to open a session with PuTTY in order to open that tunnel to port 9870. That means that you will have a PuTTY window open all day, and your boss might start to get suspicious. What then?

Why I Love Hackers

Since PuTTY and all of its ancillary software is free and open, hackers are able to create really cool things like MyEnTunnel (, which stands for My Encrypted Tunnel. This program runs as a system tray icon that opens a saved PuTTY session and monitors it to make sure it stays connected. When the connection drops, it will retry as long as you tell it to. Your boss will be none the wiser.

1) Install and start the program.
2) SSH Server: Tunnels (or whatever you called your PuTTY session, above)
3) SSH Port: 22 (almost always)
4) username and password are obvious
5) Reconnect on Failure is a good idea, and Infinite Retry Attempts is preferable as well
6) IMPORTANT – I found that the Plink.exe file that came with MyEnTunnel simply did not work. I overwrote it with a current download from the PuTTY site. This is why I suggested you download it above.

Then connect, and you are in the mighty perpetual hands of an SSH tunnel. As long as the little icon in the system tray glows green, you will be able to open your Chrome shortcut and browse freely.

Final Thoughts

Yes, this does lead to a slower internet connection. You are browsing through a remote computer – what did you expect? However, I have thus far found this to be worth the trouble. You can add a –proxy-bypass-list=”aaa;bbb;ccc” parameter to your chrome shortcut if you visit sites like Pandora and need high bandwith available.

Also, I realized that with all internet traffic tunneled, I no longer needed the individual ports tunneled. For instance, rather than opening localhost:9080 for my sab queue, I can just open (i.e. the internal IP of the server at home). This is because, effectively, I am browsing from my home network.

I wrote this post somewhat haphazardly, just trying to set down my thoughts before they vanish and I have to figure this out all over again. If you would like clarification, leave me an email, and I will update the post or answer your question(s).


HAVING Keyword in SQL (or How to Calculate Loyalty with One Sentence)

This was a lot easier than I thought it would be.  For some back story – but without revealing too much about my industry – in our company, we have individuals who provide us with a great deal of business.  However, they provide our competitors with a great deal of business as well.  They are not contracted to give us all of their business, and there’s really nothing we can do aside from creating solid business relationships in order to assure that they do give us at least most of their business.

So in order to easily determine which individuals are giving us most of their business (and we define that as over 70% of their business), I wrote something very similar to the following simple SQL statement (for MySQL):

1. SELECT individual_id, count(1) AS total_volume,
2.  sum(case business when 'Us' then 1 else 0 end) as our_volume
3. FROM main_data_table
4. GROUP BY individual_id
5. HAVING our_volume / total_volume >= 0.7;

Note: the line items are not part of the SQL code – they were added to make explaining it easier.

I don’t normally do a lot with the HAVING keyword, but I suppose I ought to.  It comes in really handy in this example.  Here’s the breakdown of what’s going on:

1 : This is a stupid way of doing it (not the actual way I chose) but it essentially gives you a total number of line items for the individual_id.

2 : Two things are going on here.  First, we tell MySQL that when the line item represents our business (i.e. the center where the transaction happened was one of our centers), count it as 1.  Otherwise, count it as 0.  That way, when they are aggregated (using the sum() function), the result is the total number of line items for the individual_id that were completed at one of our centers.

5: This limits the results to those individual_id’s that produced at least 70% of their business at one of our centers.  You must use the HAVING keyword when you want to limit the results to a calculated aggregate qualifier.

The Greatest and Best Fish in the World: A Tribute

Having not written anything guj for what seems like ages, something happened over the Labor Day weekend that was incredibly non-guj, and for which I will write a tribute post.  My beta fish, Brutus, crossed over.

Brutus was never a very intellectual fish.  When poised with the problem of how to fit his mouth around the morsels of food I gave him, he failed at an alarming rate.  As he would lunge towards the surface, fully expecting to direct his mouth around the pellet, he would, more often than not, completely miss his target.  This caused the then tumultuous water to reverse the buoyancy of the pellet and drive it down to the murky rocky depths.  Brutus never even seemed to notice.  He would sit at the surface, waiting for another pellet from me, his dutiful master.

When he stopped eating as much, I never thought too much of it.  “He’s getting old,” thought I, “and has had enough of foolishness.  He must have learned that if he grazes the bottom of the tank, he will be able to suck the nutrients out of yesterday’s forgotten foodstuff, and will continue to live forever based on this new plan.”  Ah, but there was a nagging voice deep in my subconscious reminding me that he is indeed getting old, and perhaps this loss of appetite could mean other, more serious things.

I am not a veterinarian, nor do I think a veterinarian can do anything to cure an aging beta fish.  I do not harbor any regret for not having done more to sustain his little life.  All I could do was sit at my desk and watch him slowly decay, his breathing becoming more and more labored, his attitude more and more lethargic.  But I do curse this world for creating something so fragile, and forcing me to love it.

Brutus was a gift from my wife.  She wanted me to have a companion at my cubicle.  His presence seemed to brighten any day.  He was so eager to swim up and look at me when I arrived in the morning.  Yes, this was really just anticipation of the food pellets soon to rain into his tank, but it was pleasant nonetheless.  As days turned into months and finally years, his presence on my desk became a fixture, and I couldn’t imagine working if it weren’t beside him.

There were a few scares.  He stopped eating for about a week a year ago.  I was sure he was a goner.  Many of the symptoms I witnessed over the past few weeks were witnessed then as well.  But somehow, miraculously, he began to eat again, and I nursed him back to health.

When I changed jobs to a different building down the street, I entrusted Brutus to a coworker at the first job, while I moved and got settled in the second job.  When I finally retrieved Brutus from this woman, his tank smelled like a forgotten pond in a redneck’s back yard, and he seemed absolutely miserable.  I cleaned his tank, gave him some food, sat and talked with him, and he was happy.

Now he lies at the bottom of the tank – resting, peacefully, finally.  He is not struggling to breathe, and he is not forcing himself to rise to the surface in a futile attempt at eating.  I don’t have the heart today to flush him.  Maybe tomorrow.  Hopefully soon, because this is kind of depressing looking at a dead body all day.

I want him to stay peaceful, and I don’t want to think of his little fish body being blasted through miles of plumbing to end up in a water treatment plant.  He’s better than that.

Goodbye Brutus, old man.  It was a pleasure getting to know you.