Access denied for user root when password and user is correct

chdonncha posted 5 months ago in General

I've had this issue for the past few days with no fixes I can find on the forum here.

The issue is the SSH tunnel to the host server seems to be able to establish a connection successfully but is rejected on connecting to the MYSQL database stating that access is denied for 'root'@'127.0.0.1'. I can confirm the credentials I'm using are correct as I've been able to successfully login using the same credentials on Putty and in terminal.

If anyone knows anything that may possibly help it'd be greatly appreciated!

ansgar posted 5 months ago

Is the local port on the "SSH tunnel" tab different to that one you are probably using for your local MySQL server installation? And on the same tab, is the SSH port correctly set?

chdonncha posted 5 months ago

Checked the port through the SQL server using SHOW GLOBAL VARIABLES LIKE 'PORT'; and returns the port of 3306 which I was trying to connect with. If I change any of the ports it returns an error saying it lost connection to the server rather than saying that access is denied.

chdonncha posted 5 months ago

Solved the problem. Port forwarding was granted to localhost which was confirmed at 127.0.0.1 but also needed port forwarding granted with 127.0.0.1 because it was converted from 'localhost' to 127.0.0.1 automatically in Heidisql. Didn't think this would have been the problem but there we go!

dutchgent posted 1 week ago

I have the same issue and have a question for someone familiar with this program. Using this program to log in via SSH (with privatekey) as not the root but just a sudo user you it is probably causing the denial. If I use Putty to login via SSH and 'sudo mysql -u root -p' I am able to login into the database. If I do not use 'sudo' I am being refused. I prefer not to open SSH to root login. Is there a way to still login as sudo user?

dutchgent posted 1 week ago

Solved it myself. Here is for anyone who may have the same issue in future. Only the root user needs sudo requirement to login to mysql. I resolved this by creating a new user and granting access to the required databases. Open a session in Putty and login mysql using root: mysql -u root -p

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

now newuser can login without sudo requirement:

mysql -u newuser -p

This now will also work using heidisql logging into the db.

Please login to leave a reply, or register at first.