0xCAFEBABE 0xCAFEBABE - 1 year ago 39
SQL Question

Tomcat + MySQL: "Invalid authorization specification" when accessing MySQL via Tomcat application

I have been tasked to replace the hardware an existing Apache/Tomcat/MySQL application is running. The old machines are running Ubuntu, while the new machines have a clean and fresh Debian Squeeze 64 bit installation.

I have copied over pretty much everything, backup up and restored the database on the new database server. I've replaced the database connection parameters in the application on the application server to the IP address of the new database server.

The database can be accessed without any problems on the command line. However, accessing the database using the Tomcat application provides the following error message:

Exception while connecting to database: java.sql.SQLException: Invalid authorization specification message from server: "Access denied for user 'root'@'srvbisonapp.local' (using password: YES)"
java.sql.SQLException: Invalid authorization specification message from server: "Access denied for user 'root'@'srvbisonapp.local' (using password: YES)"
java.sql.SQLException: Invalid authorization specification message from server: "Access denied for user 'root'@'srvbisonapp.local' (using password: YES)"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2001)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1907)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:2524)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:818)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:1808)
at com.mysql.jdbc.Connection.<init>(Connection.java:452)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411)
at java.sql.DriverManager.getConnection(DriverManager.java:620)
at java.sql.DriverManager.getConnection(DriverManager.java:222)
at de.branion.db.mysql.DBConnection.connect(DBConnection.java:107)
at de.branion.db.mysql.DBConnection.connectPermissions(DBConnection.java:41)
at de.branion.permissions.User.loadFromDB(User.java:75)
at de.branion.permissions.User.login(User.java:360)
at org.apache.jsp.login_jsp._jspService(login_jsp.java:133)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:636)

Accessing the database with the wrong credentials does not work, and it gives a different error message. So the problem is not with the actual credentials. As I said, access still works on the command line using

mysql -u root -p

As I tried to google the problem, I saw that many had the same kind of trouble over the years, but not many had a real solution. What I've tried so far:

  • Changing the Host parameter of the login the Tomcat application is using (found in the error message) to "%",

  • Granting not "ALL" privileges, but granting individual privileges (SELECT, INSERT etc.) to the user Tomcat is using

Both were suggestions I found on the net, and both did not help.

I fear it's something esoteric like the old .jar used for accessing MySQL databases is not compatible with the new (recent) version of MySQL, but does not say so. I hope to find a solution that does not require reworking the transfered application, so I'm asking you guys.

I can provide any information that you require.

Thank you.

Answer Source

Access denied for user 'root'@'srvbisonapp.local' (using password: YES)

You can check previledges for this user by executing this command,

show grants for 'root'@'srvbisonapp.local';

And see what privileges this user has.

Also this error clearly indicates that authentication detail is wrong for this user you can change password of existing user using root account of MySQL

GRANT USAGE ON *.* TO 'root'@'srvbisonapp.local' IDENTIFIED BY 'password_you_wish';

Make necessary changes in connection parameter in Tomcat application, this should work