Connecting Using SnowSQL

This topic describes how to connect to Snowflake by entering connection parameters manually. The topic then explains how to configure a default connection for ease of use, as well as one or more named connections to use alternative connection settings or create multiple concurrent sessions.

In this Topic:

Connection Syntax

$ snowsql <connection_parameters>

Where connection_parameters are one or more of the following. For detailed descriptions of the parameters, see Connection Parameters Reference (in this topic).

-a, --accountname TEXT          Name assigned to your Snowflake account. Honors $SNOWSQL_ACCOUNT.
-u, --username TEXT             Username to connect to Snowflake. Honors $SNOWSQL_USER.
-d, --dbname TEXT               Database to use. Honors $SNOWSQL_DATABASE.
-s, --schemaname TEXT           Schema in the database to use. Honors $SNOWSQL_SCHEMA.
-r, --rolename TEXT             Role name to use. Honors $SNOWSQL_ROLE.
-w, --warehouse TEXT            Warehouse to use. Honors $SNOWSQL_WAREHOUSE.
-h, --host TEXT                 Host address for the connection. Honors $SNOWSQL_HOST.
-p, --port INTEGER              Port number for the connection. Honors $SNOWSQL_PORT.
--region TEXT                   Snowflake region. US-West (default), US-East: us-east-1, EU (Dublin): eu-west-1, EU (Frankfurt): eu-central-1, Asia Pacific (Sydney): ap-southeast-2. Honors $SNOWSQL_REGION.
-m, --mfa-passcode TEXT         Token to use for multi-factor authentication (MFA)
--mfa-passcode-in-password      Appends the MFA passcode to the end of the password.
--abort-detached-query          Aborts a query if the connection between the client and server is lost. By default, it won't abort even if the connection is lost.
--probe-connection              Test connectivity to Snowflake. This option is mainly used to print out the TLS/SSL certificate chain.
--authenticator TEXT            Authenticator: 'snowflake', 'externalbrowser' (to use any IdP and a web browser), or https://<your_okta_account_name>.okta.com (to use Okta natively).
-v, --version                   Shows the current SnowSQL version, or uses a specific version if provided as a value.
--noup                          Disables auto-upgrade for this run. If no version is specified for -v, the latest version in ~/.snowsql/ is used.
-D, --variable TEXT             Sets a variable to be referred by &<var>. -D tablename=CENUSTRACKONE or --variable db_key=$DB_KEY
-o, --option TEXT               Set SnowSQL options. See the options reference in the Snowflake documentation.
-f, --filename PATH             File to execute.
-q, --query TEXT                Query to execute.
--config PATH                   Path and name of the SnowSQL configuration file.  By default, ~/.snowsql/config.
-P, --prompt                    Forces a password prompt. By default, $SNOWSQL_PWD is used to set the password.
-M, --mfa-prompt                Forces a prompt for the second token for MFA.
-c, --connection TEXT           Named set of connection parameters to use.
--single-transaction            Connects with autocommit disabled. Wraps BEGIN/COMMIT around statements to execute them as a single transaction, ensuring all commands complete successfully or no change is applied.
-?, --help                      Show this message and exit.

Specifying Passwords When Connecting

Passwords cannot be passed through connection parameters. Passwords must be specified in one of the following ways:

  • Entered via interactive prompt in SnowSQL (applies to passwords only).
  • Defined in the SnowSQL configuration file using the password option. For details, see Configuring Default Connection Settings (in this topic).
  • Specified using the SNOWSQL_PWD environment variables. For details, see Using Environment Variables (in this topic).

Note

In Windows environments, the Cygwin terminal doesn’t prompt for your account name, username, or password. This is because SnowSQL cannot enable TTY mode in Cygwin terminals.

Using Environment Variables

Currently, environment variables can only be used to pre-specify some command line parameter values such as password, host, and database. Environment variables are not available to use in SnowSQL variable substitution unless they are explicitly specified on the command line when starting SnowSQL, using either the -D or --variable connection parameter, e.g.:

snowsql ... -D tablename=CENUSTRACKONE --variable db_key=$DB_KEY

In the above example, --variable sets a Snowflake variable named db_key to the DB_KEY environment variable.

Enabling Support for the OCSP Response Cache Server

As part of the “handshake” initiated to establish a secure connection to a Snowflake service endpoint, Snowflake clients request the revocation status of the TLS/SSL certificate for the service endpoint. To mitigate latency and other issues when requesting the certificate revokation status from the certificate authority’s (CA) Certificate revocation list (CRL) or Online Certificate Status Protocol (OCSP) servers, Snowflake provides the following three levels of cache that persist the revocation status:

  1. Memory cache, which persists for the life of the process.
  2. File cache, which persists until the cache directory, e.g., ~/.cache/snowflake, is purged.
  3. OCSP response server cache

