Simple MySQL database redundancy (with load balancing) can easily be obtained using PHP:

function connectDatabase($sDatabaseName) {
  $sUsername = 'myusername';
  $sPassword = 'mypassword';
  // database server hostnames
  $arrDBServers = array(
    "192.168.100.101",
    "192.168.100.102",
    "192.168.100.103",
  );
  shuffle($arrDBServers);

  // While we have available servers which we can connect to...
  while (count($arrServers)) {
    $sHostname = array_shift($arrDBServers);
    if ($resSQL= @mysql_connect($sHostname, $sUsername, $sPassword, true))
      break;
  }

 if ($resSQL)
   if (mysql_select_db($sDatabaseName, $resSQL))
     return $resSQL;

 // if we get here, something is very wrong!
 // you should replace this line with your own error handling
 die( mysql_error() );
}

Once implemented, ensure you modify your php.ini accordingly to allow failover to happen in a timely manner in the event of a connection timeout..  In a default PHP installation the mysql_connect_timeout is set to 60 seconds, I changed this on my install to just 1 second. Don't forget to restart apache once you're done modifying php.ini !

I hope you will find this snippet useful when your web app starts to get busy!

Recently it became necessary to expose a MySQL instance to some customers across the internet. The MySQL daemon had previously been locked down using the my.cnf skip-networking directive. I removed this the MySQL configuration file and restarted the daemon. Sockstat showed MySQL was now listening but when I attempted to connect from a remote site I received this cryptic error message:

 ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Nothing showed on any logs (system logs or even the MySQL error log) so I was stumped for a little bit. Also, connecting to the mysql server via its socket or localhost worked just fine.  After some painful moments I finally figured out what the problem was - MySQL uses tcp wrappers and so I had to make the appropriate entry in /etc/hosts.allow:

mysqld : ALL : allow

Once added remote connections were instantly accepted and the error message went away - hurray!

Microsoft Dynamics RMS is a Microsoft SQL based ePoS application which ships by default with MSSQL Desktop Edition (MSDE).   MSDE is a free entry level offering to the SQL Server database software family from Microsoft.  Althou free, MSDE does have its limitations; the most painful being a 2GB database size limit.

Dynamics RMS archives receipts of all transactions processed by the point of sale to the journal table. Over time, this table can grow to be very big (primarily xml bloat).

A customer who runs a busy store has been running for years without any problems but after approx 3 years their daily backup process was producing backups which exceeded the size of a single layer DVD (~4.7GB). This was starting to cause problems so something needed to be done.  You could get something like the Archiver utility from Retail Realm but in this case it was decided that we'd just dump all the receipts which were over 1 year old. Obviously, with disk space being so cheap these days it's no harm to make a database backup now before you go deleting receipts! Keep the backup safe in case you ever need to dig up an old receipt.

This seemed simple, I logged in to the database server using RMS Administrator and executed the following command:

DELETE FROM journal WHERE time < GETDATE() - 365

Unfortunately, this query took a very long time to execute and really seemed to put the server under strain.  As a work around, I decided I would delete the data in smaller bite-sized chunks:

First, transactions older than 3 years were deleted:

DELETE FROM journal WHERE time < GETDATE() - (365 * 3)

Followed by transactions older than 2 years:

DELETE FROM journal WHERE time < GETDATE() - (365 * 2)

Followed by transactions older than a year:

DELETE FROM journal WHERE time < GETDATE() - 365

Each query took awhile to complete but in the end it worked a treat.

A new version of TextBuddy has been released and I'm happy to say that O2 Support is back and working better than ever.  Download it now if you haven't done so already. I realise TextBuddy has been neglected now for  quite sometime..  this latest update includes some major internal changes to the plugin architecture and as a result I will finally commit to adding support for Meteor. You can expect support for this network to be added within 2 weeks.

With PCI compliance regulations tightening up, two factor authentication is becoming more and more important. Before I begin let me just say that I was looking for something which was easy to implement and easy to maintain. What I was after was simple, I just wanted SSH to require a password as well as a publickey and access would not be granted unless BOTH authentication methods were successful.

After much searching for options I discovered a patch which was created by some of the folks over at mindrot.org. Unfortunately many of the patches listed on this thread were out of date and the latest patch would not cleanly apply to the OpenSSH_4.5p1 sources in /usr/src.  After a bit of tinkering I managed to get a patch which applied without any problems.

So.. here's the procedure:

