ProFTPd – Login not possible, using remote MySQL user auth

Problem

ProFTPd could not authenticate user from a MySQL database.

Analysis

Context: CentOS 5, ProFTPd with mod_sql.c, mod_sql_mysql.c and MySQL on an other server than proftpd runs.

Prerequisities:

• You must correctly configure MySQL permissions to allow access (SELECT) to proftpd database from remote server (IP based).

To debug things, you may turn on SQL logs in /etc/proftpd.conf:

<IfModule mod_sql.c>
  ...
  SQLLogFile /var/log/proftpd/mod_sql.log
  ...
</IfModule>

Solution

In /etc/proftpd.conf change

AuthOrder                      mod_auth_pam.c* mod_auth_unix.c

to the following:

#AuthOrder                      mod_auth_pam.c* mod_auth_unix.c
AuthOrder                      mod_sql.c mod_auth_unix.c

Then restart server, or run in debug mode:

# proftpd -nd9

Then try to connect to proftpd and to log in.

References

• http://isp-control.net/forum/thread-8418.html
• http://www.proftpd.org/docs/howto/Debugging.html
• http://www.proftpd.org/docs/contrib/mod_sql.html#SQLLogFile
• http://www.proftpd.org/docs/howto/SQL.html
• http://www.proftpd.org/docs/contrib/mod_sql.html