The OCSP response cache server fetches OCSP responses hourly from the CA’s OCSP servers and stores them for 24 hours. Clients can then request the validation status of a given Snowflake certificate from this server cache.

If none of the three layers of cache contain the OCSP response, the client attempts to fetch the validation status from the CA’s OCSP server.

To use the cache server, set the following environment variable before starting the connection:

export SF_OCSP_RESPONSE_CACHE_SERVER_ENABLED=true

For example:

export SF_OCSP_RESPONSE_CACHE_SERVER_ENABLED=true

snowsql -c myconnection

Configuring Default Connection Settings

We recommend configuring your default connection parameters to simplify the connection process. Thereafter, when connecting to Snowflake, you can omit your Snowflake account name, username, and any other parameters you have configured as your default values.

To configure your default settings:

  1. Open the config configuration file in a text editor. By default, the file is located in:

    Linux/Mac OS:~/.snowsql/
    Windows:%USERPROFILE%\.snowsql\

    Note

    You can change the default location by including the --config <path> connection parameter when starting SnowSQL.

  2. In the [connections] section, configure the default connection parameters by removing the comment symbol from any of the following parameters and specifying the correct values:

    [connections]
    #accountname = <string>   # Account name to connect to Snowflake.
    #region = <region_id>     # Default region. Optional.
    #username = <string>      # User name in the account. Optional.
    #password = <string>      # User password. Optional.
    #dbname = <string>        # Default database. Optional.
    #schemaname = <string>    # Default schema. Optional.
    #warehousename = <string> # Default warehouse. Optional.
    #rolename = <string>      # Default role. Optional.
    #authenticator = <string> # Authenticator: 'snowflake', 'externalbrowser' (to use any IdP and a web browser), or https://<your_okta_account_name>.okta.com (to use Okta natively).
    

    Specify a #region value only if your account is in a Snowflake Region other than US West. If the parameter is not present, you can add it manually.

    Valid values for <region_id> are:

    Region ID Snowflake Region
    us-east-1 US East
    eu-west-1 EU (Dublin)
    eu-central-1 EU (Frankfurt)
    ap-southeast-2 Asia Pacific (Sydney)

    Attention

    • The password is stored in plain text in the config file. You must explicitly secure the file to restrict access. For example, in Linux or Mac OS, you can set the read permissions to you alone by running chmod:

      $ chmod 700 ~/.snowsql/config
      
    • If your password includes special characters, you must enclose the password in either single quotes or double quotes.

Using Named Connections

To make multiple simultaneous connections to Snowflake, or to simply store different sets of connection configurations, you can define one or more named connections.

Defining Named Connections in the Configuration File

  1. Open the config configuration file in a text editor. By default, the file is located in:

    Linux/Mac OS:~/.snowsql/
    Windows:%USERPROFILE%\.snowsql\
  2. Add a separate [connections] section with a unique name for each named connection.

    For example, the following illustrates a connection named my_example_connection for a user account in the EU (Frankfurt) Snowflake Region:

    [connections.my_example_connection]
    accountname = abc123
    username = jsmith
    password = xxxxxxxxxxxxxxxxxxxx
    dbname = mydb
    schemaname = public
    warehousename = mywh
    region = eu-central-1
    

Connecting to Snowflake Using a Named Connection

Use the -c <string> (or --connection <string>) connection parameter to specify a named connection, where string is the name of a connection defined in the configuration file.

For example, connect using the my_example_connection connection you created in Defining Named Connections in the Configuration File:

snowsql -c my_example_connection

Using a Proxy Server

To use a proxy server, configure the following environment variables:

  • HTTP_PROXY
  • HTTPS_PROXY
  • NO_PROXY

Note

The proxy parameters, i.e., proxy_host, proxy_port, proxy_user and SNOWFLAKE_PROXY_PWD in the command line and config files, are deprecated. Use the environment variables instead.

Note

Requires SnowSQL 1.1.20 or later. To determine your current version, see Understanding SnowSQL Versioning.

For example:

  • Linux or Mac OS X:

    export HTTP_PROXY='http://username:password@proxyserver.company.com:80'
    export HTTPS_PROXY='http://username:password@proxyserver.company.com:80'
    
  • Windows:

    set HTTP_PROXY='http://username:password@proxyserver.company.com:80'
    set HTTPS_PROXY='http://username:password@proxyserver.company.com:80'
    

Tip

Snowflake’s security model does not allow Secure Sockets Layer (SSL) proxies (using an HTTPS certificate). Your proxy server must use a publicly-available Certificate Authority (CA), reducing potential security risks such as a MITM (Man In The Middle) attack through a compromised proxy.

If you must use your SSL proxy, we strongly recommend that you update the server policy to pass through the Snowflake certificate such that no certificate is altered in the middle of communications.

