ODBC configuration and connection parameters¶

Required connection parameters¶

<name> (Data Source)

Specifies the name of your DSN.

port (Port)

Specifies the port on which the driver listens for Snowflake communication.

Note

You do not need to change the default Port value of 443.

pwd (Password)

A password is required to connect to Snowflake; however, for security and authentication reasons, Snowflake strongly discourages storing password credentials directly within any DSN definition.

Typically, the credentials are passed to the driver programmatically by the client application that is attempting to connect to Snowflake.

Note

In Windows, the ODBC driver displays a Password field in the Data Source Administration tool; however, the driver does not store any values entered in the field. Instead, the driver requires login credentials to be provided at connection time.

server (Server)

Specifies the hostname for your account in the following format:

account_identifier.snowflakecomputing.cn

To determine the account identifier to use, see Configuring a client, driver, library, or third-party application to connect to Snowflake.

uid (User)

Specifies the login name of the Snowflake user to authenticate.

Optional connection parameters¶

BROWSER_RESPONSE_TIMEOUT

Specifies the number of seconds to wait for an authentication response in an external browser.

Default is 120.

CLIENT_OUT_OF_BAND_TELEMETRY_ENABLED=<Boolean>

Specifies whether to enable out-of-band telemetry.

Default is true

CLIENT_SESSION_KEEP_ALIVE=<Boolean>

Specifies whether to keep the current session active after a period of inactivity or to force the user to log in again. If the value is true, Snowflake keeps the session active indefinitely, even if there is no activity from the user. If the value is false, the user must log in again after four hours of inactivity.

Default is false.

database (Database)

Specifies the default database to use for sessions initiated by the driver.

disableSamlUrlCheck

Specifies whether to disable verification for SAML URLs. By default, the driver verifies SAML URLs.

Default is false.

maxHttpRetries (Database)

Specifies the maximum number of HTTP retries for queries with failed HTTP requests before returning an error. Setting maxHttpRetries=0 removes the retry limit, but doing so runs the risk of the driver infinitely retrying failed HTTP calls.

Default value is 7.

role (Role)

Specifies the default role to use for sessions initiated by the driver. The specified role should be a role that has been assigned to the specified user for the driver. If the specified role does not match any of the roles assigned to the user, sessions initiated by the driver have no role initially; however, a role can always be specified from within the session.

schema (Schema)

Specifies the default schema to use for sessions initiated by the driver.

Default is public.

SecondaryRoles (Role)

Specifies the secondary roles to use for sessions initiated by the driver. The roles must already be granted to the specified user for the driver. Secondary roles can also be activated from within a user session using the USE SECONDARY ROLES command.

Possible values include:

• All: All roles granted to the user.

• None: No roles allowed (disables secondary roles).

tracing (Tracing)

The level of detail to be logged in the driver trace files:

• 0 = Disable tracing

• 1 = Fatal only error tracing

• 2 = Error tracing

• 3 = Warning tracing

• 4 = Info tracing

• 5 = Debug tracing

• 6 = Detailed tracing

warehouse (Warehouse)

Specifies the default warehouse to use for sessions initiated by the driver.

Additional connection parameters¶

allowEmptyProxy

Specifies whether to allow empty values for the proxy and no_proxy connection parameters, as described in the following sections:

• Using Connection Parameters

• Using Configuration Parameters

• Using Environment Variables

Setting this value produces the following effects:

• true: The driver treats empty proxy values as valid proxy settings and overrides any existing settings or environment variable.

• false: The driver ignores empty proxy values and uses the specified configuration parameters or environment variable.

Default is true.

application

Snowflake partner use only: Specifies the name of a partner application to connect through ODBC.

This parameter can also be set by calling the SQLSetConnectAttr() function. For more details, see Snowflake-specific behavior of the SQLSetConnectAttr function.

authenticator

Specifies the authenticator to use for verifying user login credentials:

• snowflake (Default) to use the internal Snowflake authenticator.

• externalbrowser to use your web browser to authenticate with Okta, AD FS, or any other SAML 2.0-compliant identity provider (IdP) that has been defined for your account.

  Note

  The Snowflake ODBC driver does not support externalbrowser authentication using Microsoft Excel with MacOS.

• https://<okta_account_name>.okta.com (i.e. the URL endpoint for your Okta account) to authenticate through native Okta (only supported if your IdP is Okta).

• oauth to authenticate using OAuth. When OAuth is specified as the authenticator, you must also set the token parameter to specify the OAuth token (see below).

• username_password_mfa to authenticate with MFA token caching. For more details, see Using Multi-Factor Authentication (in this topic).

Default is snowflake.

On Windows, you can use the ODBC Data Source Administration Tool to set this parameter.

For more information on authentication, see Managing/Using federated authentication and Clients, drivers, and connectors.

default_binary_size, . default_varchar_size

