Getting notAuthorized error with cloud_sql_proxy locally

A

8

15

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

Aurelea answered 4/4, 2016 at 18:46 Comment(0)

B

4

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:

Botulinus answered 4/4, 2016 at 20:32 Comment(3)

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. – Aurelea 4/4, 2016 at 23:20

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). – Botulinus 4/4, 2016 at 23:43

Incredible. Thanks for the suggestion, it really was a bad name after all the staring. – Aurelea 4/4, 2016 at 23:56

W

12

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:

Go to the Cloud Project IAM page (Left Menu > IAM & Admin > IAM)

Look for the row with the robot account that is having the issue.

It should say "Cloud SQL Client" on the role column.

Click on it to open the dropdown

Click on the 'Cloud SQL Client' role that is checked to uncheck it.

Click the blue save button above.

Verify that the service account is removed from the IAM page.

Click the Add button above in the same page.

Enter the service account email address and select the Cloud SQL Client role

Click Add

The service account should appear again in the the list.

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

Wifeless answered 30/4, 2018 at 23:22 Comment(1)

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." – Ordinarily 18/10, 2018 at 8:56

F

5

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

Fictive answered 21/9, 2016 at 22:1 Comment(1)

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. – Linkwork 29/12, 2016 at 2:47

B

4

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:

Botulinus answered 4/4, 2016 at 20:32 Comment(3)

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. – Aurelea 4/4, 2016 at 23:20

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). – Botulinus 4/4, 2016 at 23:43

Incredible. Thanks for the suggestion, it really was a bad name after all the staring. – Aurelea 4/4, 2016 at 23:56

I

3

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.

Impartial answered 9/6, 2017 at 10:55 Comment(0)

L

2

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

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

$gcloud config set project my-project
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

Liege answered 31/5, 2018 at 22:10 Comment(0)

F

2

Another take is that if you are using the -dir flag means you will be connecting through a unix-socket which for MySQL is an extra connection parameter and the =tcp:3306 becomes redundant, when connecting though 127.0.0.1 this wouldn't work because it is TCP and it is waiting connection on the socket.

Feretory answered 16/6, 2020 at 14:25 Comment(0)

M

0

In my case, I have set up a different project. set the correct project and check.

gcloud init

./cloud_sql_proxy -instances=<project-name>:<location>:<dbname>=tcp:1433

Maretz answered 16/9, 2022 at 6:31 Comment(0)

P

0

"Not Authorized" clearly means "The user you are using to connect to Cloud SQL with has no GCP cloud SQL client role" as per the documentation here: https://cloud.google.com/sql/docs/mysql/connect-auth-proxy#service-account Also make sure you have enabled "Cloud SQL API". Once you add that role to your service account, make sure to run the command to authenticate again before proceeding to connecting with your Database.

gcloud auth activate-service-account sa-service-acct@<ProjectName>.iam.gserviceaccount.com --key-file=<Location/to/your/sa-service-acct.json> --project=<ProjectName>

where sa-service-acct is an example of your service acct.

Physicochemical answered 25/1, 2023 at 5:42 Comment(0)

Recommended topics

Hot tags