I have 2 FTP servers running both mainly using sFTP on port 2222 AND FTP on port 21. On top of the ProFTPd system I have a proftpd_admin tool running: proftpd-admin link1

That uses a MySQL backend for users. but it turns out that the sFTP on port 2222 uses this user database, but the FTP part is using the local system accounts to access the server.

I would like to use the same MySQL database to serv both services. I added the SQL section in the global section of the proftpd.conf and there is the sftp service defined.

In the virtual server I defined the FTP service, but when I add the load module mysql it fails to start in general....

Do you have any tips for me where and how to define the mysql usage in the virtual server for the ftp service..?


[edit] P.S. here is my proftpd.conf

 ServerName             "Some FTP Delivery server"
 ServerIdent            on "FTP Server ready."
 ServerAdmin            root@localhost
 DefaultServer          on
 DefaultAddress         372.124.444.395
 DefaultRoot            ~ !adm
 TimeoutIdle            300
 TimeoutNoTransfer      600
 UseIPv6                off

 #AuthPAMConfig         proftpd
 UseReverseDNS          off

 User               nobody
 Group              nobody

 MaxInstances         100
 RequireValidShell    off
 AllowForeignAddress  on
 AllowRetrieveRestart on
 AllowStoreRestart    on

 UseSendfile         off
 Umask               000 000
 PassivePorts        65100 65199

 LogFormat           default    "%h %l %u %t \"%r\" %s %b"
 LogFormat           auth   "%v [%P] %h %t \"%r\" %s"
 LogFormat           write           "%h %l %u %t \"%r\" %s %b"
 TransferLog         /var/log/proftpd/xfer.log
 ExtendedLog         /var/log/proftpd/auth.log      AUTH auth
 AuthOrder           mod_sql.c         

 # Modules to load section.
 LoadModule mod_ctrls_admin.c
 LoadModule mod_vroot.c
 LoadModule mod_sftp.c
 #LoadModule mod_sftp_sql.c
 LoadModule mod_sql.c
 LoadModule mod_sql_mysql.c
 LoadModule mod_quotatab.c
 LoadModule mod_quotatab_sql.c
 LoadModule mod_wrap.c
 LoadModule mod_rewrite.c
 LoadModule mod_shaper.c

 LoadModule mod_ifsession.c

 ModuleControlsACLs     insmod,rmmod allow user root
 ModuleControlsACLs     lsmod allow user *

 ControlsEngine         on
 ControlsACLs           all allow user root
 ControlsSocketACL      allow user *
 ControlsLog            /var/log/proftpd/controls.log

 <IfModule mod_ctrls_admin.c>
   AdminControlsEngine      on
   AdminControlsACLs        all allow user root

 <IfModule mod_vroot.c>
   VRootEngine              on

 <IfDefine TLS>
   TLSEngine                on
   TLSRequired              on
   TLSRSACertificateFile    /etc/pki/tls/certs/proftpd.pem
   TLSRSACertificateKeyFile /etc/pki/tls/certs/proftpd.pem
   TLSCipherSuite           ALL:!ADH:!DES
   TLSOptions               NoCertRequest
   TLSVerifyClient          off
   TLSLog                   /var/log/proftpd/tls.log
   <IfModule mod_tls_shmcache.c>
     TLSSessionCache        shm:/file=/var/run/proftpd/sesscache


  LoadModule        mod_ban.c
  BanEngine         on
  BanLog            /var/log/proftpd/ban.log
  BanTable          /var/run/proftpd/ban.tab
  BanOnEvent        MaxLoginAttempts 2/00:10:00 01:00:00
  BanMessage        "Host %a has been banned"
  BanControlsACLs   all allow user ftpadm

# Set networking-specific "Quality of Service" (QoS) bits on the 
packets used
# by the server (contrib/mod_qos.html)
<IfDefine QOS>
  LoadModule            mod_qos.c
  # RFC791 TOS parameter compatibility
  QoSOptions            dataqos throughput ctrlqos lowdelay
  # For a DSCP environment (may require tweaking)
  #QoSOptions           dataqos CS2 ctrlqos AF41

  AllowOverwrite        yes

  ShowSymlinks on
  <IfModule mod_sql.c>
    SQLBackend                      mysql
    SQLAuthTypes                    Backend
    SQLConnectInfo                  proftpd@ proftpd proftpd
    SQLUserInfo                     usertable userid passwd uid gid 
