Connect to Cloud SQL for SQL Server from Cloud Run functions

Connect to Cloud SQL for SQL Server from Cloud Run functions

MySQL   |  PostgreSQL   |  SQL Server

Learn how to deploy a sample app on Cloud Run functions connected to a SQL Server instance by using the Google Cloud console and a client application.

Assuming that you complete all the steps in a timely manner, the resources created in this quickstart typically cost less than one dollar (USD).

Before you begin

Note: The name you use for your project must be between 4 and 30 characters. When you type the name, the form suggests a project ID, which you can edit. The project ID must be between 6 and 30 characters, with a lowercase letter as the first character. You can use a dash, lowercase letter, or digit for the remaining characters, but the last character cannot be a dash.

1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. Make sure that billing is enabled for your Google Cloud project.

4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

5. Make sure that billing is enabled for your Google Cloud project.

6. Enable the Cloud APIs necessary to run a Cloud SQL sample app on Cloud Run functions.

   Console

   Click Enable APIs to enable the APIs required for this quickstart.

   Enable APIs

   This enables the following APIs:

   • Cloud Run functions API
   • Cloud SQL Admin API
   • Cloud Run Admin API
   • Cloud Build API
   • Artifact Registry API
   • Eventarc API
   • Compute Engine API
   • Service Networking API
   • Cloud Logging API

   gcloud

   Click the following button to open Cloud Shell, which provides command-line access to your Google Cloud resources directly from the browser. Cloud Shell can be used to run the gcloud commands presented throughout this quickstart.

   Open Cloud Shell

   Run the following gcloud command using Cloud Shell:

   gcloud services enable cloudfunctions.googleapis.com sqladmin.googleapis.com run.googleapis.com cloudbuild.googleapis.com artifactregistry.googleapis.com eventarc.googleapis.com compute.googleapis.com servicenetworking.googleapis.com pubsub.googleapis.com logging.googleapis.com

   This command enables the following APIs:

   • Cloud Run functions API
   • Cloud SQL Admin API
   • Cloud Run API
   • Cloud Build API
   • Artifact Registry API
   • Eventarc API
   • Compute Engine API
   • Service Networking API
   • Cloud Logging API

7. Add the Cloud Run functions Invoker IAM role to your user.

   Add the role

Set up Cloud SQL

Create a Cloud SQL instance

Public IP

Cloud Run functions does not support connecting to Cloud SQL for SQL Server over public IP. Use private IP instead.

Private IP

Console

Allocate an IP address range and create a private connection to configure private services access for Cloud SQL

1. In the Google Cloud console, go to the VPC networks page.

   Go to VPC networks

2. Select the default VPC network.
3. Select the Private service connection tab.
4. Select the Allocated IP ranges for services tab.
5. Click Allocate IP range.
6. For the Name of the allocated range, specify google-managed-services-default.
7. Select the Automatic option for IP range and specify the prefix length as 16.
8. Click Allocate to create the allocated range.
9. Select the Private connections to services tab for the default VPC network.
10. Click Create connection to create a private connection between your network and a service producer.
11. For the Assigned allocation, select google-managed-services-default.
12. Click Connect to create the connection.

Create an instance with private IP address and SSL enabled

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. Click Create instance.
3. Click Choose SQL Server.
4. Make sure that Enterprise Plus is selected as the Cloud SQL edition for your instance.
5. In the Instance ID field, enter quickstart-instance.
6. In the Password field, enter a password for the sqlserver user. Save this password for future use.
7. In the Choose region and zonal availability section, select Single zone.
8. Click the Show configuration options menu.
9. Expand the Machine configuration node.
10. From the Machine shapes region, select the 4 vCPU, 32 GB shape.
11. Expand the Connections node.
12. Clear the Public IP checkbox to create an instance only with a private IP address.
13. Select the Private IP checkbox.
14. From the Network menu, select default.
15. Click Create instance and then wait for the instance to initialize and start.
16. Click Connections.
17. In the Security section, select Allow only SSL connections to enable SSL connections.
18. In the Allow only SSL connections dialog, click Save and restart and then wait for the instance to restart.

gcloud

Allocate an IP address range and create a private connection to configure private services access for Cloud SQL

