Jdbc connection error from Google Apps Script

Question

I have created a Google Cloud Project MySQL database to use in conjunction with the Jdbc service provided by Google Apps Script. Everything went as planned with the connection. I am basically connecting as it does in the docs.

var conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);

I shared the file with another account and all of a sudden I am seeing a red error saying:

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

Nothing changed in the code, but there is an error. When I go back to my original account and run the same bit of code, there is no error. What is happening here? Any ideas?

Incriminate

Can you check the permissions on the MySQL database to see if the credentials you're using are restricted to a particular host (IP)? – Extreme

@GordThompson I am super unfamiliar with the console, so forgive me. My user account has 'Host name: % (any host)' – Incriminate

In the docs, it also suggests adding ip ranges if you are connecting from a different provider developers.google.com/apps-script/guides/… I added all of these just in case but still get that error. I don't think I even need them bc it is a Google Cloud project. – Incriminate

Based from this link, there is a known bug which causes problems with jdbc connections using hostnames. Try using an IP address instead. – Allow

Answer 1

I'd double-check once again all IP ranges which should be whitelisted. According to your description it worked fine in first account, probably in second account Apps Script uses another IP for connection, which was not whitelisted or whitelisted with some typo. Could you share screenshot how did you exactly whitelist the ranges from this article?

Enmesh

I am marking this as correct, however, it doesn't solve the problem of getCloudSqlConnection(). The IP addresses only work with the getConnection method. – Incriminate

Ok, maybe you have more than one script file .gs in your project with the same function name and different arguments? – Enmesh

Answer 2

Jdbc.getConnection works from both: my account and another account:

var conn = Jdbc.getConnection('jdbc:mysql://' + IP + ':3306/' + database_name, user, password)

I'm really confused because the recommended method did not work.

There are two ways of establishing a connection with a Google Cloud SQL database using Apps Script's JDBC service:

• (Recommended) Connecting using Jdbc.getCloudSqlConnection(url)
• Connecting using Jdbc.getConnection(url)

Notes:

• IP is a Public IP address from the OVERVIEW tab in your database console:
• I've allowed any host when created a user:

Qualitative

Thanks @Max good to know that another user is also encountering this. It took ages to try the many options suggested by documentation; finally your format above solved it for me. Keen to understand why Recommended version does not seem to work. – Marguritemargy

If you follow this suggestion remember to whitelist the App Scripts IP addresses listed here: developers.google.com/apps-script/guides/… – Lorenzoloresz

Answer 3

I think this is a permission issue in your second account. Necessary information are missing in your question. But, the secound account, if run as a another user, won't necessarily have your sqlservice authorization. The permission,

https://www.googleapis.com/auth/sqlservice

Manage the data in your Google SQL Service instances

is required to use Jdbc.getCloudSqlConnection(url), while Jdbc#getConnectionUrl() just requires external link connection permission

https://www.googleapis.com/auth/script.external_request

I believe that you can only connect to sql instances owned by you with getCloudSqlConnection() which doesn't even require external connection permission. This method probably calls your sql instance internally.

References:

• Jdbc#getCloudConnection

• Jdbc#getConnection

---

Conclusion

To connect to any external service, you need external_request permission. But, You don't need that permission to connect to your own documents say, Spreadsheets owned by you/have edit access permission - through SpreadsheetApp.openByUrl(). I believe it's the same thing with Jdbc.getCloudSqlConnection(). It calls your Google sql internally - So, even if you grant external request permission, It won't work. What will work for this method is

1. Installable triggers (which runs as you).

2. Add the second account also as owner in GCP-IAM (may not work though) See this answer

Aulic

Thank you! Could you share the link with text: "Manage the data in your Google SQL Service instances"? – Qualitative

@Max Click here to see. I couldn't find it documented in any of the auth scopes documentation(closest was sqlservice.admin doc). So, I just created a new project with getCloudsql()> Try to run it > Google will ask for this permission without external request permission. The link above is however, a playground link. – Aulic

Could it have something to do with the scopes you mentioned not being requested from the user? It has been a while since I had this problem and I never clearly understood what I was doing wrong or how my work-around fixed the problem. – Incriminate

Also, I feel like I should change the accepted answer to this, however, I will wait to see what @MaxMakhrov says. – Incriminate

@Jordan To connect to any external service, you need external_request permission. But, You don't need that permission to connect to your own documents say, Spreadsheets owned by you/have edit access permission - through SpreadsheetApp.openByUrl. I believe it's the same thing with Jdbc.getCloudSqlConnection(). It calls your Google sql internally - So, even if you grant external request permission, It won't work. What will work for this method is1.Installable triggers (which runs as you). One more thing I'll try is: 2. Add the second account also as owner in GCP-IAM(may not work though). – Aulic

@TheMaster, thank you too! My task was to add users from any given account and give them an ability to write data into DB. Adding a new owner would be a good experiment, but it is not my practical case. So I'll leave this option to others. – Qualitative

@Max I see. Thanks again! – Aulic

Answer 4

I am not sure whether this question has been resolved or not, but let me add this answer.

I also faced the same problem but I found the resolution. What I did is:

First, go to the console.

https://console.cloud.google.com

Then, open IAM. and add the account as a member and add this permission: "Cloud SQL Client".

Canard

Just had the same issue and by experimentation it seems that Herbert's answer is correct with respect to getCloudSqlConnection(): I can't find it documented anywhere, but it appears that getCloudSqlConnection() passes the active user's google account and requires that user to have SQL-related permission in the IAM console of the Cloud Project. Users without permission get the "Failed to establish..." error. – Monmouthshire

This does not work in my case. I have the "cloud SQL client" permission for the member but the Apps Script run by this member (it's actually me all the time) still get back the error. Something else must be missing – Footpoundsecond

Answer 5

I have a GAS Add-On that uses a Google cloud dB. I initially set this up by:

1. Whitelisting Google Cloud IP ranges in my SQL instance
2. Getting the script.external_request scope approved for OAuth Consent screen

This all works great from GAS for the add-on, but I suspect that if this whitelist is not comprehensive and volatile (which I expect it is), I will see intermittent connectivity issues.

I recently added a Firebase web app that needs access to the same dB. I had issues, because Firebase does not conform to those Google IP ranges and does not expose its IP for whitelisting. So I had to create a socket layer connection as if Firebase was an external service.

Which got me thinking, should I put a socket layer in my GAS Add-On? But nothing in the GAS JBDC Class documentation indicates a socket parameter.

Which leads me to a question that was not really answered in this thread:

Does anyone know why Jdbc.getCloudSqlConnection(url) is the "Recommended" approach? The documentation seems to imply that because the IP whitelisting is not required, Jdbc.getCloudSqlConnection(url) is using a socket (or some other secure method) to connect to the dB?

It also seems silly that if that is the case, that I would need two have two sensitive scopes to manage a dB connection. I would rather not go through another OAuth const audit and require my users to accept another scope unless there is a benefit to doing so.

Saransarangi