Apache and MySQL Authentication

One of the things that has bugged me over time is how annoying Apache’s usual file-based passwd files are to manage and deal with. I don’t change the configuration very much as a result. adding a new user is tedious, as I never remember the syntax to pass to the utility to create the file, etc. Being on windows, the joys of modules like mod_auth_mysql just didn’t work well, (in fact, in my experience, crashed.) and was rather unreliable. So, when Apache 2.2 was first released, I was excited that one of the new features was SQL Database support. Sweet, I thought, only to quickly find out that, it wasn’t compiled by default for Windows yet. No big deal, I’m rather used to bleeding edge features being a compile-it-yourself kind of thing in open source applications. Last Fall though, I discovered that the module was included in the windows distributions and set about trying to set things up, following the documentation which instructed me to specify a DBDriver directive to select which driver, and even specifies as an example, mysql. Of course, apr_dbd_mysql.so, as referenced on that page wasn’t included, So I set out to find a copy, something I was used to doing for mod_ssl.so, only to find little info, and then to discover, that not only was the module not available by default, it wasn’t yet even in the apache source tree but rather a 3rd party because of licensing issues, and further, that in the 1.2.x version of apr-util, it didn’t even have dynamic module support for the db drivers, so no such apr_dbd_mysql.so existed without patches to the to eventually become 1.3.x apr-util unstable trunk. Not to be beaten, I decided to give the module a try on my linux apache install, patch source tree here, figure out to reconfigure apr-util to be mysql enabled and rebuild, then install, then figure out how to convince mysql to get along so authentication actually worked, after a few hours of banging it surprisingly worked, and worked well. Not ready for my other machines though. So, that’s alot of history, and here’s the good news. since that time, Apache now bundles and is using what was then only a myth, APR 1.3.0. In fact, by default in the windows distribution, you’ll actually now have dbdrivers to pick from, pgsql, sqlite3 and oracle. Still no MySQL. I’ve seen a couple of possible reasons for this, one from the apr-util readme.mysql which suggests licensing issues still apply, not sure if this applies to the httpd distributions or not though. The second, and more important issue I found in a readme.win32 in the httpd-win32 source distribution, also printed here, that the MySQL driver wouldn’t compile on windows. Sure enough, after setting up a MSVC6 build enviroment with the appropriate Platform SDKs to build apache, trying to build apr_dbd_mysql-1.dll resulted in several fatal errors regarding the use of “long long” types which VC6 (which the ASF binaries are built with) doesn’t support. This turns out to be a known issue, already fixed in apr-util 1.3.2 which has been released. (I fully expect that future apache versions (such as a 2.2.10 whenever that is supposed to be) will likely use this version, and whether or not the mysql driver is built we’ll have to see then, but until then…) So I quickly grabbed the windows source trees and after a little bit of fighting with VC6, which i’m rather new to, (No, I don’t want to build APR for x64 with VC6 on a Intel 32-bit CPU, thanks) I successfully compiled apr_dbd_mysql-1.dll. After dropping in the configuration from my tests last year, the new DLL into the bin folder and restarting, things seemed to be ok. (Without the DLL of course, you get this error “DBD: mod_dbd not compatible with APR in get_driver” which is a rather cryptic way of saying that APR doesn’t have the driver you’re looking for.) Pointing my browser to my test directory though, gave me a prompt for password and then promptly, an Internal Server Error (500). doh (Along with this very helpful error message in the log “[error] [client 127.0.0.1] Failed to acquire database connection to look up user ‘wolf'”) reminding me that the apache mysql user actually needed privledges to read from the new DB table.. Fixing that up, and boom, it works. I have only tested it on a limited basis and haven’t tried it in production yet, but much like my previous efforts at compiling, I wanted to share with the great internet masses who don’t have access to a copy of VC6 and just want to enable this functionality into their Apache 2.2.9 (or later, most likely, but not earlier, since it requires APR-1.3 to work) to share in my happiness… So here you go, there’s no warranty here, and if it fails I most likely cannot help you. That disclaimer aside, go ahead eager one, grab it. 🙂 It drops right into the standard apache distribution for windows.

Configuration Notes: This is my configuration, scrubbed for passwords and such.. I have it at the end of the general configuration, before my virtual hosts.. Most of it was stolen from the Apache docs, which aside from being a bit vague about DBDriver are pretty good.

Update for Apache 2.2.11!

For those of you running Apache 2.2.11, there’s a newer version of this file now available, as the bundled APR is now 1.3.4, its available on the downloads page.