Application Vault Problem: Connection Error

[magic_77]

Hi,

after a server crash i´ve installed a new system with plesk 10.3. Dumped back all mysql stuff and restored all users.
system is running like a charm but with one issue that happened (i didn´t know that before) - so that
application vault runs into trouble.
clicking on "Application Vault" results in
"Internal error: [unixODBC][MySQL][ODBC 3.51 Driver]Access denied for user 'apsc'@'localhost' (using password: YES)"

How to reset the user APSC? or where can i set the password for this user?
can anybody help me out, please

thx
Marek

 

[IgorG]

Check connection possibility from localhost - http://forum.parallels.com/showpost.php?p=452137&postcount=5

 

[magic_77]

Hi IgorG,

connecting with "mysql -h 127.0.0.1 -P 3306 -uadmin -p`cat /etc/psa/.psa.shadow`" results in a successful connection.
Because i dumped back the APSC Mysql DB from an other Installation i fear that the passwords doesn´t match.

don´t know what to do.

Greets
Marek

 

[IgorG]

Try to update password for 'apsc' user in 'mysql' database 'users' table.

 

[magic_77]

Hi IgorG,

tried several passwords, still not better. So where does this system stores passwords that are send to mysql to authorize.

passwords i tried are from admin, root

greets marek

 

[DimitriyH]

IF anyone still need this, I managed to solve the problem. The problem I had was that grants for apsc where set to localhost, however in configuration for Applications host was set to "127.0.0.1" as a result apsc was unable to connect to apsc db. As a solution you need to make update either in GRANTS e.g. set to 127.0.0.1 or set localhost for connections.
steps:
check grants that are set in mysql db and make sure we are going to make correct changes:
[root@ ]#mysql -uadmin -p`cat /etc/psa/.psa.shadow`
mysql> use mysql;
mysql> select * from user where User='apsc';SHOW GRANTS FOR 'apsc'@'localhost';
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| localhost | apsc | *09C714AAB4B6F7B4704EAC765B6ECBEC26FBF5E9 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
1 row in set (0.00 sec)

+-------------------------------------------------------------------------------------------------------------+
| Grants for apsc@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'apsc'@'localhost' IDENTIFIED BY PASSWORD '*09C714AAB4B6F7B4704EAC765B6ECBEC26FBF5E9' |
| GRANT ALL PRIVILEGES ON `apsc`.* TO 'apsc'@'localhost' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
--------->8<----------
As you can see grants are configured to connect from localhost
now we need to check settings that are set in Plesk DB
--------->8<----------
mysql> use psa;
mysql> select * from misc where param like '%aps%';
+--------------+--------------+
| param | val |
+--------------+--------------+
| aps_database | apsc |
| aps_host | 127.0.0.1 |
| aps_login | apsc |
| aps_password | <YOU CURRENT PASSWORD>|
| aps_port | 3306 |
+--------------+--------------+
5 rows in set (0.00 sec)

mysql>
------->8<-------
as you can see from these lines apsc is trying to connect through 127.0.0.1, so In my case I had to change "127.0.0.1" to "localhost". After this you can confirm if everything is ok by running the following command from shell:
------->8<-------
mysql -hlocalhost -uapsc -p<YOU CURRENT PASSWORD> -P3306 apsc
------->8<-------

P.S. of course it's primitive solution but anyway...

 

[magic_77]

Hi DimitriyH,
hey - many thx for your reply.
My Results for this are:
mysql> select * from user where User='apsc';
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+------------- -------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv e_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+------------- -------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+
| localhost | apsc | *09C714AAB4B6F7B4704EAC765B6ECBEC26FBF5E9 | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+------------- -------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'apsc'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'apsc' on host 'localhost'

Maybe you have an advice - ´cause my sql skill are to low for that

thx
marek

 

[DimitriyH]

execute the following commands and provide results:
1 - SHOW GRANTS FOR 'apsc'@'127.0.0.1';

2 - select * from misc where param like '%aps%';



in case "1" will not give any results then you will need to add grants manually:

a) GRANT USAGE ON *.* TO 'apsc'@'localhost' IDENTIFIED BY PASSWORD '*09C714AAB4B6F7B4704EAC765B6ECBEC26FBF5E9'
b) GRANT ALL PRIVILEGES ON `apsc`.* TO 'apsc'@'localhost'

P.S. What Plesk version you are using?

 

[magic_77]

ok - doing this:

# REVOKE ALL PRIVILEGES ON *.* FROM 'apsc'@'localhost';
# FLUSH PRIVILEGES;

set´s GRANTS on user. i changed "aps_host" to localhost, but still get an
ERROR 1045 (28000): Access denied for user 'apsc'@'localhost' (using password: YES)

 

[magic_77]

select * from misc where param like '%aps%';
+--------------+--------------+
| param | val |
+--------------+--------------+
| aps_database | apsc |
| aps_host | localhost |
| aps_login | apsc |
| aps_password | eK1I4F5X24Va |
| aps_port | 3306 |
+--------------+--------------+

this is the result from PSA
and i´m using Plesk 10.31