Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

ETL Method – Fastest Way To Get Data from DB2 to Microsoft SQL Server

For a while, I have been working on figuring out a “better” way to get data from DB2 to Microsoft SQL Server. There are many different options and approaches and environments, and this one is mine, your mileage may vary.

Usually, when pulling data from DB2 to any Windows box, the first thing you might think of is ODBC. You can either use the Microsoft DB2 driver (which works, if you are lucky enough to get it configured and working), or the IBM iSeries Client Access ODBC Driver (which works well), or another 3rd party ODBC driver. Using ODBC, you can access DB2 with a ton of different clients. Excel, WinSQL, any 3rd party SQL Tool, a MSSQL linked server, SSIS, etc. ODBC connects just fine, and will work for “querying” needs. Also, with the drivers you might install, you can usually set up an OLE DB connection if your client supports it (SSIS for example) and query the data using OLEDB – this works as well, but there are some caveats, which I will talk about.

In comes SSIS, the go to ETL tool for MSFT BI developers. You want to get data from DB2 to your SQL Server Data Warehouse, or whatever. You try with an OLEDB connection source, but it is clunky, weird, and sometimes doesn’t work at all (PrimeOutput Errors Anyone?). If you do manage to get OLEDB configured and working, you still probably will be missing out on some performance gains compared to the method I am going describe.

Back to SSIS, using ODBC. It works. You have to create an ADO.NET ODBC connection, and use a DataReader source instead of an OLEDB source. Everything works fine, except one thing. It is slow! Further proof?

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/162e55e5-b64b-423e-94c1-dd764ca1f683

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96977

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/cfade7e7-50d5-4447-9821-35c5d5ae1b66

http://www.sqlservercentral.com/Forums/Topic702042-148-1.aspx

http://www.sqlservercentral.com/Forums/Topic666993-148-1.aspx

Ok, enough links. But if you do read those. SQL 2000 DTS is faster than using SQL 2005/2008 SSIS. WTF? The best I can guess is that it is because of the .NET wrapper around ODBC. DTS is using “native” ODBC.

So, now what? Do we want to use DTS 2000? No. What to do though?

Well, after a few days of research, and just exploring around, I think I have found a good answer.. Replace DB2 with SQL Server.. just kidding. Here is what you need to do:

Install the IBM Client Access tools. There is a tool called “Data Transfer From iSeries Server” which the actual exe is "C:Program FilesIBMClient Accesscwbtf.exe"

image

This little tool allows you to set up data transfers from your DB2 system to multiple output choices (Display, Printer, Html, and Text). We want to export to Text file on our filesystem. You have to set up a few options, like the FileName, etc. In “Data Options” you can set up a where statement, aggregates, etc.

If you output to a file, you can go into “Details” and choose a file type, etc. I use ASCII Text, and then in the  “ascii file details” I uncheck all checkboxes. You set up your options and then hit the “Transfer data from iSeries” button and it will extract data to the file you chose in the filename field. Pretty sweet. But this is a GUI, how can I use this tool? I am not going to run this manually. Well, you are in luck.

If you hit the “Save” button, it will save a .dtf file for you. If you open this .dtf file in a text editor, you will see all options are defined in text, in a faux ini style. Awesome, we are getting somewhere.

Now, how do you run this from a cmd prompt? Well, we are in luck again. Dig around in C:Program FilesIBMClient Access and you will find a little exe called “rxferpcb”

image

What this tool allows you to do, is pass in a “request” (aka a DTF file), and a userid/password for your DB2 system, and it will execute the transfer for you. Sweet!

Now what do we do from here?

1) Create an SSIS package

2) Create an execute process task, call rxferpcb and pass in your arguements.

3) Create a BULK Insert task, and load up the file that the execute process task created. (note you have to create a .FMT file for fixed with import. I create a .NET app to load the FDF file (the transfer description) which will auto create a .FMT file for me, and a SQL Create statement as well – saving time and tedious work)