Optionally NO_PROXY can be used to bypass the proxy for specific communications. For example, AWS S3 access can be bypassed by specifying NO_PROXY=".amazonaws.com".

Using a Web Browser for Federated Authentication/SSO

To use browser-based SSO authentication for SnowSQL, add --authenticator externalbrowser to your SnowSQL connection parameters:

For example:

snowsql -a <accountname> -u <username> --authenticator externalbrowser

When you connect to Snowflake using SnowSQL, the following actions occur:

  1. The default web browser in your operation system launches or opens a new tab or window, displaying the IdP authentication page.
  2. Enter your IdP username and password. If multi-factor authentication is enabled for your account, you may need to type a passcode from another device or confirm the login for authentication.
  3. When the IdP has authenticated your credentials, the browser displays a success message. Return to the terminal window and use the Snowflake session that has started.

For more information about federated authentication/SSO, see Managing/Using Federated Authentication.

Connection Error Handling

Cannot open self /usr/bin/snowsql or archive /usr/bin/snowsql.pkg (Linux Only)

Due to a limitation in pyinstaller (the program that packages SnowSQL into a stand-alone executable from Python source code), prelink mistakenly strips parts of the snowsql executable and causes this error.

To avoid this issue, the SnowSQL installer attempts to update the prelink configuration file in /etc/prelink.conf.d/snowsql.conf for the snowsql executable such that prelink does not alter the file. Unfortunately, this configuration update cannot be made by the SnowSQL auto-upgrade process.

Work with your system administrator to run the following command on your workstation:

$ sudo bash -c "echo '-b snowsql' > /etc/prelink.conf.d/snowsql.conf"

Note

If you install snowsql in your user home directory, this issue is less likely to occur because prelink is configured, by default, to scan the shared binary directories (e.g. /usr/bin or /bin) and does not alter programs in your home directory.

Connection Parameters Reference

-a , --accountname

Type:

String

Description:

Required

Specifies the name of your account (provided by Snowflake).

The string can be an environment variable:

Linux/Mac OS:$SNOWSQL_ACCOUNT
Windows:%SNOWSQL_ACCOUNT%

For example, in Linux or Mac OS:

$ export SNOWSQL_ACCOUNT=abc123

$ snowsql -a $SNOWSQL_ACCOUNT

This connection parameter can also be set in the configuration file.

Default:

None

-u , --username

Type:

String

Description:

Specifies the login name of the user with whom you connect to the specified account.

The string can be an environment variable:

Linux/Mac OS:$SNOWSQL_USER
Windows:%SNOWSQL_USER%

For example, in Linux or Mac OS:

$ export SNOWSQL_USER=jdoe

$ snowsql -a $SNOWSQL_USER

This connection parameter can also be set in the configuration file.

Default:

None

--region

Type:

String

Description:

Specifies the ID for the Snowflake Region where your account is located (use only if your account is not in US West):

Region ID Snowflake Region
us-east-1 US East
eu-west-1 EU (Dublin)
eu-central-1 EU (Frankfurt)
ap-southeast-2 Asia Pacific (Sydney)

The string can be an environment variable:

Linux/Mac OS:$SNOWSQL_REGION
Windows:%SNOWSQL_REGION%
Default:

None

-d , --dbname

Type:

String

Description:

Specifies the database to use by default in the client session (can be changed after login).

The string can be an environment variable:

Linux/Mac OS:$SNOWSQL_DATABASE
Windows:%SNOWSQL_DATABASE%

This connection parameter can also be set in the configuration file.

Default:

None

-s , --schemaname

Type:

String

Description:

Specifies the database schema to use by default in the client session (can be changed after login).

The string can be an environment variable:

Linux/Mac OS:$SNOWSQL_SCHEMA
Windows:%SNOWSQL_SCHEMA%

This connection parameter can also be set in the configuration file.

Default:

None

-r , --rolename

Type:

String

Description:

Specifies the role to use by default for accessing Snowflake objects in the client session (can be changed after login).

The string can be an environment variable:

Linux/Mac OS:$SNOWSQL_ROLE
Windows:%SNOWSQL_ROLE%

This connection parameter can also be set in the configuration file.

Default:

None

-w , --warehouse

Type:

String

Description:

Specifies the virtual warehouse to use by default for queries, loading, etc. in the client session (can be changed after login).

The string can be an environment variable:

Linux/Mac OS:$SNOWSQL_WAREHOUSE
Windows:%SNOWSQL_WAREHOUSE%

This connection parameter can also be set in the configuration file.

Default:

None

-h , --host

Type:

String

Description:

For internal use only

Specifies the address of the host to which you connect in Snowflake. This option does not need to be specified because the host address is determined automatically by concatenating the account name you specified and the rest of the Snowflake domain snowflakecomputing.com.

