How to install and Configure ProFTP with MySQL

ProFTP: ProFTPD is FTP server that can easily installed and configured on Linux machine. ProFTP support both FTP and SFTP protocol.

 Prerequisites- 

• One Linux System with MySQL 5.6 admin access
• A user with Sudo access.
• An FTP client to test server and configuration.

Step 1- Install MySQL Server 5.6 if not installed already. 
Add the MySQL 5.6 PPA repository

root@Server1:~# add-apt-repository -y ppa:ondrej/mysql-5.6

Run apt-get update to update the list of the repository.

root@Server1:~# apt-get update

Install MySQL server 5.6 using the command below

root@Server1:~# apt-get install mysql-server-5.6

Setup MySQL root user password during installation when asked.

Retype again when asked.

 MySQL-SERVER5.6 installation completed. let's proceed for Proftp installation

Step 2- Install Proftp server with Proftp MySQL modules. Run the following command to start the installation.

root@Server1:~# apt-get install proftpd-basic proftpd-mod-mysql

This will install all the required packages. If the installation asks, choose the 

standalone mode.

Step 3- Create Database: In order to create database login to MySQL server using root.

root@Server1:~# mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.35-1+deb.sury.org~xenial+0.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Now, Create proftp database, We will use this database to store username and password for FTP authentication.

mysql> create database proftp;
Query OK, 1 row affected (0.03 sec)

Step 4- Create a user and assign permission on the proftp database.

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON proftp.* TO 'proftp'@'localhost' IDENTIFIED BY 'ftpdpass';
Query OK, 0 rows affected (0.00 sec)

Run Flush privileges command to reload user permission.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.05 sec)

Step 5- Create two tables in the proftp database, one for the user and another for Group.

Table 1 name ftpuser

mysql> use proftp;
Database changed
mysql> CREATE TABLE ftpuser (
    -> id int(10) unsigned NOT NULL auto_increment,
    -> userid varchar(32) NOT NULL default '',
    -> passwd varchar(32) NOT NULL default '',
    -> uid smallint(6) NOT NULL default '5500',
    -> gid smallint(6) NOT NULL default '5500',
    -> homedir varchar(255) NOT NULL default '',
    -> shell varchar(16) NOT NULL default '/sbin/nologin',
    -> count int(11) NOT NULL default '0',
    -> accessed datetime NOT NULL default '0000-00-00 00:00:00',
    -> modified datetime NOT NULL default '0000-00-00 00:00:00',
    -> PRIMARY KEY (id),
    -> UNIQUE KEY userid (userid)
    -> ) ENGINE=MyISAM COMMENT='ProFTP user table';
Query OK, 0 rows affected (0.01 sec)

Table 2 name ftpgroup

mysql> CREATE TABLE ftpgroup (
    -> groupname varchar(16) NOT NULL default '',
    -> gid smallint(6) NOT NULL default '5500',
    -> members varchar(16) NOT NULL default '',
    -> KEY groupname (groupname)
    -> ) ENGINE=MyISAM COMMENT='ProFTP group table';
Query OK, 0 rows affected (0.03 sec)

Step 6- Create FTP user and password- In order to log on to the FTP server we need an FTP user and password, let's create one user using the following command.

Please replace User1 with  Username and "md5}4nmK8Sp6D09wtNae+8JfTQ=="with password and /home/user1 path your desire path.

Please generate an encrypted password using the following command.

root@Server1:~# /bin/echo "{md5}"`/bin/echo -n "Enter_Your_Password_Here" | openssl dgst -binary -md5 | openssl enc -base64` 

Now, Simply replace the command output  from "md5}4nmK8Sp6D09wtNae+8JfTQ=="

mysql> INSERT INTO `ftpuser` (`id`, `userid`, `passwd`, `uid`, `gid`, `homedir`,
 `shell`, `count`, `accessed`, `modified`) VALUES (4, 'user1', '{md5}4nmK8Sp6D09wtNae+8JfTQ==', 2001
, 2001, '/home/user1', '/bin/bash', 0, '', '');;

Now exit from the MySQL access shell

mysql> quit;
Bye

Step 7- Configure Proftp configuration to allow MySQL authentication, FTP connection and SFTP connection.

7A- Modify proftpd.conf:

root@Server1:~# vi /etc/proftpd/proftpd.conf

Uncomment or add the highlighted lines in your proftpd.conf file.