Now take 2 minutes and think how you could make everything generic/expression/variable driven, and you have yourself a sweet little SSIS package to extract any table from DB2 to text and bulk load it.

image

What is so great about the .DTF files is that you can modify them with a text editor, which means you can create/modify them programmatically. Think – setting where statements for incremental loads, etc.

image

 

You can see from the two screenshots above, that is all there is. Everything is expression/variable drive. Full Load, and Incremental Load. Using nothing but .dtf files, rxferpcb, a little .NET app I wrote to automatically create DTF’s for incremental (where statements), truncate, delete, and bulk insert. I can load up any table from DB2 to SQL by just setting 3 variables in a parent package.

After you wrap your head around everything I just went over, then stop to think about this. The whole DTF/Data Transfer/etc is all exposed in a COM API for “Data Transfer Automation Objects’”

http://www-912.ibm.com/s_dir/slkbase.NSF/643d2723f2907f0b8625661300765a2a/0c637d6b03f927ff86256a710076ab22?OpenDocument

With that information at your disposal, you could really do some cool things. Why not just create a SSIS Source Adapter that wraps that COM object and dumps the rows directly to the SSIS Buffer, and then does an OLEDB insert or Bulk Insert using the SQL Server Destination?

I have found in my tests that I can load over 100 million row tables – doing a full complete load, in about 6-7
hours. 30-40 million row tables in 4 hours. 2 to extract, 2 to BULK insert. Again, your mileage may vary depending on the width of your table, network speed, disk I/O, etc. To compare, with ODBC, just pulling and inserting 2 million records was taking over 2 hours, I didn’t wait around for it to finish. Pulling 2 Million records with my method described in this blog takes about 3-5 minutes (or less!)

I know I have skimmed over most of the nitty gritty details in this post, but I hope to convey from a high level that ODBC/OLE DB just aren’t as fast as the method here, I have spent a lot of time over the last few weeks comparing and contrasting performance and manageability. Now, if I could just get that DB2 server upgrade to SQL Server 2008. . . Happy ETL’ing!

Categories
Geeky/Programming

Ubuntu 8.04 Hardy Heron – Second Chance, Wireless Works and was Easy!

So, last night I decided to give Ubuntu 8.04 Hardy Heron another try. I blogged previously about setting it up and getting wireless to work, and what a nightmare it was

What I did this time was this. First off, I have computers everywhere, a MacBook, MacBook Pro, Dell Desktop and Dell Laptop. I have Vista on the desktop, and the Dell laptop I wasn’t doing much with, so I decided to load up Ubuntu on it. After installing, I connected it to a wired connection, downloaded the OS updates and let is sit for a while. After a few minutes it popped up with an alert telling my that there were updated drivers for the BroadCom wireless nic card. Wha?!? I open the alert, hit enable, and it downloads new drivers and fwcutter and lo and behold, wireless starts working with no hassles. I am on Ubuntu right now dinking around. So what is the first thing I do? Well make it look like a Mac of course.

So now I have Mac OS 10.5, Vista, and Ubuntu 8.04 all running on different machines. I would say right now though I do like the Mac the best. I use Vista for anything that really needs Windows. I will probably setup VMWare Fusion again on the MBP, but a smaller partition. I moved all my music there (80+ GB) and had to free up some space, my Vista partition was 60 GB.

Categories
Geeky/Programming Ramblings

Ubuntu 8.04 Hardy Heron – Linux is Cool, Linux Wireless is Not – 10 Step Program

Gah. I have a love hate relationship with Linux. It is pretty cool, can do pretty much everything. But.. But.. wireless support is just a joke. Same issues with Yellow Dog Linux on the PS3.

Wireless should JUST WORK.

I downloaded the 8.04 iso, and burnt it to cd. Installed it in windows, which is cool, a 10 GB partition. rebooted and the windows boot manager lets you choose , Vista or Ubuntu.