1. Run the gcloud compute addresses create command to allocate an IP address range.

   gcloud compute addresses create google-managed-services-default \
   --global --purpose=VPC_PEERING --prefix-length=16 \
   --description="peering range for Google" --network=default

2. Run the gcloud services vpc-peerings connect command to create a private connection to the allocated IP address range. Replace YOUR_PROJECT_ID with your project's project ID.

   gcloud services vpc-peerings connect --service=servicenetworking.googleapis.com \
   --ranges=google-managed-services-default --network=default \
   --project=YOUR_PROJECT_ID

Create an instance with private IP address and SSL enabled

1. Before running the command as follows, replace DB_ROOT_PASSWORD with the password of your database user.

Optionally, modify the values for the following parameters:

• --database-version: The database engine type and version. If left unspecified, the API default is used. See the gcloud database versions documentation to see the current available versions.
• --cpu: The number of cores in the machine.
• --memory: A whole number value indicating how much memory to include in the machine. A size unit can be provided (for example, 3072MB or 9GB). If no units are specified, GB is assumed.
• --region: The regional location of the instance (for example asia-east1, us-east1). If left unspecified, the default us-central1 is used. See the full list of regions.

Run the gcloud sql instances create command to create a Cloud SQL instance with a Private IP address.

gcloud sql instances create quickstart-instance \
--database-version=SQLSERVER_2017_STANDARD \
 --cpu=1 \
 --memory=4GB \
 --region=us-central \
 --root-password=DB_ROOT_PASSWORD \
 --no-assign-ip \
--network=default

2. Run the gcloud sql instances patch command to enable only allow SSL connections for the instance.

gcloud sql instances patch quickstart-instance --require-ssl

Create a database

Console

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. Select quickstart-instance.
3. From the SQL navigation menu, select Databases.
4. Click Create database.
1. In the Database name field of the New database dialog box, enter quickstart-db.
2. Click Create.

gcloud

Run the gcloud sql databases create command to create a database.

gcloud sql databases create quickstart-db --instance=quickstart-instance

Configure a Cloud Run functions service account

Configure the service account used by Cloud Run functions so that it has the Cloud SQL Client role with permissions to connect to Cloud SQL.

Console

1. In the Google Cloud console, go to the IAM page.

   Go to IAM

2. For the service account named Compute Engine default service account, click the pencil icon.
Note: The name of the service account appears in the Name column. This column is next to the identifier of the service account (in the Principal column). The identifier is typically an email address.
3. Click ADD ANOTHER ROLE.
4. Add the Role named Cloud SQL Client.
5. Click Save.

gcloud

1. Run the following gcloud command to get a list of your project's service accounts:

   gcloud iam service-accounts list

2. Copy the EMAIL of the Compute Engine service account.
3. Run the following command to add the Cloud SQL Client role to Compute Engine service account:

   gcloud projects add-iam-policy-binding YOUR_PROJECT_ID \
     --member="serviceAccount:SERVICE_ACCOUNT_EMAIL" \
     --role="roles/cloudsql.client"

Create a function

1. In the Google Cloud console, go to the Cloud Run functions page.

   Go to Cloud Run functions

2. Click Create function.
3. For the Environment, select 2nd gen.
4. In the Function name field, enter quickstart-function.
5. In the Authentication region, select Allow unauthenticated invocations.
6. Click Next.
7. From the Runtime menu, select one of the following languages:

Language	Version number
Go	1.20
Python	3.10

8. In the Source code menu, verify that the Inline editor is selected. In this step, you use the default function provided in the editor.

Deploy the function

1. At the bottom of the Cloud Run functions page, click Deploy. The Google Cloud console redirects you to the function's details page.
2. When the function deployment is complete, click the URL link to see a web browser displaying the message Hello, World!.

Deploy the Cloud SQL sample app as a function

You configure and deploy the Cloud SQL sample app as a function in Cloud Run functions. First, you use Cloud Shell to configure and build the sample app. Then, you use Cloud Run functions to deploy the sample app.

Configure a Cloud SQL sample app

Public IP

Cloud Run functions does not support connecting to Cloud SQL for SQL Server over public IP. Use private IP instead.

