Getting notAuthorized error with cloud_sql_proxy locally

Question

I'm trying to setup a connection locally for 2nd generation cloud sql instance.

Call for the proxy is

./cloud_sql_proxy -dir=/cloudsql -instances=status-1268:us-central1:status-dev=tcp:3306 & mysql -u status_stg --host 127.0.0.1

I am a bit unclear on how to set up correctly the service accounts from the API section, so I tried with both of the defaults, App engine default service account and Compute engine default service account. I also created a new one service account. For each one of them I assigned the service account ID to be Editor and Owner from the manage permissions section. Perhaps there is another way to enable Cloud SQL Admin API for these accounts?

Output from proxy when my service tries to talk to DB:

./cloud_sql_proxy -dir=/cloudsql -instances=status-1268:us-central1:status-   dev=tcp:3306 & mysql -u status_stg --host 127.0.0.1
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)
2016/04/04 11:26:58 Open socket for "status-1268:us-central1:status-dev" at     "127.0.0.1:3306"
2016/04/04 11:26:58 Socket prefix: /cloudsql
2016/04/04 11:27:10 Got a connection for "status-1268:us-central1:status-dev"
2016/04/04 11:27:11 couldn't connect to "status-1268:us-central1:status-dev": googleapi: Error 403: The client is not authorized to make this request., notAuthorized
2016/04/04 11:29:08 Got a connection for "status-1268:us-central1:status-dev"
2016/04/04 11:29:08 couldn't connect to "status-1268:us-central1:status-dev": googleapi: Error 403: The client is not authorized to make this request., notAuthorized

Thoughts on what to look for? I think I am missing something basic here configuring the service accounts.

(Overall, for my project I am running a node.js express app and hoping to connect it to 2nd gend cloud sql DB instance without having to expose it with 0.0.0.0 as I currently have working)

Thanks

Solution 1

Please double check the connection string. It sounds like your credentials are in order, so it's likely you have an error in the instance name.

I recommend copying & pasting the "Instance connection name" from the Cloud Console page for the instance:

Solution 2

TL;DR: If your error log contains Error during createEphemeral.., please read on.

Solution that works for me is from Github Cloud-SQL-Proxy Issue Tracker.

It seems that you may have encountered a bug in backend that is triggered when there is an old service account with the same name. Follow the steps below to solve your issue:

1. Go to the Cloud Project IAM page (Left Menu > IAM & Admin > IAM)
2. Look for the row with the robot account that is having the issue.
3. It should say "Cloud SQL Client" on the role column.
4. Click on it to open the dropdown
5. Click on the 'Cloud SQL Client' role that is checked to uncheck it.
6. Click the blue save button above.
7. Verify that the service account is removed from the IAM page.
8. Click the Add button above in the same page.
9. Enter the service account email address and select the Cloud SQL Client role
10. Click Add
11. The service account should appear again in the the list.

Credits to Chees, but points are mine :P. Hope it solves your issue.

Solution 3

In my case the problem was that the service account didn't have the correct role. You should at least set it to Editor.

Solution 4

There are many reasons for such error.

following is solutions:

1) Copy instance name from sql instance 'overview' tab.

2) You may not have access of cloud sql client.

here is more detail.

Solution 5

For my case, there are two mistakes that I did:

1. Make sure you use your correct project for the cloud sql instance.

   $gcloud config set project my-project

2. Use the correct connectionName. If you're like me following google documentation, you will possibly end up put square brackets in your connectionName. Remove them.

Myname-MBP:django setoelkahfi$ ./cloud_sql_proxy -instances="[blitzkrig-9f158:us-central1:polls-instance]"=tcp:3306 2018/06/01 04:55:48 Listening on 127.0.0.1:3306 for [blitzkrig-9f158-9f158:us-central1:polls-instance] 2018/06/01 04:55:48 Ready for new connections 2018/06/01 04:56:48 New connection for "[blitzkrig-9f158-9f158:us-central1:polls-instance]" 2018/06/01 04:56:50 couldn't connect to "[blitzkrig-9f158-9f158:us-central1:olls-instance]": ensure that the account has access to "[blitzkrig-9f158-9f158:us-central1:polls-instance]" (and make sure there's no typo in that name). Error during createEphemeral for [blitzkrig-9f158-9f158:us-central1:polls-instance]: googleapi: Error 403: The client is not authorized to make this request., notAuthorized

View more solutions

Comments

[georges]

I have that already in place by setting the GOOGLE_APPLICATION_CREDENTIALS environment variable. If that wasn't working there is a different error starting up the proxy prompting for the -credential_file flag.

[Vadim]

Ah, i see, sorry that wasn't clear from the original post. I've updated my answer -- please double check the instance name (including the project ID).

[georges]

Incredible. Thanks for the suggestion, it really was a bad name after all the staring.

[tmarthal]

This was my problem as well, with the error message outlined in the question. The default service account does not have editor privileges required to launch the proxy properly.

[Stepan Vrany]

Thank you very much! It was exactly my case. I've created SA a few days ago, this SA was removed and I've created SA with the same name. So the solution is just remove "new" SA from IAM and add it again with "Cloud SQL client."