After getting set up, logged in, I tried to get on wireless. Doesn’t work. The thing is with Linux, is if you start configuring stuff here and there, it can get WAY out of hand, and then its just wacked. That happened, so I reboot to Vista, uninstall Linux, reinstall.

Now, lets search the forums, blogs and what not to get wireless to work. These are the steps I took to get it to work. My laptop is Inspiron E1705 with Broadcom wireless..

Fire up terminal..

1) sudo apt-get install build-essential

2) wget http://bu3sch.de/b43/fwcutter/b43-fwcutter-011.tar.bz2

3) tar xjf b43-fwcutter-011.tar.bz2

4) cd b43-fwcutter-011

5) make

6) cd..

7) wget http://downloads.openwrt.org/sources/broadcom-wl-4.80.53.0.tar.bz2

8) tar xjf broadcom-wl-4.80.53.0.tar.bz2

9) cd broadcom-wl-4.80.53.0/kmod

10) sudo ../../b43-fwcutter-011/b43-fwcutter -w “/lib/firmware� wl_apsta.o

Now, reboot a few times, and then maybe.. just maybe your wireless will connect and work. Once it latches on, it seems to be fine. I am on Ubuntu right now, writing this post.

Only 10 steps to get wifi working, all manual, and just a PITA. Granted it took me about an hour to patch together 18 different ways to get it to work..

Now I know why people use Mac and Windows. There is now way regular users are going to put up with that. Its like having to turn a crank to get your engine in your car to start. Just ain’t going to happen. Maybe in version 9 🙂

Categories
Geeky/Programming

HowTo: Hack Your PS3: Installing Yellow Dog Linux 6.0, and Actually Getting It To Work!

So, a couple of weeks ago, I bought a PS3, for the Blu-Ray disc capabilities. Since HD-DVD has died and I can get Blu-Ray discs through Netflix, and since the PS3 can be updated with new features, I figured I would give it a try. (The 100 dollar EB games credit I had made the decision easier as well)

After playing around with the PS3 (I bought one game disc ; Uncharted, Drakes Adventures, and one Online game; Pain) I figured it would be good to hack around and install Linux on it. With the PS3, Sony makes it really easy to install Linux.

linux_on_ps3

I decided to go with Yellow Dog Linux (YDL), version 6.0 (the latest). Other distros will work, but this is the main one they support – you can download it here – http://www.terrasoftsolutions.com/support/downloads/

After getting the 3.7 GB file, which took forever, I burnt the ISO to DVD using Active@ ISO-Burner (a sweet ISO burner BTW). Ok, all set to install. A few things to consider

1) You need a USB keyboard and mouse

2) You need a HDTV for this to work

In the PS3, go to settings, system settings, and then format utility. You can format a 10 GB partition for “Other OS”.  Restart the PS3

Then put in the Linux DVD, go back to settings, system settings, Install Other OS. YDL said /ps3/otheros/otheros.bld  … so, hit start. Then it will tell you how to get to the other OS.

Go then to “Default System” menu and choose Other OS, and then restart..

It should restart into Linux. It will be at a prompt, “kboot:” – hit enter, it should get into setup, you can follow the onscreen instructions from there. Everything is pretty much default and hitting yes to any prompts, a basic install. You set up a root password, etc. Reboot after it is done, it will go through some final setup steps (checking sound, etc), after that, you should be able to login.

Logging in, first thing I wanted to do was get networking set up. I am not using the wired connection, rather I am using wireless, with WPA. Here is where the fun starts, and why I hate Linux.

YDL doesn’t support WPA out of the box, isn’t that awesome? The wifi was there in network config, etc, and I could put in settings, but it would never connect. I do love the error it gives you as well… “Check Cable?” umm… well, I would but Linux, remember we are configuring a Wi-Fi Connection? No Cable?

Anyway’s.. debacle time.. found this after some searching – http://dachaac.blogspot.com/2007/08/guide-to-get-wpa-psk-working-on-ps3-ydl.html

