//jerrywalsh.org

coding, hacking, startups, computer security, technology and more

Using FreeTDS With Ruby-odbc on Debian Linux

I recently was upgrading a Ruby on Rails installation from 3.2 to 5.2 and as is normal lots of stuff had changed. This particular project involves the use of ruby-odbc and tiny-tds along with activerecord-sqlserver-adapter to provide connectivity to Microsoft SQL server.

The ruby ODBC & TDS gems took quite a version jump after upgrading and required a newer version of FreeTDS than was available via the apt repositories (debian backports was no help either). As a result the only option is to fetch, compile and install freetds yourself. It's always nice to have things configured in an easily reproducible way and so this little script performs the necessary. Simply adjust the version variables (V) and execute the script as root. You should of course ensure you've removed the native freetds-common and freetds-dev packages before you do this. I hope you find it useful.

simple script to compile and install the latest version of FreeTDS
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#!/bin/sh
# fetch and compile freetds from source because the version available from the debian apt
# repository isn't current enough, enter your freetds tarball version string here:
V="freetds-1.00.87"
# --- and begin...
set -e
cd /tmp/
test -e "$V".tar.gz || wget http://www.freetds.org/files/stable/"$V".tar.gz
test -d "$V" || tar xvfzp "$V".tar.gz
cd "$V"

# Enable openssl (recommended):
./configure --prefix=/usr/ --with-tdsver=7.4 --with-openssl --sysconfdir=/etc/

# Disable openssl (not recommended but may be desirable if you're connecting to
# older SQL clients which don't support ciphers which are considered strong enough)
# if you're getting "client library returned TDS_INT_CANCEL" and lowering
# openssl ciphers to the lowest possible setting won't work THEN consider this
# otherwise, leave openssl enabled :)
#./configure --prefix=/usr/ --with-tdsver=7.4 --with-openssl=no --without-openssl --sysconfdir=/etc/
make
make install

Note as indicated in the script above, if you're receiving a client library returned TDS_INT_CANCEL error during connect then this is probably related to the openssl ciphers you're using. You should adjust the ciphers used on either your server or client library so they're compatible.

Upgrading Microsoft RMS With Linux Backed MS SQL 2017 Instance

Retail Management System was a point-of-sale system from Microsoft. Originally called Quicksell 2000 it was acquired by Microsoft from Quicksell Commerce in California somewhere around 2005. Like many things Microsoft end up acquiring the product was eventually left to rot and has now been discontinued from future sales. It's a pity because it was a solid product with a solid ecosystem, it just wasn't a good fit for Microsoft.

Of course, as is common with small to medium sized businesses - whatever works gets used at least until it breaks and so there's a lot of these systems still being used in production today. A long standing client of mine had to recently go about upgrading their primary Microsoft SQL database server and while the server had been providing many years of reliable uninterruptable service it was just starting to become problematic. The final straw was when the PSU suddenly failed one night. The PSU was replaced but it was time to upgrade to a newer machine. A new poweredge server was purchased from Dell Computer Corp. Then the fun and games began...

Bear in mind we're talking about a small 'mom-and-pop' style business here and in order to survive, thrifty is the name of the game. They will typically run whatever technical solution they can get away with for as long as they can get away with it. The old server was running Windows 2000 - not a big problem thou since the sole purpose of the server is just to run SQL server. This meant the new server was providing a massive leap forwards in terms of the technology which would be used. Installing Windows 2000 was no-go at this stage so we had to look at what our options were. Purchasing a newer version of Windows meant more cost for them. I had recently read the news of Microsoft releasing the first 'stable' release of SQL Server 2017 for Linux. It seemed like the perfect opportunity to go test the waters.

The latest copy of Debian Linux was installed on the server and Microsoft SQL Server for Linux was then installed. Here was the test.. I restored a copy of the live database from the SQL Server 2005 instance to the 2017 instance running on Linux. The restore operation went without a hitch - impressive! Next up was the real test thou, I reconfigured one of the existing POS systems to point at the SQL 2017 database running on the Linux server. The application started up like it didn't even realise anything underlying had been changed. So far, so good!