Specifies the default size, in bytes, that the driver uses when retrieving and converting values from BINARY or VARCHAR columns of undetermined sizes. Set this when retrieving values from these types of columns.

By default, the driver uses 8388608 (for BINARY columns) and 16777216 (for VARCHAR columns) as the default sizes when allocating memory for retrieving the value of a column of undetermined size.

To reduce the amount of memory allocated for these values, you can set default_binary_size and default_varchar_size to the maximum size of the values in these types of columns.

Note

Setting these values only changes the SQL_DESC_LENGTH field in Implementation Row Descriptor (IRD) and the corresponding values returned from SQLDescribeCol/SQLColAttribute/SQLColAttributes. The driver still returns the entire data even when it’s length exceeds the setting.

On the other hand, an application could allocate a data buffer based on the length specified in these parameters that could truncate the data due to not enough space in the buffer. As the best practice, Snowflake recommends setting the default size larger than the maximum size of the typical data (e.g. 4000 or 8000 bytes) to reduce the memory usage significantly from the original default values of 16777216/8388608 bytes and to minimize the chance of data truncation.

Note that you can also use these settings to avoid the following error, which can occur when using the Microsoft OLE DB provider (MSDASQL) with a Snowflake database:

Requested conversion is not supported
Cannot get the current row value of column

Copy

You can specify these parameters as connection configuration parameters (for example, in the simba.snowflake.ini on macOS and Linux). If this is set as both a connection parameter and a configuration parameter, the connection parameter in the DSN (or connection string) takes precedence.

These parameters were introduced in version 2.23.2 of the ODBC Driver.

get_size_threshold

Specifies the minimum file size, in megabytes (MB), to break files into smaller parts when downloading files with the GET command. Files with sizes smaller than this threshold will not use multi-part downloading.

Default is 5 (MB).

Note

Setting this value as a connection parameter overrides the value of the corresponding get_size_threshold configuration parameter.

login_timeout

Specifies how long, in seconds, to wait for a response when connecting to the Snowflake service before returning a login failure error.

Default is 300 (seconds).

network_timeout

Specifies how long, in seconds, to wait for a response when interacting with the Snowflake service before returning an error. Zero (0) indicates no network timeout is set.

Default is 0 (seconds).

retryTimeout

Specifies how long, in seconds, to wait before returning an error for HTTP retries from queries with failed HTTP requests. Zero (0) indicates no retry timeout is set.

Default is 300 (seconds).

no_proxy

Specifies which hostname endings should be allowed to bypass the proxy server (e.g. no_proxy=.amazonaws.com means that Amazon S3 access does not need to go through the proxy).

This parameter does not support wildcards. Each value specified should be one of the following:

• The end of a hostname (or a complete hostname), for example:

  • .amazonaws.com

  • myorganization-myaccount.snowflakecomputing.cn

• An IP address, for example:

  • 192.196.1.15

If more than one value is specified, values should be separated by commas, for example:

no_proxy=localhost,.my_company.com,myorganization-myaccount.snowflakecomputing.cn,192.168.1.15,192.168.1.16

Copy

Note

This parameter is applied to the process. If another connection shares the same process, the proxy setting must be identical or the behavior is not predictable.

odbc_use_standard_timestamp_columnsize

This boolean parameter affects the column size (in characters) returned for SQL_TYPE_TIMESTAMP. When this parameter is set to true, the driver returns 29, following the ODBC standard. When this parameter is set to false, the driver returns 35, which allows room for the timezone offset (e.g. “-08:00”).

This value can be set via not only the odbc.ini file (Linux or macOS) or the Microsoft Windows registry, but also the connection string.

Default is false.

passcode

Specifies the passcode to use for multi-factor authentication.

For more information about multi-factor authentication, see Multi-factor authentication (MFA).

passcodeInPassword

Specifies whether the passcode for multi-factor authentication is appended to the password:

• on (or true) specifies the passcode is appended.

• off (or false) or any other value specifies the passcode is not appended.

The default value is off.

proxy

Specifies the proxy server URL in the format http://<hostname>:<port>/ or <hostname>:<port_number> so that all communications from ODBC use the proxy server.

Note

This parameter is applied to the process. If another connection shares the same process, the proxy setting must be identical or the behavior is not predictable.

put_compresslv

Specifies the compression rate the ODBC driver uses when transferring data with the PUT command. This parameter overrides the default gzip compression level. If you do not specify put_compresslv the ODBC driver uses the default compression level.

Valid values are -1 to 9. The default value is -1 and specifies the default Z_DEFAULT_COMPRESSION.

Values 0 through 9 specify a custom compression rate. 0 causes the ODBC driver to use a lower compression rate and 9 uses a higher compression rate. Using a higher compression rate results in slower data transfer speeds.

You can also specify this parameter as a configuration parameter (for example, in the simba.snowflake.ini on macOS and Linux). If this is set as both a connection parameter and a configuration parameter, the connection parameter in the DSN (or connection string) takes precedence.