Private IP

For private IP paths, your application connects directly to your instance through Serverless VPC Access. This method uses a TCP socket to connect directly to the Cloud SQL instance without using the Cloud SQL Auth Proxy.

Go

Create and download SSL server certificate

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. Click the quickstart-instance to see its Overview page.
3. Click the Connections tab.
4. In the Security section, click Download certificates to download the SSL server certificate.

Create a serverless VPC connection for connections to the instance using private IP

1. In the Google Cloud console, go to the Serverless VPC access - Create connector page.

   Create Serverless VPC connector

2. Enter quickstart-connector for the Name.
3. Select default from the Network drop-down menu.
4. Select Custom IP range from the Subnet drop-down menu.
5. Enter 10.8.0.0 in the IP range input box.
6. Click Create.

Build sample app with SSL server certificate

1. In Cloud Shell Editor, open the sample app's source code.

   Open Cloud Shell Editor
2. In the Open in Cloud Shell dialog box, click Confirm to download the sample app code and open the sample app directory in Cloud Shell Editor.
3. At the Cloud Shell command prompt, in the bottom half of the screen below the Cloud Shell Editor, run the following command to activate your project. Replace YOUR_PROJECT_ID with your project ID.

   gcloud config set project YOUR_PROJECT_ID

4. If an Authorize Cloud Shell dialog box appears, then click Authorize.
5. Upload the SSL server certificate file to the certs folder.
   1. Right-click the certs folder in Cloud Shell Editor and select Upload Files.
   2. Select the following file on your local machine: server-ca.pem.
   3. With the SSL server certificate file selected, click Open to complete the process of uploading the file to Cloud Shell Editor.
6. Run the following gcloud artifacts repositories create command in Cloud Shell to create a repository in the Artifact Registry named quickstart-repo in the same region as your Cloud SQL instance. Replace YOUR_PROJECT_ID with your project ID and YOUR_REGION_NAME with your region name.

   gcloud artifacts repositories create quickstart-repo \
     --project=YOUR_PROJECT_ID \
     --repository-format=docker \
     --location=YOUR_REGION_NAME \
     --description="Cloud Function Quickstart Cloud SQL sample app"

7. Run the gcloud builds submit command as follows in Cloud Shell to build a Docker container and publish it to Artifact Registry. Replace YOUR_PROJECT_ID with your project ID and YOUR_REGION_NAME with your region name.

   gcloud builds submit \
     --tag YOUR_REGION_NAME-docker.pkg.dev/YOUR_PROJECT_ID/quickstart-repo/function-sql .

8. In gen2 of Cloud Run functions, configuring and running the Cloud SQL Auth Proxy automatically isn't supported. This functionality is supported by using Cloud Run. You must revise the underlying Cloud Run service manually to configure and run the Cloud SQL Auth Proxy with the service by using the --add-cloudsql-instances flag. Replace INSTANCE_CONNECTION_NAME with your instance's connection name.

   gcloud run deploy quickstart-function --source . \
     --region YOUR_REGION_NAME \
     --add-cloudsql-instances INSTANCE_CONNECTION_NAME

Python

Create and download SSL server certificate

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. Click the quickstart-instance to see its Overview page.
3. Click the Connections tab.
4. In the Security section, click Download certificates to download the SSL server certificate.

Create a Serverless VPC Connection for connections to the instance using private IP

1. In the Google Cloud console, go to the Serverless VPC access - Create connector page.

   Create Serverless VPC connector

2. Enter quickstart-connector for the Name.
3. Select default from the Network drop-down menu.
4. Select Custom IP range from the Subnet drop-down menu.
5. Enter 10.8.0.0 in the IP range input box.
6. Click Create.

Build sample app with SSL server certificate

1. In Cloud Shell Editor, open the sample app's source code.

   Open Cloud Shell Editor
2. In the Open in Cloud Shell dialog box, click Confirm to download the sample app code and open the sample app directory in Cloud Shell Editor.
3. At the Cloud Shell command prompt, in the bottom half of the screen below the Cloud Shell Editor, run the following command to activate your project. Replace YOUR_PROJECT_ID with your project ID.

   gcloud config set project YOUR_PROJECT_ID

