Cannot Connect by Cloud SQL Proxy from Cloud Shell By Proxy

8
votes

I am following the Django sample for GAE and have problem to connect to Cloud SQL instance by Proxy from Google Cloud Shell. Possibly related to permission setting since I see the request not authorized,

Other context,

  1. "gcloud beta sql connect auth-instance --user=root" has no problem to connect.

  2. I have a service account for SQL Proxy Client.

I possibly miss something. Could someone please shed some light? Thanks in advance.

Thanks in advance.

Proxy log:

./cloud_sql_proxy -instances=auth-158903:asia-east1:auth-instance=tcp:3306

2017/02/17 14:00:59 Listening on 127.0.0.1:3306 for auth-158903:asia-east1:auth-instance
2017/02/17 14:00:59 Ready for new connections
2017/02/17 14:01:07 New connection for "auth-158903:asia-east1:auth-instance"
2017/02/17 14:03:16 couldn't connect to "auth-158903:asia-east1:auth-instance": dial tcp 107.167.191.26:3307: getsockopt: connection timed out

Client Log:

mysql -u root -p --host 127.0.0.1
Enter password: 
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I also try with credential file but still no luck,

./cloud_sql_proxy -instances=auth-158903:asia-east1:auth-instance=tcp:3306 -credential_file=Auth-2eede8ae0d0b.jason

2017/02/17 14:21:36 using credential file for authentication; [email protected]
2017/02/17 14:21:36 Listening on 127.0.0.1:3306 for auth-158903:asia-east1:auth-instance
2017/02/17 14:21:36 Ready for new connections
2017/02/17 14:21:46 New connection for "auth-158903:asia-east1:auth-instance"
2017/02/17 14:21:48 couldn't connect to "auth-158903:asia-east1:auth-instance": ensure that the account has access to "auth-158903:asia-east1:auth-instance" (and make sure there's no typo in that name). Error during get instance auth-158903:asia-east1:auth-instance: googleapi:     **Error 403: The client is not authorized to make this request., notAuthorized**
3
google-cloud-platformgoogle-cloud-sql

3 Answers

3
votes

The Cloud SQL proxy uses port 3307 instead of the more usual MySQL port 3306. This is because it uses TLS in a different way and has different IP ACLs. As a consequence, firewalls that allow MySQL traffic won't allow Cloud SQL proxy by default.

Take a look and see if you have a firewall on your network that blocks port 3307. To use Cloud SQL proxy, authorize this port for outbound connections.

2
votes

I can reproduce this issue exactly if I only give my service account "Cloud SQL Client" IAM role. When I give my service account the "Cloud SQL Viewer" role as well, it can then connect. I suggest you try this and see if it helps.

1
votes

It looks like a network connectivity issue. Read this carefully if you use a private IP : https://cloud.google.com/sql/docs/mysql/private-ip

Note that the Cloud SQL instance is in a Google managed network and the proxy is meant to be used to simplify connections to the DB within the VPC network.

In short: running cloud-sql-proxy from a local machine will not work, because it's not in the VPC network. It should work from a Compute Engine VM that is connected to the same VPC as the DB.

What I usually do as a workaround is use gcloud ssh from a local machine and port forward over a small VM in compute engine, like:

gcloud beta compute ssh --zone "europe-north1-b" "instance-1" --project "my-project" -- -L 3306:cloud_sql_server_ip:3306

Then you can connect to localhost:3306 (make sure nothing else is running or change first port number to one that is free locally)