Managing Snowflake connections¶

Before you can use Snowflake CLI, you must define connections, which specify how Snowflake CLI connects to Snowflake. Snowflake CLI uses the following precedence hierarchy to determine which value to use when a connection parameter is defined in multiple locations:

Command-line parameters
Environment variables overriding specific config.toml parameters, such as SNOWFLAKE_CONNECTIONS_MYCONNECTION_PASSWORD
Connections defined in config.toml file manually or using snow connection add command
Generic environment variables, such as SNOWFLAKE_USER.

You can also use the --temporary-connection option, which does not require defining it in config.toml.

Caution

For improved security, Snowflake strongly recommends using either SNOWFLAKE_CONNECTIONS_<NAME>_PASSWORD or SNOWFLAKE_PASSWORD environment variable.

Define connections¶

Connection definitions are stored in the [connections] section of the config.toml file, similar to the following:

[connections.myconnection]
account = "myaccount"
user = "jondoe"
password = "hunter2"
warehouse = "my-wh"
database = "my_db"
schema = "my_schema"

Copy

Connection definition support the same configuration options as the Snowflake Connector for Python. Additionally, you can specify a default connection in the default_connection_name variable at the top of the file. You cannot include it within a connection definition. For example:

default_connection_name = "myconnection"

[connections.myconnection]
account = "myaccount"
...

Copy

Note

For MacOS and Linux systems, Snowflake CLI requires the config.toml file to limit its file permissions to read and write for the file owner only. To set the file required file permissions execute the following commands:

chown $USER connections.toml
chmod 0600 connections.toml

Copy

Snowflake CLI also supports the connections.toml configuration file. The file should be placed in the same directory as the config.toml file, and it should contain only connections. Configurations in connections.toml require a different section name, without connections. For example, [connections.myconnection] would be just [myconnection].

Note

If both the config.toml and connections.toml configurations contain connections, Snowflake CLI uses only configurations from connections.toml.

Manage or add your connections to Snowflake with the `snow connection` commands¶

The snow connection commands let you create, manage, and test Snowflake connections.

Add a connection¶

To create a new connection and add it to the configuration file, do the following:

Execute the snow connection add command:

snow connection add

Copy

When prompted, supply the required connection, account, and username parameters, as well as any other desired optional parameters.

Name for this connection: <connection-name>
Snowflake account name: <account-name>
Snowflake username: <user-name>
Snowflake password [optional]: <password-value>
Role for the connection [optional]: <role-name>
Warehouse for the connection [optional]: <warehouse-name>
Database for the connection [optional]: <database-name>
Schema for the connection [optional]: <schema-name>
Connection host [optional]: <host-name>
Connection port [optional]: <port-value>
Snowflake region [optional]: <region-name>
Authentication method [optional]: <authentication-method>
Path to private key file [optional]: <path-to-private-key>
Path to token file [optional]: <path-to-mfa-token>
Wrote new connection myconnection2 to config.toml

You can also add values for specific parameters on the command line, as shown:

snow --config-file config.toml connection add -n myconnection2 --account myaccount2 --user jdoe2

Copy

Note

If the command finish with an error, such as if the --private_key_file option references a non-existing file, the connection is not saved in the config.toml configuration file.

By default, the snow connection add command prompts for optional parameters if they are not specified on the command line. If you want to add connections without specifying some optional parameter like account and skip the interactive prompts, you can use the --no-interactive option, as shown:

snow connection add -n myconnection2 --user jdoe2 --no-interactive

Copy

List defined connections¶

To list the available connections, enter the snow connection list command, as shown:

snow connection list

Copy

+-------------------------------------------------------------------------------------------------+
| connection_name | parameters                                                       | is_default |
|-----------------+------------------------------------------------------------------+------------|
| myconnection    | {'account': 'myaccount', 'user': 'jondoe', 'password': '****',   | False      |
|                 | 'database': 'my_db', 'schema': 'my_schema', 'warehouse':         |            |
|                 | 'my-wh'}                                                         |            |
| myconnection2   | {'account': 'myaccount2', 'user': 'jdoe2'}                       | False      |
+-------------------------------------------------------------------------------------------------+