Firstly, ensure your sources are up to date, use cvsup if neccessary. My patch applies to the RELENG_7_0 sources without any issues.  Once your sources are up to date, grab a copy of my modified patch:

[root@orion] (~): cd /usr/src/crypto/openssh/
[root@orion] (/usr/src/crypto/openssh): fetch http://jerrywalsh.org/files/openssh-two-factor-auth.patch
openssh-two-factor-auth.patch                 100% of   22 kB   21 kBps
[root@orion] (/usr/src/crypto/openssh): patch < openssh-two-factor-auth.patch
--snip snip--
Patching file sshd_config.5 using Plan A...
Hunk #1 succeeded at 524 with fuzz 1 (offset -1 lines).
Hunk #2 succeeded at 734 with fuzz 2 (offset -66 lines).
done

No compilation is done from this directory, it is all done from the src/secure directory. The separation between src/contrib and src/crypto is the result of an old USA law, which made these sources export controlled, so they had to be kept separate. Anyway, moving along, now that we've applied the patch we'll need to recompile SSH:

[root@orion] (/usr/src/crypto/openssh): cd /usr/src/secure/ && make clean && make && make install
===> lib (all)
===> lib/libcrypto (all)
cc -O2 -fno-strict-aliasing -pipe -O2 -fno-strict-aliasing -pipe -s  -DTERMIOS -DANSI_SOURCE ..
--snip snip--

This will take a little while as there's a bunch of other stuff in here which will also get compiled. Once it's completed the patched binaries will have been installed. We're not just done quite yet thou...

As you'll see from the mindrot.org link I mentioned earlier, this patch does not support privilegeseparation and after a bunch of trials and tests I discovered you also need to disable PAM support via sshd_config.  Please understand that by disabling privilegeseparation you may be introducing a potential security issue if at some point in the future a vulnerability is discovered and you leave your daemon unpatched against it. For me thou, the benefits provided by this patch far outweight this issue and this combined with the fact that I perform regular patching anyway makes it a non issue. So, let's modify the UsePAM and UsePrivilegeSeparation directives in our sshd_config:

[root@orion] (/usr/src/secure): sed -i '.old' -e ' \
  /^#UsePAM/ s/.*(UsePAM).*/1 no/ \
  /^#UsePrivilegeSeparation/ s/.*(UsePrivilegeSeparation).*/1 no/' \
  /etc/ssh/sshd_config

Finally, we need to specify our newly added configuration directives: RequiredAuthentications1 and RequiredAuthentications2. We use these directives to tell SSH that we want to require BOTH a password AND a publickey - either one of these authentication methods will not be enough to grant access to the machine.

[root@orion] (/usr/src/secure): egrep -q '^RequiredAuthentications1' /etc/ssh/sshd_config || \
        echo "RequiredAuthentications1 password" >> /etc/ssh/sshd_config

[root@orion] (/usr/src/secure): egrep -q '^RequiredAuthentications2' /etc/ssh/sshd_config || \
        echo "RequiredAuthentications2 publickey" >> /etc/ssh/sshd_config

Finally, we need to restart sshd in order for these options and our newly compiled ssh daemon to take effect:

[root@orion] (/usr/src/secure): /etc/rc.d/sshd restart
Stopping sshd.
Starting sshd.
[root@orion] (/usr/src/secure):

Now, this bit is important! Open up a NEW connection to the machine and ensure you can login OK - remember at this point you will need to have your publickey in place, otherwise will you not be able to access the shell!

Assuming you've done everything right at this point connecting via SSH will act a little differently now:

Authenticating with public key "JW" from agent
Authenticated with partial success.
jw@orion's password:
Last login: Thu Nov 13 19:01:40 2008 from 194.125.133.10
Copyright (c) 1980, 1983, 1986, 1988, 1990, 1991, 1993, 1994
        The Regents of the University of California.  All rights reserved.

FreeBSD 7.0-RELEASE-p5 (ORION) #1: Tue Nov 11 15:38:18 UTC 2008

[jw@orion] (~):

Note the "Authenticated with partial success" line above - congratulations! You've just added two factor authentication to OpenSSH!

I hope you found this guide useful, I know if I had found something like this I'd be very thankful! ;)

Until next time..

EDIT: Brandon Nolte kindly has created a rhel5 package that includes this two-factor authentication patch.

The O2 WebSite has been updated and as a result support for O2 in TextBuddy is broken. I will have an updated version available tomorrow night!