2

I am facing below connectivity problem using cloud SQL proxy. I have followed detailed steps from this link https://codelabs.developers.google.com/codelabs/cloud-sql-connectivity-gce-private#0

Connecting SQL using private IP & service account. And I do have service account policy added like this,

gcloud projects add-iam-policy-binding <GCP PROJECT ID> --member serviceAccount:<SERVICE_ACCOUNT_NAME>@<GCP PROJECT ID>.iam.gserviceaccount.com --role roles/cloudsql.client

When I SSH into VM after I did grab cloud_sql_proxy using below,

wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy

and starting,

./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432 &

Results in below error, since the service account is already tied to the VM permission isn't supposed to connect or kick on the SQL connection?

Rlimits for file descriptors set to {&{8500 1048576}}
errors parsing config:
        googleapi: Error 403: Request had insufficient authentication scopes.
More details:
Reason: insufficientPermissions, Message: Insufficient Permission

Any ideas?

Daniel Ocando
  • 3,554
  • 2
  • 11
  • 19
Satscreate
  • 495
  • 12
  • 38

1 Answers1

1
  • If you are running the Proxy from a Compute Engine instance make sure that the service account in use by the instance (generally the Compute Engine default service account) has the correct scopes and permissions assigned (you've already added the correct role, but make sure that this is enabled when creating the instance):

enter image description here

  • If you created a separate service account on your own and added the relevant role with:
gcloud projects add-iam-policy-binding <GCP PROJECT ID> --member serviceAccount:<SERVICE_ACCOUNT_NAME>@<GCP PROJECT ID>.iam.gserviceaccount.com --role roles/cloudsql.client

Make sure that you download the .json file corresponding to your service account to your Compute Engine instance and start the proxy using the following command:

./cloud_sql_proxy -instances=<INSTANCE_CONNECTION_NAME>=tcp:5432 -credential_file=[PATH-TO-CREDENTIALS.JSON-FILE] &

Additionally make sure that all the APIs related to Cloud SQL and Compute Engine are enabled and that you have a firewall rule set in place to allow traffic to the specific ports use by the database (5432 for Postgres).

The following docs can provide you further advise on how to troubleshoot connection issues with the proxy.

Daniel Ocando
  • 3,554
  • 2
  • 11
  • 19
  • 1/2) This is a good answer. However, I would like to suggest the correct method today. Compute Engine Scopes are legacy and were used before IAM. These scopes limit the permissions (roles) assigned to the service account that compute engine is using. It is better to assign the correct roles to the service account and then select "Allow full access to all cloud apis". The reason for the selection of scopes is that in the old days service accounts did not exist neither did IAM. Scopes limited the "total" power that services had. – John Hanley Feb 10 '21 at 09:38
  • 2/2) An even better improvement is to create separate service accounts with IAM roles specific to each compute engine group/app-type. – John Hanley Feb 10 '21 at 09:39
  • Thanks folks, i was creating separate service account for this, and seems working from sqlcmd from VM instance by passing credentials file. But How do i connect this sql instance using SQL Studio from my laptop? any idea – Satscreate Feb 10 '21 at 10:45
  • The tutorial you mention makes reference to connecting to a Cloud SQL instance with PostgreSQL. The public docs provide specific instructions on [how to connect a Cloud SQL SQL Server instance with SQL Server Management Studio](https://cloud.google.com/sql/docs/sqlserver/quickstart#connect-using-ssms). Open a new post if you have any additional questions as this will be a separate question. – Daniel Ocando Feb 10 '21 at 12:16