Test a connection¶

To test whether a connection can successfully connect to Snowflake, enter the snow connection test command, similar to the following:

snow connection test -c myconnection2

Copy

+--------------------------------------------------+
| key             | value                          |
|-----------------+--------------------------------|
| Connection name | myconnection2                  |
| Status          | OK                             |
| Host            | example.snowflakecomputing.com |
| Account         | myaccount2                     |
| User            | jdoe2                          |
| Role            | ACCOUNTADMIN                   |
| Database        | not set                        |
| Warehouse       | not set                        |
+--------------------------------------------------+

Set the default connection¶

You can use the snow connection set-default command to specify which configuration Snowflake CLI should use as the default, overriding the default_connection_name configuration file and SNOWFLAKE_DEFAULT_CONNECTION_NAME variables, if set.

The following example sets the default connection to myconnection2:

snow connection set-default myconnection2

Copy

Default connection set to: myconnection2

Note

If both connections.toml and config.toml files are present, Snowflake CLI uses only connections defined in connections.toml.

Use environment variables for Snowflake credentials¶

You can specify Snowflake credentials in system environment variables instead of in configuration files. You can use the following generic environment variables only to specify connection parameters:

SNOWFLAKE_ACCOUNT
SNOWFLAKE_USER
SNOWFLAKE_PASSWORD
SNOWFLAKE_DATABASE
SNOWFLAKE_SCHEMA
SNOWFLAKE_ROLE
SNOWFLAKE_WAREHOUSE
SNOWFLAKE_AUTHENTICATOR
SNOWFLAKE_PRIVATE_KEY_PATH
SNOWFLAKE_SESSION_TOKEN
SNOWFLAKE_MASTER_TOKEN

Pass connection parameters to the `snow` command¶

You can pass connection parameters directly in every snow command that requires a connection. For a full list of connection configuration parameters, execute the snow sql --help command, as shown. Note that the output shows only the section with the connection configuration options.

snow sql --help

Copy

╭─ Connection configuration ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────╮
│ --connection,--environment             -c      TEXT     Name of the connection, as defined in your config.toml. Default: default.            │
│ --host                                         TEXT     Host address for the connection. Overrides the value specified for the connection.   │
│ --port                                         INTEGER  Port for the connection. Overrides the value specified for the connection.           │
│ --account,--accountname                        TEXT     Name assigned to your Snowflake account. Overrides the value specified for the       │
│                                                         connection.                                                                          │
│ --user,--username                              TEXT     Username to connect to Snowflake. Overrides the value specified for the connection.  │
│ --password                                     TEXT     Snowflake password. Overrides the value specified for the connection.                │
│ --authenticator                                TEXT     Snowflake authenticator. Overrides the value specified for the connection.           │
│ --private-key-file,--private-key-path          TEXT     Snowflake private key file path. Overrides the value specified for the connection.   │
│ --token-file-path                              TEXT     Path to file with an OAuth token that should be used when connecting to Snowflake    │
│ --database,--dbname                            TEXT     Database to use. Overrides the value specified for the connection.                   │
│ --schema,--schemaname                          TEXT     Database schema to use. Overrides the value specified for the connection.            │
│ --role,--rolename                              TEXT     Role to use. Overrides the value specified for the connection.                       │
│ --warehouse                                    TEXT     Warehouse to use. Overrides the value specified for the connection.                  │
│ --temporary-connection                 -x               Uses connection defined with command line parameters, instead of one defined in      │
│                                                         config                                                                               │
│ --mfa-passcode                                 TEXT     Token to use for multi-factor authentication (MFA)                                   │
│ --enable-diag                                           Run python connector diagnostic test                                                 │
│ --diag-log-path                                TEXT     Diagnostic report path                                                               │
│ --diag-allowlist-path                          TEXT     Diagnostic report path to optional allowlist                                         │
╰──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯

Caution

