Cannot Connect by Cloud SQL Proxy from Cloud Shell By Proxy

Question

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**

Genteel

Answer 1

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.

Lipstick

This is not the answer, there's no need for a firewall rule. I have it working in a separate project with zero related firewall rules but in our new project it doesn't work at all. There are no private IPs being used. – Grainy

Answer 2

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.

Lipstick

Thanks for suggestion. API did get through but Istill cannot connect to Cloud SQL. I added all permissions in Cloud SQL to the service account. The API dashboard shows requests and no error for Google Cloud SQL API. For some reason, still no luck to connect to cloud sql via Proxy. I am using cloud shell. Add all network (0.0.0.0/0) and the cloud shell machine (gcloud beta sql connect) to authorized network. Still cannot connect. The error message from proxy is, "getsockopt: connection timed out" – Genteel

Answer 3

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)

Unionist

Then what is the point of cloudsql proxy? – X

That was my thought initially too, the answer is in the docs, stating it is meant to proxy from within the VPC network. I agree the name proxy is a bit misleading and made me assume that I could use it to connect directly from a local machine. A 'simple' port forward suffices though. – Unionist

I still don't understand the point of the cloudsql proxy though. Once you're within the vpc, you can just access the database using the private ip, right? Why would you need to proxy from within your vpc? – X

perhaps if you have multiple? not sure to be honest. But why not ask at Google ? go to docs and use feedback / create ticket. I found no use for it personally, perhaps also from within cluster, I just stopped looking and used port forward over ssh to connect a local DB client personally. – Unionist

As mentioned, the cloud sql proxy allows you to create a tunnel from the cloud sql instance to a vm on the vpc network. From there you can create an iap tunnel from your local machine to the proxy running on the vm. This allows you to, for example, access a private cloud sql instance using a locally running gui based sql editor such as pgAdmin – Gabrielegabriell