phpMyAdmin always gives "Access denied" when using HTTP authentication.

[Kenneth_61]

I have the above problem.

I found the following information
====================================
[4.4] phpMyAdmin always gives "Access denied" when using HTTP authentication.
This could happen for several reasons:

$cfg['Servers'][$i]['controluser'] and/or $cfg['Servers'][$i]['controlpass'] are wrong.
The username/password you specify in the login-dialog are invalid.
You have already setup a security mechanism for the phpMyAdmin-directory, eg. a .htaccess file. This would interfere with phpMyAdmin's authentication, so remove it.
============================================

Here is my config.inc.php

$cfg['Servers'][$i]['host'] = '192.168.1.2'; // MySQL hostname or IP address
$cfg['Servers'][$i]['port'] = ''; // MySQL port - leave blank for default port
$cfg['Servers'][$i]['socket'] = ''; // Path to the socket - leave blank for default socket
$cfg['Servers'][$i]['connect_type'] = 'tcp'; // How to connect to MySQL server ('tcp' or 'socket')
$cfg['Servers'][$i]['extension'] = 'mysql'; // The php MySQL extension to use ('mysql' or 'mysqli')
$cfg['Servers'][$i]['compress'] = FALSE; // Use compressed protocol for the MySQL connection
// (requires PHP >= 4.3.0)
$cfg['Servers'][$i]['controluser'] = 'root'; // MySQL control user settings
// (this user must have read-only
$cfg['Servers'][$i]['controlpass'] = 'rootIDpassword'; // access to the "mysql/user"
// and "mysql/db" tables).
// The controluser is also
// used for all relational
// features (pmadb)
$cfg['Servers'][$i]['auth_type'] = 'http'; // Authentication method (config, http or cookie based)?
$cfg['Servers'][$i]['user'] = ''; // login user
$cfg['Servers'][$i]['password'] = ''; // MySQL password (only needed
// with 'config' auth_type)
$cfg['Servers'][$i]['only_db'] = ''; // If set to a db-name, only
// this db is displayed in left frame
// It may also be an array of db-names, where sorting order is relevant.
$cfg['Servers'][$i]['verbose'] = ''; // Verbose name for this host - leave blank to show the hostname

Q1) Anyone know why I cannot login to my SQL ? The root password is correct.

Q2) I do not know what .htaccess means. I cannot find information in internet. Sorry.

[Alex Lavrov.]

You must configure apache for this:

http://httpd.apache.org/docs/howto/auth.html

Alex.

[Ermin Borovac · Accepted solution]

Please check mysql.user table for host name.

mysql> select user, host from user;

Then configure $cfg['Servers'][$i]['host'] in config.inc.php with host name found in mysql.user (localhost or ???).

[Kenneth_61]

When I run the command, it display below. Is that normal to have 2 root, 2 localhost, 2 server.mydomain.com ?

I had use server.mydomain.com instead of using static 192.168.1.2. Problem still.

# -----+------------------------+
| user | host |
+------+------------------------+
| | localhost |
| root | localhost |
| | server.mydomain.com |
| root | server.mydomain.com |
+------+------------------------+
4 rows in set (0.00 sec)

[Ermin Borovac]

Have you tried using 'localhost' instead?

What version of PHP do you have?

These 4 entries in mysql.user table are quite normal.

[Kenneth_61]

I had tried localhost. PHP version is 4.3.8

[Ermin Borovac]

Please check apache log (/logs/error_log) for any errors.

Try starting mysqld with --log=/path/to/mysql.log option. Then examine mysql.log after login attempt.

[Kenneth_61]

There is no error in Apache.
However, error display in mysql.log:
What is wrong ? thanks.
============================================
# bin/mysqld --log=/usr/local/mysql/mysql.log --user=mysql &
[1] 29031
# 050522 15:30:20 InnoDB: Started; log sequence number 0 43675
050522 15:30:20 InnoDB: Starting recovery for XA transactions...
050522 15:30:20 InnoDB: 0 transactions in prepared state after recovery
050522 15:30:20 [Note] bin/mysqld: ready for connections.
Version: '5.0.4-beta-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)

# cat mysql.log


bin/mysqld, Version: 5.0.4-beta-standard-log. started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
050522 15:30:44 1 Connect Client does not support authentication protocol requested by server; consider upgrading MySQL cl
ient
050522 15:30:51 2 Connect Client does not support authentication protocol requested by server; consider upgrading MySQL cl
ient
========================================

[Ermin Borovac]

Please try using 'old_passwords' option in my.cnf and reset root password using 'OLD_PASSWORD' SQL function.

http://dev.mysql.com/doc/mysql/en/old-client.html

[Laurent Laperrousaz]

The http server has an IP address which must also be declared in yhe user table in MYSQL.
You have to set an entry for your http server and user (you should avoid root user).
Create a user web in mysql with the full access and with hostname corresponding to IP address of your HTTP server

hope this helps

[Kenneth_61]

Ermin Borovac , You are very helpful ! I can use phpMyadmin now by your method. You know all the things and bring out the key points than manual.