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.<\/p>\n
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.<\/p>\n
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…<\/p>\n
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:<\/p>\n
<Directory \/path\/to\/validated\/directory>\r\n\r\nAuthName \"**String to show the user on the frontend**\"\r\nAuthType Basic\r\nAuth_MySQL On\r\nAuthBasicAuthoritative Off\r\nAuthUserFile \"\/dev\/null\"\r\nAuth_MySQL_Host **db_host**\r\nAuth_MySQL_DB **db_schema**\r\nAuth_MySQL_User **db_user**\r\nAuth_MySQL_Password **db_password**\r\nAuth_MySQL_Password_Table **db_auth_table**\r\n\r\n#optional\r\nAuth_MySQL_Group_Table **db_group_table**\r\nAuth_MySQL_Username_Field **user_field_in_auth_table**\r\nAuth_MySQL_Password_Field **password_field_in_auth_table**\r\n# Plaintext, Crypt_DES, Mysql, PHP_MD5\r\nAuth_MySQL_Encryption_Types Plaintext\r\nAuth_MySQL_Group_Field **group_field_in_group_table\r\nAuth_MySQL_Empty_Passwords off\r\n\r\nrequire valid-user\r\n\r\n# optional\r\nrequire group **group_to_check_against**\r\n\r\n<\/Directory>\r\n<\/code><\/pre>\n* This configuration may require further changes to match your’s<\/p>\n
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?):<\/p>\n
a2enmod auth_mysql<\/code><\/pre>\nWhen 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.<\/p>\n
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…).<\/p>\n
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:<\/p>\n
# mod_dbd configuration\r\nDBDriver pgsql\r\nDBDParams \"dbname=apacheauth user=apache password=xxxxxx\"\r\n\r\nDBDMin\u00a0 4\r\nDBDKeep 8\r\nDBDMax\u00a0 20\r\nDBDExptime 300\r\n\r\n<Directory \/usr\/www\/myhost\/private>\r\n# core authentication and mod_auth_basic configuration\r\n# for mod_authn_dbd\r\nAuthType Basic\r\nAuthName \"My Server\"\r\nAuthBasicProvider dbd\r\n\r\n# core authorization configuration\r\nRequire valid-user\r\n\r\n# mod_authn_dbd SQL query to authenticate a user\r\nAuthDBDUserPWQuery \\\r\n\"SELECT password FROM authn WHERE user = %s\"\r\n<\/Directory><\/code><\/pre>\nAs 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.<\/p>\n
The final configuration keeps as this (I’ll reduce the less important lines assuming they are fine):<\/p>\n
DBDriver mysql\r\nDBDParams \"dbname=**db_schema** user=**db_user** password=**db_password**\"\r\n\r\n<Directory \/var\/www\/php\/pesca\/pesca3\/privat>\r\nAuthName \"**String to show the user on the frontend**\"\r\nAuthType Basic\r\n\r\nAuthBasicProvider dbd\r\nAuthDBDUserPWQuery \"SELECT **password_fileld** FROM TBLCLI WHERE STRUID=%s\"\r\n\r\nRequire valid-user\r\n<\/Directory><\/code><\/pre>\nNo 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:<\/p>\n
sudo apt-get install libaprutil1-dbd-mysql<\/code><\/pre>\nThe 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:<\/p>\n
\n- dbd<\/li>\n
- authz_core<\/li>\n
- authz_dbd<\/li>\n
- authz_user<\/li>\n<\/ul>\n
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):<\/p>\n
\n- It seems to not connect to the mySQL server begging there’s no password<\/li>\n
- 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<\/li>\n
- 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)<\/li>\n
- 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…)<\/li>\n
- It seems default field for apache validation is password, maybe it don’t likes mine, maybe a “AS password” wil help in the validation<\/li>\n
- 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)<\/li>\n<\/ul>\n
The final fact to make it work, is:<\/p>\n
\n- 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<\/li>\n
- 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:\n
\n- Doesn’t provide high security encription (no problem, we were trying to make it work, and these are intramachine communications)<\/li>\n
- The ENCRYPT funcion will use just 8 length passwords, and discard the rest of the password (I now know it, will tell my “customers”)<\/li>\n
- 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)<\/li>\n<\/ul>\n<\/ul>\n
So the final configuration ends in a way like this:<\/p>\n
DBDriver mysql\r\nDBDParams \"dbname=**db_schema** user=**db_user** pass=**db_password**\"\r\nDBDMin\u00a0 4\r\nDBDKeep 8\r\nDBDMax\u00a0 20\r\nDBDExptime 300\r\n\r\n<Directory \/path\/to\/validated\/directory>\r\n\r\nAuthName \"**String to show the user on the frontend**\"\r\nAuthType Basic\r\n\r\nAuthBasicProvider dbd\r\nAuthDBDUserPWQuery \"SELECT ENCRYPT(**password_field**) AS password FROM **validation_table** WHERE **user_field**=%s\"\r\n\r\nRequire valid-user\r\n<\/Directory><\/code><\/pre>\nYou 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.<\/p>\n
This would work without the AS password part of the MySQL instance, but I prefer to left it here to make it clear.<\/p>\n
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’].
\nAny other fields added here will be available in any apache script, so there will be no need to launch another db query.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,7,4,5],"tags":[],"_links":{"self":[{"href":"http:\/\/iosnow.net\/index.php?rest_route=\/wp\/v2\/posts\/21"}],"collection":[{"href":"http:\/\/iosnow.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/iosnow.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/iosnow.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/iosnow.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=21"}],"version-history":[{"count":5,"href":"http:\/\/iosnow.net\/index.php?rest_route=\/wp\/v2\/posts\/21\/revisions"}],"predecessor-version":[{"id":26,"href":"http:\/\/iosnow.net\/index.php?rest_route=\/wp\/v2\/posts\/21\/revisions\/26"}],"wp:attachment":[{"href":"http:\/\/iosnow.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=21"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/iosnow.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=21"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/iosnow.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=21"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}