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 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.

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.

   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 scopes enabled on the instance.

      Authenticating using scopes requires both of the following scopes:

      • https://www.googleapis.com/auth/sqlservice.admin
      • https://www.googleapis.com/auth/devstorage.read_write

      Alternatively, the https://www.googleapis.com/auth/cloud-platform scope enables all Google Cloud Platform APIs.

      If your Compute Engine instance does not have the proper scopes, you can update the instance to include them. For more information, see 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.14.1

6. If you are running the Cloud SQL Auth Proxy Docker image on a local machine (not a Compute Engine instance), or your Compute Engine instance does not have the proper scopes, create a Google Cloud Platform service account.
   If you are connecting from Compute Engine, make sure your VM has the proper scope to connect using the Cloud SQL Admin API.

   Configure the service account to have either of the following access scopes:

   • https://www.googleapis.com/auth/sqlservice.admin
   • https://www.googleapis.com/auth/cloud-platform
   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 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.

   Depending on your language and environment, you can start the Cloud SQL Auth Proxy using either TCP sockets or Unix sockets. Unix sockets are not supported for applications written in the Java programming language or for the Windows environment.

   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.14.1 \
     --credentials-file /path/to/service-account-key.json <INSTANCE_CONNECTION_NAME>

   If you are using the credentials provided by your Compute Engine instance, do not include the --credentials-file parameter and the -v <PATH_TO_KEY_FILE>:/path/to/service-account-key.json line.

   Always specify 127.0.0.1 prefix in -p so that the Cloud SQL Auth Proxy is not exposed outside the local host. The "0.0.0.0" in the instances parameter is required to make the port accessible from outside of the Docker container.

   Unix sockets

   docker run -d -v /cloudsql:/cloudsql \
     -v <PATH_TO_KEY_FILE>:/path/to/service-account-key.json \
     gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.14.1 //
     --unix-socket=/cloudsql \
     --credentials-file /path/to/service-account-key.json <INSTANCE_CONNECTION_NAME> 

   If you are using the credentials provided by your Compute Engine instance, do not include the --credentials-file parameter and the -v <PATH_TO_KEY_FILE>:/path/to/service-account-key.json line.

   If you are using a container optimized image, use a writeable directory in place of /cloudsql, for example:

   -v /mnt/stateful_partition/cloudsql:/cloudsql

   You can specify more than one instance, separated by commas. You can also use Compute Engine metadata to dynamically determine the instances to connect to. Learn more about the proxy parameters.

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. Future proxy releases will also be noted in the Google Groups Cloud SQL announce forum.

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.