pure-ftp - Access denied for user 'root'@'localhost'

Discussion in 'Installation/Configuration' started by tom, Jun 19, 2009.

  1. tom

    tom Member

    I've created a ftpuser and a password but login is not possible.

    Syslog says:
    Code:
    Jun 19 15:04:42 server2 pure-ftpd: ([email protected]) [ERROR] The SQL server seems to be down [Access denied for user 'root'@'localhost' (using password: YES)]
    But:
    Code:
    server2:/var/log# netstat -tap | grep mysql
    tcp        0      0 *:mysql                 *:*                     LISTEN      1465/mysqld     
    tcp        0      0 localhost.localdo:mysql localhost.localdo:55791 VERBUNDEN   1465/mysqld     
    tcp        0      0 localhost.localdo:mysql localhost.localdo:46320 VERBUNDEN   1465/mysqld 
    Any idea?
     
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    Then the password or login credentials in the pureftpd file are wrong or your mysql server does not allow connections from localhost. Please try to login with the mysql username and password from the pureftpd config file with the mysql command on the shell.
     
  3. tom

    tom Member

    I's even not possible to login to mysql from the shell with the ftp user and password.
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    Please try what I suggested above. as long as pureftpd is not able to login it can not authenticate FTP users, so we will have to find out whats wrong with the mysql login and then ftp users will work too. The FTP user can not login to mysql as its a FTP only login, so please try the mysql user that you can find in the pureftpd config file.

    If this user does not work, try to login with the mysql user and password that you find in the file /usr/local/ispconfig/server/lib/config.inc.php

    Additionally, did you change the mysql password for the user named "ispconfig"?
     
    Last edited: Jun 19, 2009
  5. tom

    tom Member

    What pure-ftp config file you mean? Im using debian but I cant find a config file with user and password. Where is it and what the name of the config file?
    ispconfig can log in.
    no
     
    Last edited: Jun 19, 2009
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    The file is /etc/pure-ftpd/db/mysql.conf and the content should look like this:

    Code:
    ##############################################
    #                                            #
    # Sample Pure-FTPd Mysql configuration file. #
    # See README.MySQL for explanations.         #
    #                                            #
    ##############################################
    
    
    # Optional : MySQL server name or IP. Don't define this for unix sockets.
    
    MYSQLServer     {mysql_server_ip}
    
    
    # Optional : MySQL port. Don't define this if a local unix socket is used.
    
    # MYSQLPort       3306
    
    
    # Optional : define the location of mysql.sock if the server runs on this host.
    
    # MYSQLSocket      /var/run/mysqld/mysqld.sock
    
    
    # Mandatory : user to bind the server as.
    
    MYSQLUser       ispconfig
    
    
    # Mandatory : user password. You must have a password.
    
    MYSQLPassword   xwetxgbuw5x3w5qx63y63
    
    
    # Mandatory : database to open.
    
    MYSQLDatabase   localhost
    
    
    # Mandatory : how passwords are stored
    # Valid values are : "cleartext", "crypt", "md5" and "password"
    # ("password" = MySQL password() function)
    # You can also use "any" to try "crypt", "md5" *and* "password"
    
    MYSQLCrypt      crypt
    
    
    # In the following directives, parts of the strings are replaced at
    # run-time before performing queries :
    #
    # \L is replaced by the login of the user trying to authenticate.
    # \I is replaced by the IP address the user connected to.
    # \P is replaced by the port number the user connected to.
    # \R is replaced by the IP address the user connected from.
    # \D is replaced by the remote IP address, as a long decimal number.
    #
    # Very complex queries can be performed using these substitution strings,
    # especially for virtual hosting.
    
    
    # Query to execute in order to fetch the password
    
    MYSQLGetPW      SELECT password FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L"
    
    
    # Query to execute in order to fetch the system user name or uid
    
    MYSQLGetUID     SELECT uid FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L"
    
    
    # Optional : default UID - if set this overrides MYSQLGetUID
    
    #MYSQLDefaultUID 1000
    
    
    # Query to execute in order to fetch the system user group or gid
    
    MYSQLGetGID     SELECT gid FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L"
    
    
    # Optional : default GID - if set this overrides MYSQLGetGID
    
    #MYSQLDefaultGID 1000
    
    
    # Query to execute in order to fetch the home directory
    
    MYSQLGetDir     SELECT dir FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND username="\L"
    
    
    # Optional : query to get the maximal number of files 
    # Pure-FTPd must have been compiled with virtual quotas support.
    
    MySQLGetQTAFS  SELECT quota_files FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND quota_files != '-1' AND username="\L"
    
    
    # Optional : query to get the maximal disk usage (virtual quotas)
    # The number should be in Megabytes.
    # Pure-FTPd must have been compiled with virtual quotas support.
    
    MySQLGetQTASZ  SELECT quota_size FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND quota_size != '-1' AND username="\L"
    
    
    # Optional : ratios. The server has to be compiled with ratio support.
    
    MySQLGetRatioUL SELECT ul_ratio FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND ul_ratio != '-1' AND username="\L"
    MySQLGetRatioDL SELECT dl_ratio FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND dl_ratio != '-1' AND username="\L"
    
    
    # Optional : bandwidth throttling.
    # The server has to be compiled with throttling support.
    # Values are in KB/s .
    
    MySQLGetBandwidthUL SELECT ul_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND ul_bandwidth != '-1' AND username="\L"
    MySQLGetBandwidthDL SELECT dl_bandwidth FROM ftp_user WHERE active = 'y' AND server_id = '{server_id}' AND dl_bandwidth != '-1' AND username="\L"
    
    # Enable ~ expansion. NEVER ENABLE THIS BLINDLY UNLESS :
    # 1) You know what you are doing.
    # 2) Real and virtual users match.
    
    # MySQLForceTildeExpansion 1
    
    
    # If you upgraded your tables to transactionnal tables (Gemini,
    # BerkeleyDB, Innobase...), you can enable SQL transactions to
    # avoid races. Leave this commented if you are using the
    # traditionnal MyIsam databases or old (< 3.23.x) MySQL versions.
    
    MySQLTransactions On
    
     
  7. tom

    tom Member

    my looks quite different ..

    I will try it with your.
    It seems that it has to be like that:
    But where from comes the password?
    Code:
    ##############################################
    #                                            #
    # Sample Pure-FTPd Mysql configuration file. #
    # See README.MySQL for explanations.         #
    #                                            #
    ##############################################
    
    
    # Optional : MySQL server name or IP. Don't define this for unix sockets.
    
    # MYSQLServer     127.0.0.1
    
    
    # Optional : MySQL port. Don't define this if a local unix socket is used.
    
    # MYSQLPort       3306
    
    
    # Optional : define the location of mysql.sock if the server runs on this host.
    
    MYSQLSocket      /var/run/mysqld/mysqld.sock
    
    
    # Mandatory : user to bind the server as.
    
    MYSQLUser       root
    
    
    # Mandatory : user password. You must have a password.
    
    MYSQLPassword   rootpw
    
    
    # Mandatory : database to open.
    
    MYSQLDatabase   pureftpd
    
    
    # Mandatory : how passwords are stored
    # Valid values are : "cleartext", "crypt", "md5" and "password"
    # ("password" = MySQL password() function)
    # You can also use "any" to try "crypt", "md5" *and* "password"
    
    MYSQLCrypt      cleartext
    
    
    # In the following directives, parts of the strings are replaced at
    # run-time before performing queries :
    #
    # \L is replaced by the login of the user trying to authenticate.
    # \I is replaced by the IP address the user connected to.
    # \P is replaced by the port number the user connected to.
    # \R is replaced by the IP address the user connected from.
    # \D is replaced by the remote IP address, as a long decimal number.
    #
    # Very complex queries can be performed using these substitution strings,
    # especially for virtual hosting.
    
    
    # Query to execute in order to fetch the password
    
    MYSQLGetPW      SELECT Password FROM users WHERE User="\L"
    
    
    # Query to execute in order to fetch the system user name or uid
    
    MYSQLGetUID     SELECT Uid FROM users WHERE User="\L"
    
    
    # Optional : default UID - if set this overrides MYSQLGetUID
    
    #MYSQLDefaultUID 1000
    
    
    # Query to execute in order to fetch the system user group or gid
    
    MYSQLGetGID     SELECT Gid FROM users WHERE User="\L"
    
    
    # Optional : default GID - if set this overrides MYSQLGetGID
    
    #MYSQLDefaultGID 1000
    
    
    # Query to execute in order to fetch the home directory
    
    MYSQLGetDir     SELECT Dir FROM users WHERE User="\L"
    
    
    # Optional : query to get the maximal number of files 
    # Pure-FTPd must have been compiled with virtual quotas support.
    
    # MySQLGetQTAFS  SELECT QuotaFiles FROM users WHERE User="\L"
    
    
    # Optional : query to get the maximal disk usage (virtual quotas)
    # The number should be in Megabytes.
    # Pure-FTPd must have been compiled with virtual quotas support.
    
    # MySQLGetQTASZ  SELECT QuotaSize FROM users WHERE User="\L"
    
    
    # Optional : ratios. The server has to be compiled with ratio support.
    
    # MySQLGetRatioUL SELECT ULRatio FROM users WHERE User="\L"
    # MySQLGetRatioDL SELECT DLRatio FROM users WHERE User="\L"
    
    
    # Optional : bandwidth throttling.
    # The server has to be compiled with throttling support.
    # Values are in KB/s .
    
    # MySQLGetBandwidthUL SELECT ULBandwidth FROM users WHERE User="\L"
    # MySQLGetBandwidthDL SELECT DLBandwidth FROM users WHERE User="\L"
    
    # Enable ~ expansion. NEVER ENABLE THIS BLINDLY UNLESS :
    # 1) You know what you are doing.
    # 2) Real and virtual users match.
    
    # MySQLForceTildeExpansion 1
    
    
    # If you upgraded your tables to transactionnal tables (Gemini,
    # BerkeleyDB, Innobase...), you can enable SQL transactions to
    # avoid races. Leave this commented if you are using the
    # traditionnal MyIsam databases or old (< 3.23.x) MySQL versions.
    
    # MySQLTransactions On
    
     
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    The username is ispconfig and the password is the same then in the config.inc.php file. This file is installed by ISPConfig, if yours is different then it might have been overwritten by an debian update or pureftpd was installed after ispconfig.
     
  9. tom

    tom Member

    I've edit the password but still no login for ftp users ... from the shell it does
    Code:
    Jun 19 17:23:28 server2 pure-ftpd: ([email protected]) [ERROR] The SQL server seems to be down [Unknown MySQL server host '{mysql_server_ip}' (1)]
     
    Last edited: Jun 19, 2009
  10. till

    till Super Moderator Staff Member ISPConfig Developer

    You have to replace

    {mysql_server_ip}

    with:

    127.0.0.1

    in the file and restart preftpd
     
  11. tom

    tom Member

    done

    and the password are the same now in config.inc.php and mysql.conf but still no login from the shell ...
    Code:
    un 19 17:34:47 server2 pure-ftpd: ([email protected]) [ERROR] The SQL server seems to be down [Access denied for user 'ispconfig'@'localhost' to database 'localhost']
     
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    Ok, then there is a problem with your mysql setup or hostname setup. Please post the content of the /etc/hosts file
     
  13. tom

    tom Member

    Code:
    127.0.0.1 localhost.localdomain localhost
    # Auto-generated hostname. Please do not remove this comment.
    81.169.1x1.xxx h1609xxx.ratoserver.net server2
     
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    Thats starange, you said a few posts above that you are able to login with the mysql command on the shell:

    mysql -h localhost -u ispconfig -p

    and then providing the password. Are you really sure that it works, please try again.
     
  15. tom

    tom Member

    yes:
    server2:/# mysql -u ispconfig -p
    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 5607
    Server version: 5.0.51a-24+lenny1 (Debian)

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql>
     
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    Ok, thats strange. I have no idea why pureftpd is not able to login if it uses the exact same login details.
     
  17. sphax3d

    sphax3d New Member

    You should see the following post ;) It worked for me :)
    http://www.howtoforge.com/forums/showpost.php?p=243125&postcount=10
    Code:
    $ sudo service pure-ftpd-mysql status
     * could not access PID file for pure-ftpd
    $ ps aux | grep pure
    <user>   353  0.0  0.0   3340   808 pts/0    S+   21:02   0:00 grep --color=auto pure
    root     25898  0.0  0.0   7796   856 ?        Ss   09:51   0:00 pure-ftpd
    $ sudo kill -9 25898
    $ ps aux | grep pure
    <user>   357  0.0  0.0   3340   808 pts/0    S+   21:03   0:00 grep --color=auto pure
    $ sudo service pure-ftpd-mysql start
    Starting ftp server: Running: /usr/sbin/pure-ftpd-mysql -l mysql:/etc/pure-ftpd/db/mysql.conf -l pam -j -O clf:/var/log/pure-ftpd/transfer.log -E -A -u 1000 -U 002:002 -8 UTF-8 -B
    $ sudo service pure-ftpd-mysql status
     * pure-ftpd is running
     
    Last edited: Dec 9, 2010

Share This Page