This works… I just connected to a “linksys” for 2 seconds to download the RPM (or you can just put it on a flash drive as well), and then disconnected. Going through that tutorial though,there was one thing I was getting hung up on. The networking commands were not working for me. I was logged in a regular user, so I tried “su” to switch to root. Still no dice. I had to logout, and login as root, and run through that tutorial (unpacking the RPM with –force works with “su”).

Awesome, my networking is working and I am using WPA… yesssss! Now, just check a few things, email, reader, Facebook, cool. How about YouTube… wait… no flash. Check Adobe’s site.. no flash for PPC architecture.. WTF? So no flash at this point…which stinks..

Whew, all the Linux hacking is making me thirsty.. ok, lets boot back to the PS3 OS…wait.. how?

After doing some more digging, there should be a command “boot-game-os” you can run from the terminal and it should reboot. Wait, trying to run that command, it says “command not found”… another WTF…

After some more digging, it turns out the command resides in the “/sbin” directory, which users don’t have access to… (even root? man…)

So we need to make a sym-link. You can do this as a regular user, but you need to “su”, then run

cd /bin

ln -s /sbin/boot-game-os boot-game-os

Then, you can just go to the “start” menu in TDL, and there is an option then to “Boot Game OS” – works like a charm, reboot and you are back to the PS3

Categories
Geeky/Programming

Salon Transcripts (STX) – Using Remotely – Opening Ports, Port Forwarding

A little background: Salon Transcripts (STX) is a Salon Management program for Mac’s. It is used by salon’s all over the country, small and large shops. The site they have is basically for sales. Tech support is 18 dollars per call/email, etc. They don’t have much for documentation, much less connecting remotely. I was tasked with getting it working remotely.

My first step was get remote access to a machine inside the network. For this I did 4 things.

1) Install DynDNS client for Mac

DynDNS (Dynamic DNS) allows you to create a faux domain name for an IP behind a NAT, and the little client will update DynDNS service so it is always up to date. Basically make a dynamic IP static. I installed this on a computer at the salon that is always on.

2) Install Vine VNC Server for Mac

Vine is a VNC server, I installed this on the same computer as the DynDNS client on the internal network

3) Install Chicken of the VNC for Mac

On the client computer (laptop) I installed Chicken of the VNC (love the name). Read somewhere this was the best VNC client for Mac, so I went with it.

4) Port Forward Port 5900 on the AirPort Express to the Machine Internally

Using the app in the Utilities folder on the Mac internally, AirPort Utility, I forwarded port 5900 (vnc port) to the computer with Vine and DynDNS

So far so good. I could then connect to the server on the internal network, change any settings, open more ports, etc. I could just use STX on the server machine, but it was really slow over VNC. Initially I was going to just have them use VNC and then FolderShare or email PDF reports back to the laptop so they could print them remotely, but just to slow.

I decided to get STX on the laptop to connect remotely over the Internet to the server on the internal network. Since there is NO documentation on this, I had to do some digging. First, what ports does STX use, and what is the STX server IP internally? Getting the IP is trivial, there is a setting in STX->Preferences->System where you can set the IP, so I had that. Finding out the ports, I fired up Terminal on the server, and ran

netstat -na | grep ESTA

To see what connections were established

stx_netstat

As you can see there are 3 ports that were going the server (the server was 10.0.1.100) – 9090, 4000 and 20260. These are the 3 ports you need to open up on a router and use port forwarding to the server to connect STX remotely.

Once again, the ports to use Salon Transcripts (STX) remotely are:

Port 9090
Port 4000
Port 20260

Those 3 tidbits would have saved me some time 🙂

After port forwarding those to the server, I tried connecting the laptop client over the net to my DynDNS host and it worked!

One thing I noticed though is running reports is VERY SLOW over the Internet, almost locks up STX.

Categories
Geeky/Programming

Google Apps For Your Domain – Google Talk, Federation, SRV Records, Domain Transfers, DNS

