Google App Engine .Net Core 2.0 app can't access Google Cloud SQL database

Question

I have a dotnet core 2.0 application running in Google App Engine Flexible Environment. Within the same Google project I have a Cloud SQL - MySQL database. On the Cloud SQL Instance details page, under the Authorizations tab, it states

Apps in this project: All authorized.

However, I cannot access the database from my application unless I add the 0.0.0.0/0 route to the Authorized networks section.

What can I do to give my application db access without opening my database to the world?

---

Update 2018-05-21 from Jeffery Rennie (accepted answer)

App Engine now supports connecting to a Cloud SQL instance using a port number instead of a unix domain socket. So now, you can add something like this to your app.yaml:

beta_settings:
    cloud_sql_instances: "your-project-id:us-central1:instance-name=tcp:5432"

And specify Host=cloudsql in your connection string in your appsettings.json:

"ConnectionString": "Uid=aspnetuser;Pwd=;Host=cloudsql;Database=visitors"

In the sample above, the port is 5432, which is the default port for a PostgreSQL database. For a MySQL database, use port 3306.

A full example with instructions for deploying to App Engine can be found here:

https://github.com/GoogleCloudPlatform/dotnet-docs-samples/tree/master/appengine/flexible/CloudSql

Jahveh

Answer 1

The ideal solution is to use a unix domain socket to connect from your app engine instance to Cloud SQL. That's how other programming languages like Python and PHP do it. Unfortunately, the MySQL connector does not work with domain sockets. I see no reason why it can't, but it doesn't. I hope they fix that issue soon.

As described in https://cloud.google.com/appengine/kb/#static-ip,

Note that using static IP address filtering is not considered a safe and effective means of protection. For example, an attacker could set up a malicious App Engine app which could share the same IP address range as your application. Instead, we suggest that you take a defense in depth approach using OAuth and Certs.

If certificates are not sufficient to protect your application, then the only remaining option I see today is to build a custom runtime that runs the Cloud SQL Proxy. The proxy can forward a local ip port number to a unix domain socket. If you have built a docker image or two, then it's not too bad.

I will update this answer as the situation improves.

---

Update 2018-05-21

App Engine now supports connecting to a Cloud SQL instance using a port number instead of a unix domain socket. So now, you can add something like this to your app.yaml:

beta_settings:
    cloud_sql_instances: "your-project-id:us-central1:instance-name=tcp:5432"

And specify Host=cloudsql in your connection string in your appsettings.json:

"ConnectionString": "Uid=aspnetuser;Pwd=;Host=cloudsql;Database=visitors"

In the sample above, the port is 5432, which is the default port for a PostgreSQL database. For a MySQL database, use port 3306.

A full example with instructions for deploying to App Engine can be found here:

https://github.com/GoogleCloudPlatform/dotnet-docs-samples/tree/master/appengine/flexible/CloudSql

Refit

Thank you for your response. I was expecting to just have a configuration wrong or something similar. I will try to apply some of this and see what I can get working. I'll respond back when I can. Again, thanks for your help. – Jahveh

Okay, after much reading I feel pretty comfortable with what we have to do. For now we'll rely on the certificates. I don't think we'll have to do the custom runtime and proxy. Thank you for your help. – Jahveh

Stumbled on this exact problem. Is there any update lately? @dshrout – Gonidium

Unfortunately, no. The solution described in the accepted answer is the way to handle it. We are now looking at Azure. It is far simpler to use and is very comparable cost wise. – Jahveh

Lafexlos, there are engineers at Google working to improve this experience. I'm actively tracking and nudging the issue. – Refit

Answer 2

While you are not wrong that "apps in this this project: All authorized" seems to suggest you can out-of-the-box just use your App Engine app with Cloud SQL, but there are limitations.

First of all, your Cloud SQL needs to be a 2nd generation instance, and secondly, there are specific instructions that's dependent on the language you use and the App Engine type (standard or flex).

If your situation fit all the requirements, it should work.

For your specific use case, you need the .Net instructions, it does say you need to add a network with 0.0.0.0/0 access and an user account. The user authentication + SSL should provide the security you need.

Larrylars

Hi.! We are using 2nd generation cloud SQL and .net core 2 with flexible environment. As far as I understand from the documentation, with these settings, it should be authorized. We are using entity framework core 2 as ORM though. Do you have any idea if this might be the problem? – Gonidium

The .Net instructions requires a network with all IP access. It probably has something to do with how .Net works. Please read through it and you will see how we handle the security aspect of that setup. – Larrylars

Well, instead of allowing from "any" IP at least limit it to only allow from the app engine ip range list, which can be found by iterating on the following spf record _cloud-netblocks.googleusercontent.com See cloud.google.com/appengine/kb/#static-ip for instructions. – Sterol