A further barrage of real world tests were conducted and everything seemed to work like the database server was still running on Windows. Seemed to good to be true so for the next couple of weeks I ran the two systems side by side in case we needed to fallback in the event of something going wrong, but nothing did go wrong. Everything worked without a hitch. All the machines on the network functioned exactly how they did as if they were still being supported by a database server running Windows. I don't often tip my hat to Microsoft but you have to hand it to them - it's pretty amazing that you can migrate such a complex piece of software from one OS to another and have it work almost exactly the same. Yes, I've read the technical details of how they architected a platform abstraction layer so they could re-use much of the existing code but it's still an incredible achievement nonetheless.

It's been over 6 months now and the Linux server running SQL server 2017 is actually more stable than the Windows one was. There's no need for a clunky UI and everything is accessible remotely via a terminal which makes it even easier to support. The backup application which was used on Windows was dumped and replaced with a shell script run via cron which performed the necessary. Mozy Pro cli then takes care of the offsite backup aspect.

This was just one of those times a crazy plan came together and went off without a hitch. I felt I had to write about it here since during the migration process I actually found there wasn't much information on the internetz about SQL Server on Linux. The conclusion: it works, and it works really well too!

Life Got in the Way

It's been too long -- as the saying goes, life simply got in the way.

life got in the way

So I'm making a conscious effort to get back to bloging at least on a semi regular basis. Here's hoping I can stick to that commitment!

So what happened ?

For the last 5 years I've been travelling the world leading the life of a digital nomad, hopping from one country to another, exploring this fabulous planet we share. As I write this blog post I'm currently travelling by bicycle ("bikepacking") across the north coast of Italy. The starting point for this trip was the North Coast of France and so far it's taken over 2000km. By the time it's over it will probably be a further 2000km and I'll be somewhere in Germany. At that point I'll pack the bike in a box and fly it back to Ireland.

In case you're unfamiliar, bikepacking is not like your typical road riding cycling affair. Normally you utilize a sturdier (and as a result heavier) bicycle which is typically loaded with panniers which contain your stuff (clothes, laptop, etc.). In my case it's a trusty steel framed "Sutra" bike from Kona cycles. It's not possible to race around the place with this type of configuration as you're just carrying too much weight -- average speed is around 20km/ph, depending on terrain of course. This is my bike:

fully loaded kona sutra from bikepacking trip

What next?

At least in terms of this blog, I plan on keeping it up to date with the usual type of material; technical articles relating to topics I encounter on a day to day basis as well as topics of interest to me; e.g. digital privacy & security.

Your Phone Company Is Watching You

Malte Spitz, a member of Germany’s Green Party, sued his mobile phone provider to reveal what records it kept about him. The records including tracking data based on rough triangulation from the nearest cell-phone towers. Spitz then sent this information to Zeit Online, who combined it with Spitz’ personal blog and Twitter entries to produce an incredible animated map that reveals a surprisingly detailed account of his movements over a six-month period.

To reiterate, this was done all without GPS services and without Spitz requesting his movements be tracked — it was automatically generated simply by his mobile phone communicating with cell phone towers. It very much goes to show the importance of regulations that limit retention periods for this kind of data, given how detailed a reconstruction can be made of an individual’s life.

TextBuddy Is No More! It Was Fun While It Lasted Y'all

TextBuddy was created to make it easy for people to actually utilize the Webtext allowances providers gave them. In the last few weeks some providers (namely O2 and Vodafone) have begun making changes to their webtext pages in order to make it more difficult (but not impossible) for applications such as TextBuddy from operating. Unfortunately it’s just not worth trying to integrate with providers who don’t want you integrating with them.

If this move leaves you high and dry then perhaps something like Cabbage Texter will work instead?

Thanks for using TextBuddy!

HOWTO Secure Your Linux Box With IPTABLES

Okay, so this post will be brief but to the point. Today I needed to lock down a machine I administer so the only inbound connections which were allowed were SSH connections from trusted hosts. I'm using Debian so this will obviously work for other Debian based distros such as Ubuntu, Linux Mint etc.