# /etc/proftpd/proftpd.conf -- This is a basic ProFTPD configuration file.
# To really apply changes, reload proftpd after modifications, if
# it runs in daemon mode. It is not required in inetd/xinetd mode.
#

# Includes DSO modules
Include /etc/proftpd/modules.conf

# Set off to disable IPv6 support which is annoying on IPv4 only boxes.
UseIPv6                         on
# If set on you can experience a longer connection delay in many cases.
IdentLookups                    off

ServerName                      "Debian"
ServerType                      standalone
DeferWelcome                    off

MultilineRFC2228                on
DefaultServer                   on
ShowSymlinks                    on

TimeoutNoTransfer               600
TimeoutStalled                  600
TimeoutIdle                     1200

DisplayLogin                    welcome.msg
DisplayChdir                    .message true
ListOptions                     "-l"

DenyFilter                      \*.*/

# Use this to jail all users in their homes
 DefaultRoot                    ~

# Users require a valid shell listed in /etc/shells to login.
# Use this directive to release that constrain.
 RequireValidShell              off
 CreateHome on

 Include /etc/proftpd/sql.conf
# Port 21 is the standard FTP port.
Port                            21

# In some cases you have to specify passive ports range to by-pass

Save and exit from the file.

7B- Edit /etc/proftpd/sql.conf file to allow connection between Proftpd and MySQL database for authentication.

Edit sql.conf  configuration file using the command below.

root@Server1:~# vi /etc/proftpd/sql.conf

Append the following line into above file.

# Connection
#SQLConnectInfo proftpd@sql.example.com proftpd_user proftpd_password
#
# Describes both users/groups tables
#
#SQLUserInfo users userid passwd uid gid homedir shell
#SQLGroupInfo groups groupname gid members
#
#</IfModule>


SQLBackend        mysql

#Passwords in MySQL are encrypted using CRYPT
SQLAuthTypes            OpenSSL Crypt
SQLAuthenticate         users groups


# used to connect to the database
# databasename@host database_user user_password
SQLConnectInfo  mysql_database@localhost mysql_user mysql_password


# Here we tell ProFTPd the names of the database columns in the "usertable"
# we want it to interact with. Match the names with those in the db
SQLUserInfo     ftpuser userid passwd uid gid homedir shell

# Here we tell ProFTPd the names of the database columns in the "grouptable"
# we want it to interact with. Again the names match with those in the db
SQLGroupInfo    ftpgroup groupname gid members

# set min UID and GID - otherwise these are 999 each
SQLMinID        500

# Update count every time user logs in
SQLLog PASS updatecount
SQLNamedQuery updatecount UPDATE "count=count+1, accessed=now() WHERE userid='%u'" ftpuser

# Update modified everytime user uploads or deletes a file
SQLLog  STOR,DELE modified
SQLNamedQuery modified UPDATE "modified=now() WHERE userid='%u'" ftpuser

SqlLogFile /var/log/proftpd/sql.log

Once you update your FTP database, UserName and password.

Save and exit from the file.
7C- Enable proftpd MySQL module in the module.conf file.

root@Server1:~# vi /etc/proftpd/modules.conf

Uncomment these two lines:

# Install one of proftpd-mod-mysql, proftpd-mod-pgsql or any other
# SQL backend engine to use this module and the required backend.
# This module must be mandatory loaded before anyone of
# the existent SQL backeds.


LoadModule mod_sql.c

# Install proftpd-mod-ldap to use this
#LoadModule mod_ldap.c

#
# 'SQLBackend mysql' or 'SQLBackend postgres' (or any other valid backend) directives
# are required to have SQL authorization working. You can also comment out the
# unused module here, in alternative.
#

# Install proftpd-mod-mysql and decomment the previous
# mod_sql.c module to use this.
LoadModule mod_sql_mysql.c

# Install proftpd-mod-pgsql and decomment the previous
# mod_sql.c module to use this.
#LoadModule mod_sql_postgres.c

Save and exit from the file

7D- Restart Proftpd server service.

root@Server1:~# systemctl restart proftpd.service

Check Service status using the following command and make sure it is Active.

root@Server1:~# systemctl status proftpd.service
● proftpd.service - LSB: Starts ProFTPD daemon
   Loaded: loaded (/etc/init.d/proftpd; bad; vendor preset: enabled)
   Active: active (running) since Wed 2018-08-08 10:31:21 IST; 7s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 25166 ExecStop=/etc/init.d/proftpd stop (code=exited, status=0/SUCCESS)
  Process: 25180 ExecStart=/etc/init.d/proftpd start (code=exited, status=0/SUCCESS)
    Tasks: 1
   Memory: 4.1M
      CPU: 134ms
   CGroup: /system.slice/proftpd.service
           └─25192 proftpd: (accepting connections)