homedir shell
    SQLGroupInfo                    grouptable groupname gid members
    SQLUserWhereClause              "disabled=0 and (NOW()<=expiration 
or expiration=-1 or expiration=0)"
    SQLLog PASS counter
    SQLNamedQuery counter UPDATE "lastlogin=now(), count=count+1 WHERE 
userid='%u'" usertable

    SQLLog EXIT time_logout
    SQLNamedQuery time_logout UPDATE "lastlogout=now() WHERE 
userid='%u'" usertable

    SQLNamedQuery login_time SELECT "lastlogin from usertable where 
    #SQLShowInfo PASS "230" "Last login was: %{login_time}"

    SQLLog RETR,STOR transfer1
    SQLNamedQuery  transfer1 INSERT "'%u', '%f', '%b', '%h', '%a', 
'%m', '%T', now(), 'c', NULL" xfer_stat
    SQLLog ERR_RETR,ERR_STOR transfer2
    SQLNamedQuery  transfer2 INSERT "'%u', '%f', '%b', '%h', '%a', 
'%m', '%T', now(), 'i', NULL" xfer_stat

  <IfModule mod_quotatab.c>
    QuotaEngine on
    QuotaDirectoryTally on
    QuotaDisplayUnits Mb
    QuotaShowQuotas on
    QuotaOptions ScanOnLogin
    QuotaLog /var/log/proftpd/quota.log

    SQLNamedQuery get-quota-limit SELECT "name, quota_type, 
per_session, limit_type, bytes_in_avail, bytes_out_avail, 
bytes_xfer_avail, files_in_avail, files_out_avail, files_xfer_avail 
FROM ftpquotalimits WHERE name = '%{0}' AND quota_type = '%{1}'"
    SQLNamedQuery get-quota-tally SELECT "name, quota_type, 
bytes_in_used, bytes_out_used, bytes_xfer_used, files_in_used, 
files_out_used, files_xfer_used FROM ftpquotatallies WHERE name = '% 
{0}' AND quota_type = '%{1}'"
    SQLNamedQuery update-quota-tally UPDATE "bytes_in_used = 
bytes_in_used + %{0}, bytes_out_used = bytes_out_used + %{1}, 
bytes_xfer_used = bytes_xfer_used + %{2}, files_in_used = 
files_in_used + %{3}, files_out_used = files_out_used + %{4}, 
files_xfer_used = files_xfer_used + %{5} WHERE name = '%{6}' AND 
quota_type = '%{7}'" ftpquotatallies
    SQLNamedQuery insert-quota-tally INSERT "%{0}, %{1}, %{2}, %{3}, % 
{4}, %{5}, %{6}, %{7}" ftpquotatallies

    QuotaLimitTable sql:/get-quota-limit
    QuotaTallyTable sql:/get-quota-tally/update-quota-tally/insert- 


<IfModule mod_sftp.c>
    SFTPEngine                      on
    Port                            2222
    SFTPLog                 /var/log/proftpd/sftp.log
    SFTPAuthMethods             password #publickey
    SFTPHostKey             /etc/ssh/ssh_host_dsa_key
    SFTPHostKey             /etc/ssh/ssh_host_rsa_key

#  port              21
#  DefaultRoot       ~ !admins
#  MaxClientsPerHost 25 "Sorry, no more!"
#  MaxClients      50 "Sorry, too much users.."

Include /etc/proftpd/classes.conf
Include /etc/proftpd/groups.conf
Include /etc/proftpd/shaper.conf
Include /etc/proftpd/limits.conf
    Include /etc/proftpd/virtuals.conf
  • serverfault.com/q/570961/131555 you could consult that, I think the configuration does pretty much what you want – Kiwy Mar 28 '18 at 9:19
  • also without your ftp config file it will be hard to suggest anything – Kiwy Mar 28 '18 at 9:39
  • Edited the main post to add the proftpd.conf Thanks for the tip. – SHLelieveld Apr 6 '18 at 9:46
  • OK how does it fails ? try to run it from terminal not as a service what is the ouput ? also is your configuration correct you could test with proftpd -c /path/to/new/config/file -td10 – Kiwy Apr 6 '18 at 10:26
  • The server is running sFTP does use the SQL database, but for FTP it uses local system account. If you use the wrong one it just says authentication failure. – SHLelieveld Apr 6 '18 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.