Assuming you're running a current version of Debian or a derivative then iptables will already be present on your system. One of the first things to take note is that iptables won't hold its ruleset during a reboot so to start off this tutorial the first thing I ensured was that the ruleset will be restored when the machine is rebooted. So, as root I edited /etc/rc.local and before the exit line i added /etc/iptables-init. Because this was a fresh install my rc.local ended up looking like this:

/etc/rc.local from Debian 6
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#!/bin/sh -e
#
# rc.local
#
# This script is executed at the end of each multiuser runlevel.
# Make sure that the script will "exit 0" on success or any other
# value on error.
#
# In order to enable or disable this script just change the execution
# bits.
#
# By default this script does nothing.

# Setup iptables
/etc/iptables-init
exit 0

Next, I created the script which we've setup to be executed from rc.local:

/etc/iptables-init
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#!/bin/sh
# ----------------------------------------------------------------------
# simple but secure iptables initialization script
# DateCreated: Thu 12 Jan 2012 00:37:04 GMT
# Author: Jerry Walsh
# ----------------------------------------------------------------------

# Put your trusted hosts/ranges here:
TRUSTED_HOSTS="1.2.3.4 8.8.8.8/24 \
  4.3.2.1 1.2.2.2 3.3.3.4 "

# flush rules
iptables -F

# Log dropped connections
#iptables -N LOGDROP

# allow localhost connections to the loopback interface 
iptables -A INPUT -i lo -j ACCEPT
iptables -A INPUT ! -i lo -d 127.0.0.0/8 -j REJECT

# allow connections which are already established
iptables -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

# allow all outbound connections
iptables -A OUTPUT -j ACCEPT

# allow tcp to port 22 (ssh daemon) from trusted hosts
for GOODIE in $TRUSTED_HOSTS; do
  iptables -A INPUT -p tcp -m state --state NEW -s $GOODIE --dport 22 -j ACCEPT
done
# or you could just allow ssh access from all hosts
# NOTE: if you're going to allow ssh access from all hosts then
# it's always a good idea to put sshd on a non-standard port
# - this keeps the majority of script kid trawlers out
#iptables -A INPUT -p tcp -m state --state NEW --dport 22 -j ACCEPT

#other optional extras:
# allow inbound http access
#iptables -A INPUT -p tcp -m state --state NEW --dport 80 -j ACCEPT
# allow inbound https access
#iptables -A INPUT -p tcp -m state --state NEW --dport 443 -j ACCEPT

# drop all other inbound traffic (including ICMP, UDP etc.)
iptables -A INPUT -j DROP
# you could also just block tcp connections..
#iptables -A INPUT -p tcp -j DROP

Finally, I set the script executable and executed the script now to load the new rules in to iptables:

finally, we mark the script executable and run it!
1
2
chmod 0700 /etc/iptables-init
!$

And that's it! Remember - it's always good to test your configuration from a remote host or better still from a 'bad' remote host and a 'good' (whitelisted) host.

REMEMBER: The above script is just an example! You should modify the script to meet YOUR needs (as it stands this met mine) but it still serves as a useful starting point. It should also be noted that ICMP ping replies will be blocked using the above setup - this may not be desirable but in my case it was!

John Cleese on Creativity

There's no doubt about it that John Cleese is a genius when it comes to comedy but his genius-ness (is that even a word?) doesn't stop there. What follows is one of my favourite videos from him. The highlight of which is this enlightening little piece of wisdom:

To know how good you are at something requires the same skills as it does to be good at those things. Which means if you're absolutely hopeless at something, you lack exactly the skills that you need to know that you're absolutely hopeless at it. And this is a profound discovery - that most people who have absolutely no idea what they're doing, have absolutely no idea that they have no idea what they're doing.

It explains a great deal of life.

...

It also explains why so many people in charge of so many organisations have no idea what they're doing, they have a terrible blind spot.

Watch the video here:

Finally, I Got My Site in Order!

I know, I know.. it's been long overdue and I've just been putting it off for far too long. A recent trip to Zell Am See, in Austria recently gave me enough downtime to focus on revamping the site which is now complete. I think everything should be working OK but if you encounter problems then please let me know. I hope the new look will get me blogging more often and with higher quality!

I plan to post again soon, this time with something more substantial until then I'll leave you with my "tune of the trip", "Black Ash Veil" by Apparat: