Hi, allthough I know this is mainly a IOS blog, I’ve got this problem this Eastern and I have found no places with real info to solve, so I will try to explain quite a bit how to configure apache basic mysql authentication in ubuntu 14.04.

The first thing to notice is that ubuntu 14.04 comes bundled with apache 2.4, MySQL 5.5 and PHP 5.5. So this document will.

The second is that this kind of validation is not the state-of-the-art or mood validation, so we’ll assume a migration from an older version which was working great in another LTS till 14.04 launch…

We will start hapy and confident with a current working mod_auth_mysql configuration in a 12.04 ubuntu server machine, with an apache domain configuration like this (ommited non-relevant lines:

<Directory /path/to/validated/directory>

AuthName "**String to show the user on the frontend**"
AuthType Basic
Auth_MySQL On
AuthBasicAuthoritative Off
AuthUserFile "/dev/null"
Auth_MySQL_Host **db_host**
Auth_MySQL_DB **db_schema**
Auth_MySQL_User **db_user**
Auth_MySQL_Password **db_password**
Auth_MySQL_Password_Table **db_auth_table**

#optional
Auth_MySQL_Group_Table **db_group_table**
Auth_MySQL_Username_Field **user_field_in_auth_table**
Auth_MySQL_Password_Field **password_field_in_auth_table**
# Plaintext, Crypt_DES, Mysql, PHP_MD5
Auth_MySQL_Encryption_Types Plaintext
Auth_MySQL_Group_Field **group_field_in_group_table
Auth_MySQL_Empty_Passwords off

require valid-user

# optional
require group **group_to_check_against**

</Directory>

* This configuration may require further changes to match your’s

So after upgrading to Ubuntu 14.04, apache does not validate config because maybe auth_mysql or other modules are not activated in apache. The documentation here makes you scream, as you can see AuthMySQL, Auth_MySQL and Auth_MysqlEnable for enabling the module, and apache is falling with “Invalid command ‘AuthMySQL’, perhaps misspelled or defined by a module not included in the server configuration”, you don’t know if configuration has changed or was working in the past but was wrong, everything seems right (apache starts) after re-enabling the module with the command (if the module was enabled in the 12.04 installation, why ubuntu didn’t left if enabled in the new one?):

a2enmod auth_mysql

When you believe you have a lucky day and all will work like a charm right now, the real problem begins, apache 2.4 on ubuntu 14.04 fails validation and keeps reporting 401 errors and “[:error] [pid xxxx] No requires line available” in the log file.

After some search, you may realized apache 2.2 left support for mod_auth_mysql, but at that version where still working, seems the lucky days for mod_auth_mysql has gone away (but still on ubuntu repository…).

Then you notice 2.2 and up has a new integrated validation schema you should use instead of the mod_auth_mysql solution, so create a new config with the info from the apache documentation site (what could be wrong, you need info, go to the source). The apache website prints a configuration like this:

# mod_dbd configuration
DBDriver pgsql
DBDParams "dbname=apacheauth user=apache password=xxxxxx"

DBDMin  4
DBDKeep 8
DBDMax  20
DBDExptime 300

<Directory /usr/www/myhost/private>
# core authentication and mod_auth_basic configuration
# for mod_authn_dbd
AuthType Basic
AuthName "My Server"
AuthBasicProvider dbd

# core authorization configuration
Require valid-user

# mod_authn_dbd SQL query to authenticate a user
AuthDBDUserPWQuery \
"SELECT password FROM authn WHERE user = %s"
</Directory>

As this configuration uses postgreSQL as database and uses a upper caching layer, we’ll try the easy one and delete the caching part (we are not a big site [I guess, no big site will use this configuration today]) and will make the configuration work for sure, we can always enable caching latter.

The final configuration keeps as this (I’ll reduce the less important lines assuming they are fine):

DBDriver mysql
DBDParams "dbname=**db_schema** user=**db_user** password=**db_password**"

<Directory /var/www/php/pesca/pesca3/privat>
AuthName "**String to show the user on the frontend**"
AuthType Basic

AuthBasicProvider dbd
AuthDBDUserPWQuery "SELECT **password_fileld** FROM TBLCLI WHERE STRUID=%s"

Require valid-user
</Directory>

No way, first we have to make sure all the packages to make mod_authn_dbd to use a mysql server are installed to avoid apache starting errors, so after the error: “DBD: Can’t load driver file apr_dbd_mysql.so” the first thing to do is install libaprutil1-dbd-mysql with the command:

sudo apt-get install libaprutil1-dbd-mysql

The second thing to do is make sure all the module dependencies to make mod_authn_dbd connect to the mysql server are satisfied or apache2 service will refuse to start:

• dbd
• authz_core
• authz_dbd
• authz_user

Now is where everything comes to gray, you will notice and/or try to do some of these things to clear the mess (please try any combination and shake firmly):

• It seems to not connect to the mySQL server begging there’s no password
• Mysql md5 and sha1 encryptions are incompatible with what apache understand for sha or md5, you may try to add some CONCAT(‘{SHA}:’, sha1(**password_field**)) without success
• Maybe there’s a problem with the user, maybe we should add some ‘ around %s, that SELECT will not execute on the mysql server alone (no way)
• plain password support does not exists in linux (clear difference here as it seems it really works in Unix, still some out there? I can here the eco…)
• It seems default field for apache validation is password, maybe it don’t likes mine, maybe a “AS password” wil help in the validation
• Try to generate an external password generation program to insert them in the database so apache could retrieve their sha password (some people has done this with success)

The final fact to make it work, is:

• Thanks to Ubuntu guys or a problem in the apache documentation (hope not because this mistake is here since 2.2 documentation), the DBD configuration line doesn’t work with password=xxx but with pass=xxx
• The plain text is of no use anymore, BUT an old behaviour is still working with the MySQL ENCRYPT(**password_field**) with the following limitations:
  • Doesn’t provide high security encription (no problem, we were trying to make it work, and these are intramachine communications)
  • The ENCRYPT funcion will use just 8 length passwords, and discard the rest of the password (I now know it, will tell my “customers”)
  • The ENCRYPT funcion is very old and nobody recomends it’s use (I now know it, please don’t convert to obsolete and delete from mysql functions in the future)

So the final configuration ends in a way like this:

DBDriver mysql
DBDParams "dbname=**db_schema** user=**db_user** pass=**db_password**"
DBDMin  4
DBDKeep 8
DBDMax  20
DBDExptime 300

<Directory /path/to/validated/directory>

AuthName "**String to show the user on the frontend**"
AuthType Basic

AuthBasicProvider dbd
AuthDBDUserPWQuery "SELECT ENCRYPT(**password_field**) AS password FROM **validation_table** WHERE **user_field**=%s"

Require valid-user
</Directory>

You should start with just 1 job for validation, feel free to change to your needs, as memory is not a problem here I left the numbers from the apache doc example.

This would work without the AS password part of the MySQL instance, but I prefer to left it here to make it clear.

In PHP you can access the authenticated username and password the same way it was before with auth_mysql with the variables $_SERVER[‘PHP_AUTH_USER’] and $_SERVER[‘PHP_AUTH_PW’].
Any other fields added here will be available in any apache script, so there will be no need to launch another db query.