0

I have a Java Web application deployed on Amazon Cloud and I have migrated a local MySQL database to a remote Amazon RDS MySQL database through MySQL workbench.

In spite of having been able to connect from workbench on port 3306 to my AWS MySQL (to migrate the data) database, I am not able to connect my local Java application to the AWS MySQL database.

I am pretty sure my java driver and connection is setup right however, i keep getting this error when I run the project in Tomcat:

java.sql.SQLException: Access denied for user 'root'@'176.61.103.100' (using password: YES).

Does anybody know how to resolve this error or can anybody provide a fast track solution to getting a Java application on Amazon with a AWS MySQL database feeding in data to it?

Dodo
  • 51
  • 1
  • 6
  • 1
    When you connected with the workbench, did you also log in as user `root` like you are doing when you connect from Java? – Erwin Bolwidt Apr 10 '17 at 01:44
  • try executing this command frim mysql prompt `GRANT ALL ON *.* to root@'%' IDENTIFIED BY '';` in this command please replace root password with your root passwd. – Amit Apr 10 '17 at 06:16
  • @ErwinBolwidt I used both the username and password created when setting up my Amazon MySQL database, to connect from my MySQL workbench to my Amazon MySQL. I also used the same username and password to try and connect my Java application to the AWS MySQL database. I am right in saying, I should be able to connect my local eclipse java project to a remote database through the Driver manager class: DriverManager.getConnection("jdbc:mysql://hostname:3306/data‌​base", "usersname", "password") ? This code worked perfectly for connecting to my local MySQL database. – Dodo Apr 10 '17 at 18:15
  • @AmitK I did exactly what you said and I got this following Error Code in MySQL Workbench: 19:21:06 GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'password>' Error Code: 1045. Access denied for user 'root'@'%' (using password: YES) 0.266 sec – Dodo Apr 10 '17 at 18:23

1 Answers1

0

In MySQL the users access can be restricted by hosts. Check which users you have with:

SELECT host, user FROM mysql.user;

You will notice that the root user can only be accessed by specific hosts. You need to enable the access for the new IP(s).

Try enabling the generic access from your workbench with:

GRANT ALL PRIVILEGES *.* TO root@'%' IDENTIFIED BY PASSWORD '<MySql root user password>';

Afterwards try again, the privileges should become active immediately.

Udo Held
  • 12,314
  • 11
  • 67
  • 93
  • I entered your first query successfully and retrieved the data displaying the users. However, when I ran your second script, I received firstly a syntax error. I then proceeded to use the script provided above by another stackoverflow user, which had a similar objective to your own however, it gave the Error - Error Code: 1045. Access denied for user 'root'@'%' (using password: YES) 0.266 sec. – Dodo Apr 10 '17 at 19:00
  • Is the new 'root'@'%' now shown in the users list? You check the other options listed on http://stackoverflow.com/questions/21944936/error-1045-28000-access-denied-for-user-rootlocalhost-using-password-y, but make sure you use the host wildcard when trying stuff. – Udo Held Apr 10 '17 at 22:04
  • Thank you so much for your help. It turns out I think I had the root password wrong. I can now run the application locally with the Amazon MySQL database feeding data into it. However, I deployed the exact same code on Amazon and the Java application isn't connecting to the Amazon MySQL database. Any ideas why it wouldn't run the same on Amazon? – Dodo Apr 11 '17 at 19:55