Aug 08 10:31:08 Server1 systemd[1]: Starting LSB: Starts ProFTPD daemon...
Aug 08 10:31:08 Server1 proftpd[25180]:  * Starting ftp server proftpd
Aug 08 10:31:21 Server1 proftpd[25180]:    ...done.
Aug 08 10:31:21 Server1 systemd[1]: Started LSB: Starts ProFTPD daemon.

Step 8- Test FTP server with Port 21.

Use any FTP client to test, In my case I'm using Winscp client with following user settings.

After Successful login, you will be able to see the folder list.

Step 9- Setup Proftpd to accept SFTP connections.

In order to allow SFTP connection, we need to make the following changes.

9A- Create a virtual host configuration file to access SFTP connection

Use this command to create SFTP configuration file.

root@Server1:~# vi /etc/proftpd/sftpd.conf

Append the following lines into the above file.

<IfModule mod_sftp.c>
    <VirtualHost 192.168.102.10>
        Port            2222
        DefaultRoot     ~/
        AllowOverwrite  on
 CreateHome on
        SFTPEngine      on
        SFTPLog         /var/log/proftpd/sftpd.log
        SFTPHostKey     /etc/ssh/ssh_host_dsa_key
        SFTPHostKey     /etc/ssh/ssh_host_rsa_key
        SFTPAuthMethods password
 Include /etc/proftpd/sql.conf
        SQLAuthenticate on
        SQLConnectInfo  ftp_Databased@localhost ftpd_USER ftpd_password
 RequireValidShell            off
        SQLAuthTypes Plaintext
        SQLUserInfo users userid passwd uid gid homedir shell

        SQLLog PASS updatecount
        SQLNamedQuery updatecount UPDATE "count=count+1 where userid='%u'" users
    </VirtualHost>
</IfModule>

Make the highlighted changes with your settings, Save and Exit from the file.

9B- Include SFTP.conf file in Proftpd main configuration file.

Use the command below to include SFTP configuration file.

root@Server1:~# vi /etc/proftpd/proftpd.conf

Add this line in the last of proftpd.conf file.

Include /etc/proftpd/sftpd.conf

Save and exit from the file.

Step 10 - Restart proftpd server service and test SFTP connection.

root@Server1:~# systemctl restart proftpd.service

Test SFTP connection

SFTP connection listing all the directories successfully.

Proftpd successfully configured with MySQL authentication.

Troubleshooting- In case of any issue while establishing the connection, Please review the following logs file to see the error.

Proftp connection logs

tail -f /var/log/proftpd/proftpd.log

SQL logs

tail -f /var/log/proftpd/sql.log

SFTP connection logs

tail -f /var/log/proftpd/sftpd.log

Additional changes: Enable MD5 encryption to store user's password in a database-
Sometime may company need a strong encryption to store user's password in the database, here I'm going to describe how to enable MD5 encryption in Proftpd with MySQL as backend. Follow the steps below to enable MD5 
encryption: 
Step 1- Enable mod_sql_passwd.c Module in the module.conf file.
Open module.conf file using vi editor.

root@Server1:~# vi /etc/proftpd/modules.conf

Uncomment the following line or add if not exist. 

LoadModule mod_sql_passwd.c

Save and exit from the file.
Step 2- Modify the sql.conf file to accept MD5 encryption 
Open the sql.conf file in vim editor.

root@Server1:~# vi /etc/proftpd/sql.conf

Append the following line 

SQLAuthTypes MD5
SQLPasswordEngine on
SQLPasswordEncoding hex
SQLEngine on

Comment the following line. Earlier we use this Crypt method now we have to change it to MD5.

# SQLAuthTypes Crypt

Step 3- Restart proftpd service
Run the following command to restart proftpd server service.

root@Server1:~# systemctl status proftpd.service

Step 4- Update user's password to MD5 encryption, if already added encrypt the password.

mysql > use proftp;
database changed
mysql> update `ftpuser` set passwd=md5('Enter_password_here') where id=Enter_User's_ID_here;

Now user's password will store with MD5 encryption.

Thanks for reading my Blog, please leave your comment in the comment box for any feedback and suggestion.