Well, I want my Google Apps (GAFYD) account to be able to use Google Talk with other users (using Federation) besides Gmail and GAFYD (ex: Twitter, Meebo, etc). To do this, you need to add SRV records to your DNS (http://www.google.com/support/a/bin/answer.py?answer=34143) – no problem right?

Well, my current hosting (hostmysite) doesn’t allow SRV records, so after going at it with Tech Support the answer was to "switch to another DNS provider". Yikes. My current registar is active-domain, which doesn’t support SRV records either (from what I can tell). GoDaddy does, so I will transfer my domain there. Another no problem, right?

I initiate the transfer from active-domain to GoDaddy, but then read in the FAQ on active-domain I have to manually request to be transferred to get the auth code. Still waiting for that. Then I will have to make sure GoDaddy is set up as active-domain was so my stuff still works. Then, I can finally change GoDaddy to run all the DNS for the domain instead of just pointing to hostmysite’s nameservers.

I tell ya, nothing can be easy. 🙂

Categories
Geeky/Programming

How To: Connect to SQL Server, VS TFS, etc using Windows Authentication when computer is not on Active Directory Domain (XP and Vista!)

Whew, long title, amazing results!

Problem: You have a laptop or computer and you are working remotely for a company. You VPN in. Your computer is not on their Active Directory (AD) domain. You try to connect to SQL Server using SSMS or Analysis Services using Excel, but it doesn’t work because it is using your user, not a domain user. How do you get around this?

Answer: Well, this is what I have found (tested on XP only) – start->run: computernamec$ – then it prompts you to login. Use your AD username and password, so

domainusername and password, and check the box to save password.

Seems that XP will save that in your authentication list somewhere, and then you can use SSMS or Excel to connect to the SQL Server via Windows Authentication!

This trick also works for TFS Build Servers/Team Explorer (tested with VS2005 Team Explorer) ..

Now for the fun part – Vista. The tricks above don’t work on Vista, but you can still get it to work. Here is what you do…create some shortcuts…

C:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft OfficeOffice12excel.exe”

C:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft Visual Studio 8Common7IDEdevenv.exe”

C:WindowsSystem32runas.exe /netonly /user:domainusername “C:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDESqlWb.exe”

 

Replace “domainusername” with your info. So if your domain is mycompany then it would be mycompanysteve.novoselac for example.

What happens is that then when you run those apps from those shortcuts it will prompt you for your domain password, you put it in, and it runs the app in the context of your domain user. You can then change the icon for each of these pretty easy, just browse to the exe in the second part when clicking the change icon button on the shortcut properties (the shortcuts are actually links to runas.exe which is a generic icon)

In Vista, for instance, if you are testing SQL (SSMS), you might get this error:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

The shortcuts above will get you around it in the situation where your computer is not not on the domain or you are not logged in as a domain user..

These tricks above are especially good if you need to connect to SSAS (Analysis Services) since it is only Windows Authentication. And also, the IT department doesn’t really need to have consultant machines on the domain, or VM’s set up, etc, instead they can use these workarounds

Technorati tags: , , , , , , , , , , , , , , , , , ,
Categories
Geeky/Programming

Windows Vista and netsh – How to connect to adhoc networks if you IT dept locks you down…

Ok, so for some reason you can’t play with adhoc networks, maybe your IT dept locked you down, maybe something else happened, some security software or something jacked your settings. Well, you can use netsh to allow adhoc access again. Maybe you don’t even want to connect to ad-hoc, but you want to run one, same difference.. anyways, just run this cmd:

netsh wlan delete filter permission=denyall networktype=”adhoc”

and you should be good to go!

Technorati tags: , , , ,
Categories
Geeky/Programming

Unprotected Wi-Fi: Encrypt your traffic with an SSH SOCKS Proxy to Browse Securely

Unprotected Wi-Fi: The bastion of coffee shops, airports everywhere. Browsing on these hot spots is basically like having unprotected sex with the Internet. My new solution:

image

Just kidding. Anyways, if you do browse on an unprotected hotspot, it is very easy for anyone to see all your web traffic, your passwords, your email, basically everything you do. They can save this info, then go home and get into all your accounts, basically take over your life if you give away the right info. You don’t want that do you?

Now, when I decided to finally get secure, I did some research, looking on Google, I figured someone had already done this and documented it well. The best and most comprehensive thing I found was on Lifehacker with an article in their “Geek To Live” series. Now I went through these steps, and I had some issues getting things working. I followed their steps to the letter, but it still didn’t work. I am on Windows Vista, which in the comments of the article, looks like other people had issues as well. We will get to that later 🙂

The Lifehacker article has you use Cygwin for all the SSH stuff. Pretty much this is what doesn’t work on Vista, at least from my conclusions. Over the last week or so I have been working with network guru Chris Super (my loyal tester) to get this whole setup working, and he came to the same conclusion. So, what do you do when Cygwin doesn’t work and you are running Vista? Well there are some other tools you can use to get this all running smooth. And a side note, Cygwin – ugh, why don’t you have an uninstaller? So 1996….

Step 1: SSH Server

First you are going to have to set up a SSH server. I have a Vista box at home sitting under the TV, the perfect candidate. Instead of Cygwin and configuring stuff with a command prompt, you can install a cool looking GUI SSH Server, freeSSHd – this program really is cool. First, they are using components from WeOnlyDo software, which I have used before in some of the .NET networking tools I have written. Second, they make this really easy to set up and configure. You install it, add a user (NT auth or regular), set some options for tunneling and access and you are set. If you have issues with this step I can help you out, but the options are pretty self explanatory. One thing I found is that when you add a new user, you need to restart the service for the user to work. One other thing I did was run my SSH server on a different port than the default (22) as people just try to hack this port all day. Pick something way up in the list 22822 for example.

Step 2: Dynamic DNS

The second step, unless you are running in a datacenter, is to make it so you don’t have to connect to your IP address. Instead, we want a cool domain name. What I used for this is Dynamic DNS. Chris actually blogged about this a while ago, which reminded me of the service. They have come a long way since they first started, which is nice. What you do is sign up for their service, and then install their updater tool on the same computer or another computer on your internal network. How this tool works is it checks on an interval your remote IP and updates the Dynamic DNS service. Pretty cool. Now you can remember a human readable domain name instead of your IP address!

Step 2.5: Configure your Home Router

Now that you have your SSH Server running, and your domain name pointing at your cable modem, you want to configure your router. Most if not all routers have a way to forward ports to internal IP Addresses. What you want to do is allow the port you configured in step 1 (22822) to forward to the internal IP address of your SSH Server box. That way, when you do requests to your SSH server from outside your internal network, the traffic will go to the correct box. Save your settings and you are good to go.

Step 3: SSH Client

Here is another place where Lifehacker’s steps didn’t work for me, because of Vista again. Cygwin really doesn’t work worth a damn on Vista it seems. A really good SSH Client that works on Vista is Putty. There isn’t even an install, it is just an exe. Awesome. Basically what I did was create a batch file to run putty with the command line options I wanted. The major caveat to get this to work is you need to run putty as an admin. I have that already set up on my box so no issue, but you might need to run a cmd prompt as administrator to get this to work!

One line in the batch file:

putty -D 9999 -P <the port you configured from step 1>-l <login name you configured in step 1> -ssh <your domain name from step 2>

Replace the pieces in <> with your values. The 9999 in the command is the local port that your client applications will connect to, which then gets forwarded out to your SSH server through your domain name. We will get to that in Step 3.

Once you run putty, it should ask you to login with the password you created in step 1, and you are good to go. You need to have tunnel set up for your user in the SSH Server. You might have SFTP and Shell also set up, so you will see putty show you a command line. This is the command line on the actual server on your internal network! You should now be connected to your SSH server, but yet, you still aren’t secure, because no applications are set up to use the new proxy yet.

Step 3: Configure Client Applications

Now you can configure your applications on your laptop to use your new proxy. The major applications you need to configure are your Internet Browsers. Firefox and Internet Explorer.

In Firefox, go to Tools->Options: Advanced Tab, Network Tab, Settings Button. Check the radio button for “Manual Proxy Configuration”. in the SOCKS Host area put localhost (you might need to put 127.0.0.1) and then the port you configured in step 1.

In Internet Explorer (7.0), go to Tools->Options: Connections Tab, LAN Settings Button. Check the box to “Use a proxy server for your LAN…”, click the Advanced button, in the Socks area, put localhost and the the port you configured in step 1

Wow, tons of steps to just change a little setting! I have been playing with a way to automatically set these up based on your local IP Address but haven’t perfected it yet. Once I do, I will post up an easier way.

Other applications you might have on your machine are email, IM, etc. As far as email, you might want to use a web mail client at this point. Also, for IM, you can configure them all to use SOCKS, but when I am the coffee shop I use a web based IM like Meebo because since your Internet session in your browser is already secure because you configured your SOCKS settings in your browsers, your IM’s will also be secure. There are a few other applications that you might use, like Windows Live Writer, etc and they usually have a place to set up SOCKS settings. If an application doesn’t have a place to set up SOCKS, then you probably don’t want to use it.

If you do have a corporate VPN client, you can connect to that as it is secure, and then connect to corporate sites internally and email, etc. Usually corporate networks
have tunnel’s set up when you connect to VPN. All your “corporate” traffic will go down the secure tunnel, while other traffic (such as IM, Browsing, etc) will go down an unsecured tunnel. Now that you have your SSH server set up, basically you have 3 tunnels if you connect to VPN. Secure Corporate, Secure Public, and Unsecured Public (for the applications you can’t configure SOCKS for)

Step 4: Browse Securely

Now that you have your secure setup, you can browse with more confidence. You still need to be careful, but your traffic is pretty much unreadable my would be hackers. I tested this by running it on an XP Virtual Machine, while running Wireshark on my Vista box and all the traffic was unreadable.

 Once you get back home though, you need to reverse all the SOCKS settings in your client applications so you can browse again from your internal network. That is unless you want to connect to SSH from your internal network, but that is just overkill and bad performance.

As far as connection speeds, some people really complain that is slow. I haven’t really noticed. It is a bit slower, but I would rather it be a little slower and secure than fast and wide open. For casual browsing, reading feeds news, etc, it is fine.

Other Stuff:

I set up all this using a Vista box for the backend server and a Vista box for the client. In our testing we found that you need to run Putty as an administrator for it to work. I actually downloaded Ubuntu Linux 7.04 as a VMWare image, loaded up VMWare player and tested using the built in SSH client and that worked fine, so I knew my SSH Server was working. Also, I tested using a Windows XP SP2 VPC Image using Cygwin as the SSH client and it worked fine as well. So remember, if you are on Vista, you need Putty and you need to run it as an administrator!!

Since I have only been running this for around two days, there are still some bugs to be worked out. Every so often you might receive an error from Putty about an abnormal packet received. It basically disconnects you. You probably are fine since your client applications are still configured to use the proxy, so if you try to browse you will get an error, you need to shutdown Putty, and then reconnect to your SSH server, then you can browse just fine again.

I have tested this on Unsecured networks at local coffee shops, and as I write this blog post, I am sitting at Starbucks, connected to T-Mobile hotspot, securely tunneling through SSH to my server in my apartment, browsing securely – just need to login to the hotspot first, then connect to SSH, and change your client application settings.

Technorati tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Categories
Random

Hack Free Wi-Fi Day?

Today must be hack free wi-fi day or something. Today I did my laundry, 4 loads. Every time I went up and down the the laundry room there was a different person from the building sitting on the basement stairs close to the coffee shop, hacking the wifi. Does anyone pay for cable modem’s anymore?

Speaking of coffee shop wi-fi, the last few days I have been working on a little home project, a secure SSH server/proxy so I can use that when I am over unsecured wi-fi. More details on that to come!

Technorati tags: , , , , ,