Any idea why MySQL authentication is failing

by Nov 4, 2014

I have two VMs, one running Windows 7, one running Linux/Ubuntu. In the Windows 7 VM I have ADS 14.0.8 (licensed). In the Linux VM I have installed ADS 16.0.1 (evaluation).

I have a MySQL database registered in ADS 14.0.8 on Windows, and it connects successfully. I’m trying to register the same MySQL DB in the 16.0.1 instance on Linux, with the same Login Name/Password, Host, Port,and Database. When I Test Connection, I get this message:

“Access denied for user ‘[user_name]@[VM_IP_Address’ (using password: YES)”

Any ideas why this instance would fail authentication while the other one would work?

Response

Rinaldo Bonazzo over 8 years ago
Hi tspitzer,

Hi think that the was due to a mysql configuration problem

while you can access to you mysql server via windows have a look at the user config and thus the connection for the user with server linux maybe this page can help

ask.xmodulo.com/create-configure-mysql-user-command-line.html

Regards

Rinaldo

Niels Gron over 8 years ago
Your permissions probably prevent the connection from that IP address.

Before connecting to MySQL you will need to GRANT access to your user and machine. Because the MySQL JDBC driver uses TCP/IP sockets instead of Unix Domain Sockets, the security manager in MySQL server will use the HOSTS table to determine whether or not the connection should be allowed. To grant your user access use this syntax: GRANT ALL PRIVILEGES ON [dbname].* to ’[user]’@’[hostname]’ identified by ’[password]’ to generate and execute an SQL command.

See documentation here : https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation16/page/31/MySQL

Tom Spitzer over 8 years ago
Thanks guys. The original user was set up with a wildcard hostname, which I thought was why my first client was able to connect.

I worked around this by creating a new MySQL user specific to my new VM. That works fine for now, since its a dev server.

Shavibala Rajhans over 8 years ago
If you have not reset a default root password then please follow 1-4 steps. And if you have already performed those steps then ignore them and create user by following steps 5-6 and login through the created user in ADS.

1] Set Password:

cat /root/.mysql_secret (it will give you the random password generated during installation)
2] Start our newly installed MySQL server:

service mysql start

3] Login into database server:

mysql -uroot -p

Enter password:(enter password got after running cat /root/.mysql_secret command)

4] Change root users default password:

mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘mysql’);

Query OK, 0 rows affected (0.12 sec)

5] Create User to access database:

mysql> CREATE USER ‘superadmin’@’%’ IDENTIFIED BY ‘mysql’;

Query OK, 0 rows affected (0.02 sec)

6] Grant permissions to created user:

mysql> GRANT ALL ON *.* TO ‘superadmin’@’%’ WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

After performing above steps still it gives you an error then please let me know the MySQL version and Distribution you have installed.