4. Upload SSL server certificate file to the certs folder.
   1. Right-click the certs folder in Cloud Shell Editor and select Upload Files
   2. Select following file on your local machine: server-ca.pem.
   3. With the SSL server certificate file selected, click Open to complete the process of uploading the file to Cloud Shell Editor.
5. Run the following gcloud artifacts repositories create command in Cloud Shell to create a repository in the Artifact Registry named quickstart-repo in the same region as your Cloud SQL instance. Replace YOUR_PROJECT_ID with your project ID and YOUR_REGION_NAME with your region name.

   gcloud artifacts repositories create quickstart-repo \
     --project=YOUR_PROJECT_ID \
     --repository-format=docker \
     --location=YOUR_REGION_NAME \
     --description="Cloud Function Quickstart Cloud SQL sample app"

6. Run the gcloud builds submit command as follows in Cloud Shell to build a Docker container and publish it to Artifact Registry. Replace YOUR_PROJECT_ID with your project ID and YOUR_REGION_NAME with your region name.

   gcloud builds submit \
     --tag YOUR_REGION_NAME-docker.pkg.dev/YOUR_PROJECT_ID/quickstart-repo/function-sql .

7. In gen2 of Cloud Run functions, configuring and running the Cloud SQL Auth Proxy automatically isn't supported. This functionality is supported by using Cloud Run. You must revise the underlying Cloud Run service manually to configure and run the Cloud SQL Auth Proxy with the service by using the --add-cloudsql-instances flag. Replace INSTANCE_CONNECTION_NAME with your instance's connection name.

   gcloud run deploy quickstart-function --source . \
     --region YOUR_REGION_NAME \
     --add-cloudsql-instances INSTANCE_CONNECTION_NAME

Deploy the sample app

The steps to deploy the sample to Cloud Run functions depend on the type of IP address you assigned to your Cloud SQL instance.

Public IP

Cloud Run functions does not support connecting to Cloud SQL for SQL Server over public IP. Use private IP instead.

Private IP

Console

1. In the Google Cloud console, go to the Cloud Run functions page.

   Go to Cloud Run functions

2. Select the quickstart-function that you created in a previous step of this quickstart.
3. In the Function details page, click the link in the Powered by Cloud Run pane to access the underlying Cloud Run functions service.
4. Click Edit & deploy new revision.
5. In the Container image URL field, click Select.
1. Select the Artifact Registry tab, and then expand the quickstart-repo node.
2. Expand the function-sql container, and then select the container image you created in the previous step.
3. Click Select.
6. Create the following environment variables by clicking Add variable in the Environment variables section:
• DB_NAME: Set to quickstart-db.
• DB_USER: Set to sqlserver.
• DB_PASS: Set to the password of the sqlserver user that you created in Create a Cloud SQL instance.
• INSTANCE_CONNECTION_NAME: Set to your instance's Connection name that appears on the Cloud SQL instances page in the Google Cloud console.
• INSTANCE_HOST: Set the Private IP address of your instance displayed on the Cloud SQL instances page in the Google Cloud console.
• DB_ROOT_CERT: Set to certs/server-ca.pem.
• PRIVATE_IP: Set to TRUE.

7. Enable connecting to Cloud SQL:

   1. In the Cloud SQL connections section, click Add connection.
   2. Select your instance's connection name.
   3. Select default: Serverless VPC Access Connector "quickstart-connector" from the VPC Network drop-down menu.
   4. Select the option Route all traffic through the VPC connector.
8. At the bottom of the page, click Deploy.
9. After the quickstart-function is deployed, the Service details page displays a URL to the running service. Click the URL link to see the deployed Cloud Run functions sample app connected to Cloud SQL. The app should be similar to the following:

   

gcloud

Go

1. To deploy a gen2 function to Cloud Run functions, run the following gcloud functions deploy command. Before running the command, update the following variables:
• YOUR_REGION_NAME with your region name
• PRIVATE_IP_ADDRESS with the private IP address of your Cloud SQL instance displayed on the Cloud SQL instances page in the Google Cloud console
• DB_PASS with the password of the quickstart-user that you created in the previous Create a user quickstart step

Run the gcloud functions deploy command as follows to create Cloud Run functions:

gcloud functions deploy quickstart-function \
--gen2 \
--runtime go120 \
--trigger-http \
--allow-unauthenticated \
--entry-point Votes \
--region YOUR_REGION_NAME \
--vpc-connector=quickstart-connector \
--vpc-egress=all-traffic \
--set-env-vars INSTANCE_HOST=PRIVATE_IP_ADDRESS \
--set-env-vars DB_USER=sqlserver \
--set-env-vars DB_PASS=DB_PASS \
--set-env-vars DB_NAME=quickstart-db \
--set-env-vars DB_PORT=1433 \
--set-env-vars DB_ROOT_CERT=certs/server-ca.pem \
--set-env-vars PRIVATE_IP=TRUE

2. When you see a confirmation message that the function has been deployed, click the Service URL link in the message to see the deployed Cloud Run functions sample app connected to Cloud SQL.

   

Python

1. To deploy a gen2 function to Cloud Run functions, run the following gcloud functions deploy command. Before running the command, update the following variables:
• YOUR_REGION_NAME with your region name
• PRIVATE_IP_ADDRESS with the private IP address of your Cloud SQL instance displayed on the Cloud SQL instances page in the Google Cloud console
• DB_PASS with the password of the quickstart-user that you created in the previous Create a user quickstart step

Run the gcloud functions deploy command as follows to create Cloud Run functions:

gcloud functions deploy quickstart-function \
--gen2 \
--runtime python310 \
--trigger-http \
--allow-unauthenticated \
--entry-point votes \
--region YOUR_REGION_NAME \
--vpc-connector=quickstart-connector \
--vpc-egress=all-traffic \
--set-env-vars INSTANCE_HOST=PRIVATE_IP_ADDRESS \
--set-env-vars DB_USER=sqlserver \
--set-env-vars DB_PASS=DB_PASS \
--set-env-vars DB_NAME=quickstart-db \
--set-env-vars DB_PORT=1433 \
--set-env-vars DB_ROOT_CERT=certs/server-ca.pem \
--set-env-vars PRIVATE_IP=TRUE

2. When you see a confirmation message that the function has been deployed, click the Service URL link in the message to see the deployed Cloud Run functions sample app connected to Cloud SQL.

   

Clean up

To avoid incurring charges to your Google Cloud account for the resources used on this page, follow these steps.

1. In the Google Cloud console, go to the Cloud SQL Instances page.

   Go to Cloud SQL Instances

2. Select the quickstart-instance instance to open the Instance details page.
3. In the icon bar at the top of the page, click Delete.
4. In the Delete instance dialog box, type quickstart-instance, and then click Delete to delete the instance.

5. In the Google Cloud console, go to the Cloud Run functions page.

   Go to Cloud Run functions

6. Select the checkbox next to the quickstart-function service name.
7. Click Delete at the top of the Cloud Run functions page.

Optional cleanup steps

If you're not using the Cloud SQL client role that you assigned to the Compute Engine service account, you can remove it.

1. In the Google Cloud console, go to the IAM page.

   Go to IAM

2. Click the edit icon (which looks like a pencil) for the IAM account named Compute Engine default service account.
3. Delete the Cloud SQL client role.
4. Click Save.

If you're not using the APIs that were enabled as part of this quickstart, you can disable them.

• APIs that were enabled within this quickstart:
  • Compute Engine API
  • Cloud SQL Admin API
  • Cloud Run API
  • Container Registry API
  • Cloud Build API

1. In the Google Cloud console, go to the APIs page.

   Go to APIs

2. Select any API that you would like to disable and then click the Disable API button.

What's next

Based on your needs, you can learn more about creating Cloud SQL instances.

You also can learn about creating SQL Server users and databases for your Cloud SQL instance.

For more information about pricing, see Cloud SQL for SQL Server pricing.

Learn more about:

• Configuring your Cloud SQL instance with a public IP address.
• Configuring your Cloud SQL instance with a private IP address.

Additionally, you can learn about connecting to a Cloud SQL instance from other Google Cloud applications:

• From an application running on App Engine standard environment
• From an application running on Compute Engine
• From an application running on GKE
• From Cloud Run functions