This parameter was introduced in version 2.23.3 of the ODBC Driver.

put_fastfail

If you are using wildcard characters with the PUT command to upload multiple files at once and you want the driver to stop uploading the files when an error occurs, set this parameter to true.

The default value is false, which means that if an error occurs with one file, the driver continues uploading the rest of the files.

This parameter was introduced in version 2.22.3 of the ODBC Driver.

As of version 2.22.5 of the ODBC Driver, you can also specify this parameter as a configuration parameter (for example, in the simba.snowflake.ini on macOS and Linux). If this is set as both a connection parameter and a configuration parameter, the connection parameter in the DSN (or connection string) takes precedence.

put_maxretries

Specifies the number of times that the driver should retry the PUT command if the command fails.

The default value is 5.

The valid range of values for this parameter is 0 to 100. If you specify a value outside this range, the driver uses the default value of 5.

This parameter was introduced in version 2.22.3 of the ODBC Driver.

As of version 2.22.5 of the ODBC Driver, you can also specify this parameter as a configuration parameter (for example, in the simba.snowflake.ini on macOS and Linux). If this is set as both a connection parameter and a configuration parameter, the connection parameter in the DSN (or connection string) takes precedence.

put_tempdir

Specifies the temporary directory to use for PUT command requests. The driver uses this temporary directory to create temporary compressed files before uploading those files to Snowflake.

If this parameter is not set, the driver creates and uses the temporary directory /tmp/snowflakeTmp_username, where username is the username of the current user in the operating system.

You can also specify this parameter as a configuration parameter (for example, in the simba.snowflake.ini on macOS and Linux). If this is set as both a connection parameter and a configuration parameter, the connection parameter in the DSN (or connection string) takes precedence.

This parameter was introduced in version 2.23.1 of the ODBC Driver.

token=<string>

Specifies the OAuth token to use for authentication, where <string> is the token. This parameter is required only when the authenticator=oauth parameter is set.

Default is none.

query_timeout

Specifies how long, in seconds, to wait for a query to complete before returning an error. Zero (0) indicates to wait indefinitely.

Default is 0 (seconds).

validateSessionParam

Specifies how to respond when any of the following session connection parameters are invalid:

enable_connection_diag

Specifies whether the connector generates a connectivity diagnostic report.

Default is false.

connection_diag_log_path

Specifies the absolute path where the connectivity report is stored.

Valid only when enable_connection_diag is true.

Example: connection_diag_log_path=C:\\reports

connection_diag_allowlist_path

Specifies the absolute path to a JSON file containing the output of SYSTEM$ALLOWLIST() or SYSTEM$ALLOWLIST_PRIVATELINK().

Valid only when enable_connection_diag is true.

Example: connection_diag_log_path=C:\\allowlist.json

Using connection parameters¶

To connect through a proxy server, add the following connection parameters to the DSN:

• proxy

• no_proxy

For example:

[connection]
Description = SnowflakeDB
Driver      = SnowflakeDSIIDriver
Locale      = en-US
server      = myorganization-myaccount.snowflakecomputing.cn
proxy       = http://proxyserver.company:80
no_proxy    = .amazonaws.com

Copy

See Connection Parameters for parameter descriptions.

Using configuration parameters¶

To connect through a proxy server, add the following configuration parameters:

• Proxy

• NoProxy

See Configuration Parameters for parameter descriptions.

Using environment variables¶

To connect through a proxy server, configure the following environment variables:

• http_proxy

• https_proxy

• no_proxy

For example:

• Linux or macOS:

  export http_proxy=http://proxyserver.company.com:80
  export https_proxy=http://proxyserver.company.com:80
  

  Copy

  If the proxy server requires a user name and password, include the credentials, for example:

  export https_proxy=http://username:password@proxyserver.company.com:80
  

  Copy

• Windows:

  set http_proxy=http://proxyserver.company.com:80
  set https_proxy=http://proxyserver.company.com:80
  

  Copy

  If the proxy server requires a user name and password, include the credentials, for example:

  set https_proxy=http://username:password@proxyserver.company.com:80
  

  Copy

Optionally, you can set no_proxy to bypass the proxy for specific communications (e.g. no_proxy=.amazonaws.com to bypass Amazon S3 access).

Logging configuration file¶

Alternatively, you can easily specify the log level and the directory in which to save log files in the sf_client_config.json configuration file.

This configuration file uses JSON to define the log_level and log_path logging parameters, as follows:

{
  "common": {
    "log_level": "DEBUG",
    "log_path": "/home/user/logs"
  }
}

The driver looks for the location of the configuration file in the following order:

• client_config_file containing the full path to the configuration file.

• SF_CLIENT_CONFIG_FILE environment variable, containing the full path to the configuration file.

• ODBC driver installation directory, where the file must be named sf_client_config.json.

• User’s home directory, where the file must be named sf_client_config.json.