Access to Cloud SQL with Jdbc.getCloudSqlConnection()

[Robert Lill]

Hi,

I want to connect to CloudSQL from my Ads Script. I can do this with the generic Jdbc method

Jdbc.getConnection(url, user, password);

but it fails with the recommended method

Jdbc.getCloudSqlConnection(url, user, password);

Failed to establish a database connection. Check connection string, username and password.

I checked them. Several times.

URL for getConnection(): 'jdbc:mysql://123.123.123.123/database'

URL for getCloudSqlConnection(): 'jdbc:google:mysql://project:europe-west1:instance/database'

Disadvantages of the generic method are

- Need to put external IP-Address in connection string.

- Need to whitelist Ads Script server IP in Cloud SQL.

IPs may change and break the functionality of my script.

Has anyone succeeded in connecting to Cloud SQL with getCloudSqlConnection() ? Can you tell me what I need to change?

Thanks

Robert

[Google Ads Scripts Forum Advisor]

Hi Robert,

Thanks for posting your concern.

I understand that you have a connection issue between Google Ads scripts and your database. However, the issue was encountered on the part of JDBC and I am afraid that it is out of my expertise already.

With this, you may try to reach out to the team handling this issue through this support link as they are more equipped to provide support on this issue.

Regards,
Ejay
Google Ads Scripts Team

ref:_00D1U1174p._5001UODtGx:ref

[Robert Lill]

Hi Ejay,

thanks for your your advise. We have reached out for GCP Support to find a solution.

Together we made some progress identifying a possible reason for the behavior.

The documentation of Jdbc.getCloudSqlConnection() [1] describes that to open the connection

it is necessary to set the "https://www.googleapis.com/auth/sqlservice" scope on the script.

The way to specify such a scope on an Apps Script is described in [2].

In Ads Script I don't see a way to modify a "appsscript.json" as documented. It is only

possible to enable a handfull of advanced APIs, but sqlservice is not among them.

Do you have an idea or suggestion how to set the missing scope?

Thank you very much

Robert Lill

[1] https://developers.google.com/apps-script/reference/jdbc/jdbc#getcloudsqlconnectionurl,-username,-password

[2] https://developers.google.com/apps-script/concepts/scopes#setting_explicit_scopes

[Google Ads Scripts Forum Advisor]

Hi Robert,

Thanks for returning back to us.

My apologies also for inconvenience.

So that our team can investigate the issue, could you provide the customer ID and the name the script where you encountered the issue? You can provide the those information via Reply privately to author option.

[Robert Lill]

Information shared via private reply

[Google Ads Scripts Forum Advisor]

Hi Robert,

Thanks for providing the requested information. Let us continue our conversation on the private thread.

[Robert Lill]

Hi community,

thanks to the Goole team's support I could solve my issue. Let me share the steps that led to the solution:

Permissions:

In the GCP project running the Cloud SQL instance the user running the Ads Script must have at least the 'Cloud SQL Client' role.

The Ads Script interpreter scans the Script code for required permissions. This can be enforced by following these steps:

•  Remove the access of the script 

1. First, navigate to accounts.google.com and click on "Security" (one of the items in navigation bar on the left-hand side).
2. Second, scroll down to "Third-party apps with account access" and select "Manage third-party access", then find the "Export AdWords Test Robert" script.
3. Click "Remove Access" and confirm

• Previewing new instance of the script 

1. delete all JDBC references(besides Jdbc.getCloudSqlConnection). After that, preview the script and then authorize it. Do not try to authorize before previewing here. There is a possibility you are requesting the wrong set of permissions by doing this.
   

The Authentication dialog should list an item "Manage the data in your Google SQL Service instances".

Hope this helps!

Robert