For improved security, Snowflake strongly recommends using either SNOWFLAKE_CONNECTIONS_<NAME>_PASSWORD or SNOWFLAKE_PASSWORD environment variable.

Use a temporary connection¶

You can also specify connection parameters from the command line using the --temporary-connection [-x] option. It ignores all definitions from the config.toml, using ones specified by command line options instead. This approach can be helpful for CI/CD use cases when you don’t want to use a configuration file. When you use a temporary connection, Snowflake CLI ignores any connection variables defined in the config.toml file, but does still use any environment variables, such as SNOWFLAKE_ACCOUNT, you set.

snow sql -q "select 42;" --temporary-connection \
                           --account myaccount \
                           --user jdoe

Copy

select 42;
+----+
| 42 |
|----|
| 42 |
+----+

Caution

For improved security, Snowflake strongly recommends using either SNOWFLAKE_CONNECTIONS_<NAME>_PASSWORD or SNOWFLAKE_PASSWORD environment variable.

Additional ways to authenticate your connection¶

You can also use the following methods to authenticate your connection to Snowflake:

Use a private key file for authentication
Use OAuth authentication
Use multi-factor authentication (MFA)
Use SSO (single sign-on)

Use a private key file for authentication¶

To use private key file for authentication, your connection configuration requires you to set the authenticator parameter to SNOWFLAKE_JWT and provide path to file with your private key similar to the following:

Specify the --private_key-file option in the snow connection add command, as shown:

snow connection add \
   --connection-name jwt \
   --authenticator SNOWFLAKE_JWT \
   --private-key-file ~/.ssh/sf_private_key.p8

Copy

Use the configuration file:

[connections.jwt]
account = "my_account"
user = "jdoe"
authenticator = "SNOWFLAKE_JWT"
private_key_file = "~/sf_private_key.p8"

Copy

For more details on configuring key pair authentication, see Key-pair authentication and key-pair rotation.

Note

If your private key is passphrase-protected, set the PRIVATE_KEY_PASSPHRASE environment variable to that passphrase.

Use OAuth authentication¶

To use connect using OATH, you can do either of the following:

Specify the --token-file-path option in the snow connection add command, as shown:
```
snow connection add --token-file-path "my-token.txt"
```
Copy
In the config.toml file, set authenticator = "oauth", and add the token_file_path parameter to the connection definition, as shown:
```
[connections.oauth]
account = "my_account"
user = "jdoe"
authenticator = "oauth"
token_file_path = "my-token.txt"
```
Copy

Use multi-factor authentication (MFA)¶

To use MFA:

Set up multi-factor authentication in Snowflake and set the authenticator parameter to snowflake (which is a default value).
If you want to use a Duo-generated passcode instead of the push mechanism, use either the --mfa-passcode <passcode> option or set passcode_in_password = true in the config.toml file and include the passcode in your password as described in Using MFA in Python.
Note

If you want use the passcode in the password for authentication, after executing the first snow command, you can no longer provide the passcode as long as the token in valid. You must do the following:
- Remove the passcode from the password.
- Remove or comment the passcode_in_password = true in the config.toml file.

To enable MFA caching:

For your account, set ALLOW_CLIENT_MFA_CACHING = true.
In your config.toml file, add authenticator = username_password_mfa to your connection.

Use SSO (single sign-on)¶

If you have configured Snowflake to use single sign-on (SSO), you can configure your client application to use SSO for authentication. See Using SSO with client applications that connect to Snowflake for details and configure your connection using the instructions for Python.

Managing Snowflake connections¶

Define connections¶

Manage or add your connections to Snowflake with the snow connection commands¶

Add a connection¶

List defined connections¶

Test a connection¶

Set the default connection¶

Use environment variables for Snowflake credentials¶

Pass connection parameters to the snow command¶

Use a temporary connection¶

Additional ways to authenticate your connection¶

Use a private key file for authentication¶

Use OAuth authentication¶

Use multi-factor authentication (MFA)¶

Use SSO (single sign-on)¶

Manage or add your connections to Snowflake with the `snow connection` commands¶

Pass connection parameters to the `snow` command¶