Connecting SQL Server client using the Cloud SQL Auth Proxy Docker image

MySQL   |  PostgreSQL   |  SQL Server

This page describes how to connect a sqlcmd client to your Cloud SQL instance, from a client machine running Linux or a Compute Engine Linux instance, using the Cloud SQL Auth Proxy Docker image.

Before you begin

You must have:

• Installed the gcloud CLI. Learn more.
• Authorized the gcloud CLI. Learn more.
• Set the default project for the gcloud CLI. Learn more.
• Configured a database user on your Cloud SQL instance. Learn more.

Required roles

To get the permissions that you need to connect to a Cloud SQL instance using the Cloud SQL Auth Proxy Docker image, ask your administrator to grant you the following IAM roles on your project:

• To connect using the Cloud SQL Auth Proxy: Cloud SQL Client (roles/cloudsql.client)
• If you use IAM database authentication: Cloud SQL Instance User (roles/cloudsql.instanceUser)

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Connect a sqlcmd client using the Cloud SQL Auth Proxy Docker image

To connect using the Cloud SQL Auth Proxy Docker image:

1. Enable the Cloud SQL Admin API, if it is not already enabled.

   Roles required to enable APIs

   To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

   Enable the API

2. If you are using a Compute Engine instance, prepare the instance:
   1. Display the Compute Engine instance properties:

      gcloud compute instances describe [GCE_INSTANCE_NAME]

   2. Verify the service account and access scopes configured on the instance:
      • Ensure that the service account attached to the VM has the Cloud SQL Client (roles/cloudsql.client) predefined Identity and Access Management (IAM) role to connect using the Cloud SQL Auth Proxy or the Cloud SQL Admin API. If you use IAM database authentication, also grant the Cloud SQL Instance User (roles/cloudsql.instanceUser) role. For more information about predefined roles, see Required roles.
      • Ensure that the VM access scope is set to cloud-platform (https://www.googleapis.com/auth/cloud-platform, or Allow full access to all Cloud APIs in the Google Cloud console). Access scopes are a legacy method for specifying authorization for a VM and only set the maximum OAuth authorization scope for requests. Manage access control through predefined IAM roles instead of access scopes.

      If your Compute Engine instance doesn't have the required service account roles or access scope, you can update the instance. For more information about changing the service account and access scopes on a VM, see Change the attached service account in the Compute Engine documentation.

   3. Open a terminal connection to the instance, using the instructions at Connecting to Linux instances.
3. Install the sqlcmd client on the Compute Engine instance or client machine, if it is not already installed.

   Debian/Ubuntu

   For Debian/Ubuntu, install the applicable SQL Server command-line tools.

   CentOS/RHEL

   For CentOS/RHEL, install the applicable SQL Server command-line tools.

   openSUSE

   For openSUSE, install the applicable SQL Server command-line tools.

   Other platforms

   See the landing page for installing SQL Server, as well as the SQL Server downloads page.

4. If needed, install the Docker client:

   curl https://get.docker.com | sh
   sudo usermod -aG docker $USER

   If you are using a container-optimized Compute Engine instance, it already has the Docker client installed.

5. Install the Cloud SQL Auth Proxy Docker image from the Google Container Registry.

   docker pull gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.27.0

6. If you are running the Cloud SQL Auth Proxy Docker image on a local machine (not a Compute Engine instance), or if the service account attached to your Compute Engine instance doesn't have the required IAM roles and access scope, create a Google Cloud service account.
   Note: If you are connecting from a Compute Engine VM, we recommend that you use the service account attached to the VM instead of a service account key file:

   • Grant the attached service account the Cloud SQL Client (roles/cloudsql.client) predefined IAM role to connect using the Cloud SQL Auth Proxy or the Cloud SQL Admin API, and the Cloud SQL Instance User (roles/cloudsql.instanceUser) role if you use IAM database authentication. For more information about predefined roles, see Cloud SQL IAM roles.
   • Set the VM access scope to cloud-platform (https://www.googleapis.com/auth/cloud-platform, or Allow full access to all Cloud APIs in the Google Cloud console) and use IAM roles to control permissions. Access scopes are a legacy method for specifying authorization for a VM and only set the maximum OAuth authorization scope. To update an existing VM, see Change the attached service account.
   1. In the Google Cloud console, go to the Service accounts page.

      Go to Service accounts

   2. Select the project that contains your Cloud SQL instance.
   3. Click Create service account.
   4. In the Service account name field, enter a descriptive name for the service account.
   5. Change the Service account ID to a unique, recognizable value and then click Create and continue.
   6. Click the Select a role field and select one of the following roles:
      • Cloud SQL > Cloud SQL Client
      • Cloud SQL > Cloud SQL Editor
      • Cloud SQL > Cloud SQL Admin
      Note: To create a service account with the required permissions, you must have the resourcemanager.projects.setIamPolicy permission. This permission is included in the Project Owner, Project IAM Admin, and Organization Administrator roles.
      You must also have enabled the Cloud SQL Admin API. If you are using the legacy project roles (Viewer, Editor, Owner), the service account must have at least the Editor role.
   7. Click Done to finish creating the service account.
   8. Click the action menu for your new service account and then select Manage keys.
   9. Click the Add key drop-down menu and then click Create new key.
   10. Confirm that the key type is JSON and then click Create.

       The private key file is downloaded to your machine. You can move it to another location. Keep the key file secure.

   You provide the path to the key file as "PATH_TO_KEY_FILE" when you start the Cloud SQL Auth Proxy.

7. Go to the Cloud SQL Instances page in the Google Cloud console.

   Go to Cloud SQL Instances

8. Select the instance to open its Instance details page and copy the Instance connection name.

   For example: myproject:us-central1:myinstance.

9. Start the Cloud SQL Auth Proxy.

   TCP sockets

   docker run -d 
   
     -v PATH_TO_KEY_FILE:/path/to/service-account-key.json 
   
     -p 127.0.0.1:1433:1433 
   
     gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.27.0 
   
     --address 0.0.0.0 --port 1433 
   
     --credentials-file /path/to/service-account-key.json INSTANCE_CONNECTION_NAME

   If you're using the credentials provided by your Compute Engine instance, don't include the --credentials-file parameter and the -v PATH_TO_KEY_FILE:/path/to/service-account-key.json line.

   Always specify the 127.0.0.1 prefix in -p so that the Cloud SQL Auth Proxy isn't exposed outside the local host. The --address 0.0.0.0 flag is required to make the port accessible from outside of the Docker container.

10. Start the client:

    The connection string you use depends on whether you started the Cloud SQL Auth Proxy using a TCP socket or Docker.

    TCP sockets

    1. Start the sqlcmd client:

       sqlcmd -S tcp:127.0.0.1,1433 -U USERNAME -P PASSWORD

       When you connect using TCP sockets, the Cloud SQL Auth Proxy is accessed through 127.0.0.1.

    2. If prompted, enter the password.
    3. The sqlcmd prompt appears.

Need help? For help troubleshooting the proxy, see Troubleshooting Cloud SQL Auth Proxy connections, or see our Cloud SQL Support page.

Keep the Cloud SQL Auth Proxy Docker image up to date

The Cloud SQL Auth Proxy Docker image is based on a specific version of the Cloud SQL Auth Proxy. When a new version of the Cloud SQL Auth Proxy becomes available, pull the new version of the Cloud SQL Auth Proxy Docker image to keep your environment up to date. You can see the current version of the Cloud SQL Auth Proxy by checking the Cloud SQL Auth Proxy GitHub releases page.

What's next

• Learn more about the Cloud SQL Auth Proxy.
• Get help troubleshooting connection issues for the Cloud SQL Auth Proxy.
• Create users and databases.
• Learn about options for connecting to your instance from your application.
• Learn about Docker.
• Learn about the Google Container Registry.
• Learn about options for support.