How can I diagnose root login failure to Google Cloud SQL?

4
votes

When attempting to connect to a Google Cloud SQL instance via various MySQL clients, the root login is now failing with the error:

$ mysql --host=173.194.x.x --user=root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'98.225.x.x' (using password: YES)

I was previously able to login and perform various tasks (create a local user, add tables and store procedures, etc.).

I can still login as the local user I created.

So, I have:

  • Confirmed the instance "IP Address"
  • Confirmed the "Authorized Networks"
  • Set and reset the "Root Password" multiple times
  • "Reset SSL Configuration"
  • Cleared the checkbox for "Only allow SSL connections"
  • "Restarted" the instance multiple times

Any suggestions on diagnosing root login failure?

Thanks for any help.

1
google-cloud-sql
With the user that can connect to the DB, can you check: SELECT user,host,ssl_type FROM mysql.user; and see if root@% has ssl_type set to X509? if it does, delete the user from that table and reset the password from the UI. - Juan Enrique Muñoz Zolotoochin
Thanks, Juan, but that user does not permission to query "users": ERROR 1142 (42000): SELECT command denied to user 'roms'@'98.225.x.x' for table 'user' Are you thinking I should reenable SSL? - Bob Davidson

1 Answers

8
votes

It is a manifestation of a bug. Please follow the status of the bug here.

As a workaround please try the following steps:

  1. Add SSL to the instance (if one is not in place)
  2. Connect using user = root with the SSL
  3. Remove the user root@%
  4. Uncheck (if checked) the "Only allow SSL connections"
  5. Set the root password again
  6. Now try and connect with user = root and the password that was set.