Default:

None

-p , --port

Type:

String

Description:

For internal use only

Specifies the port number to use for connection. This option does not need to be specified because the port number is always 443.

Default:

None

-m , --mfa-passcode

Type:String
Description:Specifies the second token for MFA (multi-factor authentication) if you pass in the passcode in the command line.
Default:None

--mfa-passcode-in-password

Type:

None

Description:

Appends the MFA passcode to the end of the password.

You can force the password prompt and type the password followed by the MFA passcode. For example if the MFA token was 123456 and the password was PASSWORD:

snowsql ... -P ...

Password: PASSWORD123456
Default:

N/A (parameter doesn’t take a value)

--abort-detached-query

Type:Boolean
Description:Aborts a query if the connection between the client and server is lost.
Default:False (i.e. an active query does not abort if the connection is lost)

--probe-connection

Type:None
Description:Test connectivity to Snowflake and report the results. Note that this is an experimental option used mainly to print out the TLS/SSL certificate chain.
Default:N/A (parameter doesn’t take a value)

--authenticator

Type:

String (Constant)

Description:

Specifies the authenticator to use for verifying user login credentials.

Possible values:

  • snowflake uses the internal Snowflake authenticator.
  • externalbrowser authenticates using your web browser and Okta, ADFS, or any other SAML 2.0-compliant identify provider (IdP) that has been defined for your account.
  • https://<your_okta_account_name>.okta.com (i.e. the URL endpoint for Okta) authenticates through native Okta (only supported if your IdP is Okta).

For more information, see Managing/Using Federated Authentication.

Default:

snowflake

Note

The externalbrowser authenticator is only supported in terminal windows that have web browser access. For example, a terminal window on a remote machine accessed through a SSH (Secure Shell) session may require additional setup to open a web browser.

If you don’t have access to a web browser, but your IdP is Okta, you can use native Okta (i.e. set the authenticator to https://<your_okta_account_name>.okta.com).

-v , --version

Type:String
Description:Use the specified SnowSQL version or, if no version is specified, display the latest SnowSQL version installed.
Default:None

--noup

Type:None
Description:Disables auto-upgrade for this run. If this option is not included and a newer version is available, SnowSQL automatically downloads and installs the new version. The next time you run SnowSQL, the new version is used.
Default:N/A (parameter doesn’t take a value)

--versions

Type:None
Description:Lists all available versions of SnowSQL that can be installed and run. To install an earlier SnowSQL version from the list, use the -v option and specify the version you want to install.
Default:N/A (parameter doesn’t take a value)

-D , --variable

Type:

String

Description:

Defines SnowSQL variables on the command line. This option can be used to set specific variables to use in Snowflake. For example:

... -D tablename=CENUSTRACKONE --variable db_key=$DB_KEY ...
Default:

None

-o , --option

Type:String
Description:Defines SnowSQL configuration options on the command line. These options override any options that have been set in the SnowSQL configuration file. For descriptions of the options you can set/override, see SnowSQL Configuration Options Reference.
Default:None

-f , --filename

Type:

String

Description:

Specifies a SQL file to execute in batch mode.

The string can be a file name (including the directory path, if needed) or a URL to the file.

Default:

None

-q , --query

Type:

String

Description:

Specifies a SQL query to execute.

The string can be a file name (including the directory path, if needed) or a URL to the file.

Default:

None

--config

Type:

String

Description:

Specifies the location (i.e. directory path) for the SnowSQL configuration file. Include this connector parameter if you want to move or copy the configuration file from the default location.

Default:

OS-specific:

Linux/Mac OS:~/.snowsql/
Windows:%USERPROFILE%\.snowsql\

-P , --prompt

Type:None
Description:Forces a password prompt when the password is stored in the SnowSQL configuration file.
Default:N/A (parameter doesn’t take a value)

-M, --mfa-prompt

Type:None
Description:Forces a prompt for the second token for MFA. Alternatively use --mfa-passcode <string> if you want to pass in to the command line.
Default:N/A (parameter doesn’t take a value)

-c , --connection

Type:String
Description:Specifies a connection to use, where the specified string is the name of a connection defined in the SnowSQL configuration file. For more details, see Using Named Connections (in this topic).
Default:None

--single-transaction

Type:None
Description:Combined with --filename, --query, or standard input commands, this option wraps BEGIN/COMMIT around the statements to ensure all commands complete successfully or no change is applied.
Default:N/A (parameter doesn’t take a value)

Note

Note that if the input commands use BEGIN, COMMIT, or ROLLBACK, this option will not work correctly. Also, if any command cannot be executed inside a transaction block, this option will cause the command to fail.

-? , --help

Type:None
Description:Shows the command line quick usage guide.
Default:N/A (parameter doesn’t take a value)