Google Cloud MySQL access denied through private IP but allowed through public IP

Question

I'm trying to connect from my Google Cloud Compute VM to my Google Cloud MySQL instance through its private IP. Both are in the same VPC, and it appears the MySQL instance is actually reached, but denies access with the error:

ERROR 1045 (28000): Access denied for user 'user'@'ip-address' (using password: YES)

I'm 100% sure I'm using the right username and password. If I try to connect from the same VM to the same MySQL server, with the same credentials over public IP (using SSL), it works just fine.

The user I'm trying to connect with also has its host set to wildcard (so it's 'user'@'%'). It has all privileges on the database I want to use (though the access is denied before indicating which table to use, so I'm not sure if this is relevant). The output of SHOW GRANTS for user; is:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'user'@'%' WITH GRANT OPTION

GRANT ALL PRIVILEGES ON `database`.* TO 'user'@'%'

The Google documentation only advises checking your credentials, which I did verify, so I'm hoping anyone here can provide an answer.

You likely need to `GRANT` access via that IP (or permit all IPs via a wildcard). Being able to connect via one IP doesn't mean you can connect via another. — ceejayoz, Oct 01 '19 at 19:43
@ceejayoz sorry, I should've specified that I've set it to wildcard (%). I'll add that. — Wouter Florijn, Oct 01 '19 at 19:46
I don't see a change in your question showing the actual output from the `show grants` command. — John Hanley, Oct 02 '19 at 13:27
did you set up private service access? https://cloud.google.com/sql/docs/mysql/configure-private-ip#configuring_an_instance_to_use_private_ip_at_creation_time — Travis Webb, Oct 02 '19 at 16:28
@TravisWebb yes, and I assume I did it correctly, as the connection to the MySQL instance seems to be working (it's just MySQL denying access). — Wouter Florijn, Oct 04 '19 at 12:51
Right, but MySQL may be denying you access for other reasons, since that MySQL instance is managed by GCP. — Travis Webb, Oct 04 '19 at 16:14
What authentication plugin is MySQL using for this user? Run the following command in mysql: `SELECT user,authentication_string,plugin,host FROM mysql.user;` — John Hanley, Oct 05 '19 at 20:09
I have reported that as a Bug. Google admitted thats a Bug and reported that https://issuetracker.google.com/145704815 Waiting for updates. — eset, Dec 05 '19 at 11:59

score 1 · Answer 1 · answered Oct 09 '19 at 18:37

It appears that enforcing TLS also affects the private IP connection. To me, this wasn't clear from the documentation, and the errors I received don't provide much insight either. Therefore, I tried connecting using the private IP without the client key/certificate, while I did use them for the public IP.

I solved the issue by using the same client certificates for the connection over private IP.

Another thing to note is that disabling TLS enforcement doesn't solve the issue. With my setup, it appears disabling TLS isn't an option, even though the interface says it's disabled. I'm not sure why that's the case.

Same problem, disabled "SSL" option, still can't connect through private IP, but cloud_sql_proxy works. Grrrr. — Erik Živković, Jul 11 '20 at 17:09

Google Cloud MySQL access denied through private IP but allowed through public IP

1 Answers1