ODBC configuration and connection parameters¶

Setting parameters in Windows¶

In Windows:

• Configuration parameters are set in the Windows registry using regedit and the following registry path:

  [HKEY_LOCAL_MACHINE\SOFTWARE\Snowflake\Driver]
  

  Copy

• Connection parameters are set in Data Source Names (DSNs):

  • DSNs are typically created and edited using the Windows Data Source Administration tool.

  • If you wish, the registry keys for DSNs can be edited directly in the Windows registry using regedit. The registry path to the keys is different depending on whether you’re using 64-bit and 32-bit Windows and whether you’re editing a user or system DSN:

    • 64-bit Windows:

      [HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\<DSN_NAME>]
      
      [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<DSN_NAME>]
      

      Copy

    • 32-bit Windows:

      [HKEY_CURRENT_USER\SOFTWARE\WOW6432NODE\ODBC\ODBC.INI\<DSN_NAME>]
      
      [HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432NODE\ODBC\ODBC.INI\<DSN_NAME>]
      

      Copy

    To add a connection parameter using regedit, add a new String Value, double-click on the value you created, then enter the ODBC parameter as the Value name and the parameter value as the Value data.

Setting parameters in macOS or Linux¶

In macOS or Linux:

• Configuration parameters are set in the configuration file (simba.snowflake.ini).

• Connection parameters are set in the data source name (DSN) file (odbc.ini).

Configuration parameters¶

CABundleFile

Set the location of the Certificate Authority (CA) bundle file. Must reference a file that includes a valid list of CA certificates.

For Linux, the RPM and DEB installers automatically copy the file and set this parameter.

For Mac, the PKG installer copies the file and sets this parameter.

For Windows, the MSI installer copies the file and sets this parameter.

A manual installation requires you to download the file from https://curl.haxx.se/docs/caextract.html and set the location of the file.

client_config_file

Specifies the path of a logging configuration file that you can use to define the logging level and directory for saving log files.

CURLVerboseMode

Set to true to enable cURL verbose logging. The log file snowflake_odbc_curl.dmp is created and updated. The Snowflake ODBC driver uses cURL as the HTTP and TLS library. This parameter is useful for diagnosing network issues.

DisableOCSPCheck

Set to true to disable the TLS certificate revocation status check by the Online Certificate Status Protocol (OCSP). In normal circumstances, this flag should not set. But if the OCSP availability problem persists, the application might temporarily set this parameter in order to unblock connectivity issues and remove it when the OCSP availability problem is addressed.

DisableTelemetry

Specifies whether toggling the in-band telemetry handler is enabled or not. If this driver configuration setting is set to true, the telemetry handler is not created in the driver.

DriverManagerOverride

By default, the driver auto-detects which driver manager to use. However, if your specific situation calls for it, starting from ODBC driver version 3.9.0, you can override this auto-detection and manually specify which driver manager to use.

Possible values are: UnixODBC and iODBC.

If DriverManagerOverride is not specified, the driver uses auto-detection for the driver manage (call backtrace()) to get driver manager information. This is the default behavior.

The parameter works only on Linux and MacOS.

EnableAutoIpdByDefault

Set to false to configure the ODBC Driver to set SQL_ATTR_ENABLE_AUTO_IPD to false (which is the default value in the ODBC standard).

Otherwise, by default, the ODBC Driver sets SQL_ATTR_ENABLE_AUTO_IPD to true for compatibility with third-party tools.

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

EnablePidLogFileNames

Set to true to include the process ID in the name of the log file. For example, if the process ID is 7394, the log files will be named:

• snowflake_odbc_connection_7394_0.log

• snowflake_odbc_generic7394_0.log

• snowflake_odbc_curl_7394.dmp

You can set this parameter to prevent different processes from overwriting the same log files. Each process will generate its own set of log files.

By default, the value of this parameter is false.

This parameter was introduced in version 2.22.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

You can override this value for specific cases by setting the corresponding get_size_threshold connection parameter.

KeepLeadingTrailingZeros

Determines how leading or trailing zeros in numbers formatted as string values are handled. By default, the parameter is set to true, which means the driver retains any leading or trailing zeros. Set the parameter to false to remove leading or trailing zeros, for example:

• 0.23 is changed to .23

• 7.00 is changed to 7

LogFileCount

Sets the maximum number of log files to keep before rotating older files to make room for new log files.

LogFileSize

Specifies the maximum size, in bytes, of a log file. When a log file reaches the specified size, the ODBC driver automatically creates a new log file.

Default is 20971520.

LogLevel

Specifies the level of detail logged for clients that use the ODBC driver:

• 0 = Off

• 1 = Fatal

• 2 = Error

• 3 = Warning

• 4 = Info

• 5 = Debug

• 6 = Trace

LogPath

Specifies the location of the Snowflake log files for clients that use the ODBC driver.

MapToLongVarchar

Specifies the length of a string at which to begin mapping string values to an ODBC SQL_LONGVARCHAR data type instead of the default ODBC SQL_CHAR or SQL_VARCHAR data types.

• < 0 (or unset): Maps string values in their default ODBC data types. Default = -1.

• >= 0: Specifies the maximum number of string characters to map to default ODBC string data types. All strings larger than this value are mapped to SQL_LONGVARCHAR.

You can also specify this parameter as a connection parameter. (See the instructions for setting the parameters in Windows, macOS and Linux.) If set both as 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.24.3 of the ODBC Driver.

NoExecuteInSQLPrepare

Set to true to configure the ODBC Driver to use the standard ODBC behavior when passing DDL statements (such as CREATE and DROP) to SQLPrepare() and SQLExecute().

In Snowflake, by default, when you pass a DDL statement to SQLPrepare(), the ODBC Driver sends the statement to the data source for execution (not preparation). When you pass a DDL statement to SQLExecute(), the ODBC Driver does not send the statement to the data source.

If you set NoExecuteInSQLPrepare to true, the ODBC Driver follows the standard ODBC behavior. Calling SQLPrepare() sends the statement to the data source for preparation (not execution). Calling SQLExecute() sends the statement to the data source for execution.

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

NoProxy

Specifies the hostname patterns to bypass the proxy server (e.g. .amazonaws.com to bypass Amazon S3 access).

Note

The Snowflake ODBC driver passes the NoProxy value to the curl option CURLOPT_NOPROXY.

The format of the NoProxy value can be found CURLOPT_NOPROXY explained”.

Proxy

Specifies a proxy server in the form of <host>:<port> for clients that use the ODBC driver.

Note

In Windows, entries for LogLevel and LogPath are created and populated with default values when the ODBC driver is installed; however, an entry for Proxy is not created during install. To specify a proxy to use with the driver, you must manually add the entry to the driver registry key.

To bypass the proxy for one or more IP addresses or URLs, add the NoProxy parameter.

SSLVersion

Specifies the minimum SSL/TLS version to use when initiating TLS handshake. The values correspond to libcurl’s capabilities. For more information, see CURL_SSLVERSION_* entries in CURLOPT_SSLVERSION explained.

Possible values: one of TLSv1, SSLv2, SSLv3, TLSv1_0, TLSv1_1, TLSv1_2, TLSv1_3 (default: TLSv1_2).

Snowflake recommends leaving this setting at its default when you don’t have a very specific need to change it.

SSLVersionMax

Specifies the maximum SSL/TLS version to use when initiating TLS handshake. The values correspond to libcurl’s capabilities. For more information, see CURL_SSLVERSION_MAX_* entries in CURLOPT_SSLVERSION explained.

Possible values: one of TLSv1_0, TLSv1_1, TLSv1_2, TLSv1_3 (default: TLSv1_3).

Snowflake recommends leaving this setting at its default when you don’t have a very specific need to change it.

Connection parameters¶

Connecting using the connections.toml file¶

The ODBC driver lets you add connection definitions to a connections.toml configuration file. A connection definition refers to a collection of connection-related parameters. The driver currently supports TOML version 1.0.0.

For more information about toml file formats, see TOML (Tom’s Obvious Minimal Language).

The connection string containing only the Driver parameter tells the driver to look for the connection configuration within the predefined (default) files. The ODBC driver looks for the connections.toml file in the following locations, in order:

• If a ~/.snowflake directory exists on your machine, ODBC uses the ~/.snowflake/connections.toml file.

• Location specified in the SNOWFLAKE_HOME environment variable.

• Otherwise, ODBC uses the connections.toml file in the one of the following locations, based on your operating system:

  • Linux: ~/.config/snowflake/connections.toml, but you can update it with XDG vars

  • Windows: %USERPROFILE%\AppData\Local\snowflake\connections.toml

  • Mac: ~/Library/Application Support/snowflake/connections.toml

You can generate the basic settings for the TOML configuration file in Snowsight. For information, see Configuring a client, driver, library, or third-party application to connect to Snowflake.

If you want to switch between multiple existing connections, you can configure them in the connections.toml file. The default key is default, but you change the name of the default connection by setting the SNOWFLAKE_DEFAULT_CONNECTION_NAME shell environment variable.

The following sample connections.toml files defines two connections:

[default]
account = 'my_organization-my_account'
user = 'test_user'
password = '******'
warehouse = 'testw'
database = 'test_db'
schema = 'test_odbc'
protocol = 'https'
port = '443'


[aws-oauth-file]
account = 'my_organization-my_account'
user = 'test_user'
password = '******'
warehouse = 'testw'
database = 'test_db'
schema = 'test_odbc'
protocol = 'https'
port = '443'
authenticator = 'oauth'
token_file_path = '/Users/test/.snowflake/token'

Specifying parameters in a connection string¶

You can specify connection parameters as name-value pairs in a connection string, using an equals sign (=) between each parameter and value, and using a semicolon (;) between parameters. For example:

driver={SnowflakeDSIIDriver};server=myorganization-myaccount.snowflakecomputing.com;uid=myloginname;pwd=mypassword;database=mydatabase;schema=myschema;warehouse=mywarehouse;role=myrole;...

You can generate the basic connection string in Snowsight. For information, see Configuring a client, driver, library, or third-party application to connect to Snowflake.

Verifying the network connection to Snowflake with SnowCD¶

After configuring your driver, you can evaluate and troubleshoot your network connectivity to Snowflake using SnowCD.

You can use SnowCD during the initial configuration process and on-demand at any time to evaluate and troubleshoot your network connection to Snowflake.

Connecting through a proxy server¶

The instructions for configuring a proxy server connection depend on your operating system and driver version:

Using single sign-on (SSO) for authentication¶

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.

Using multi-factor authentication¶

Snowflake supports caching MFA tokens, including combining MFA token caching with SSO.

For more information, see Using MFA token caching to minimize the number of prompts during authentication — optional.

Using key-pair authentication¶

The ODBC driver supports key pair authentication and key rotation.

1. To start, complete the initial configuration for key pair authentication as shown in Key-pair authentication and key-pair rotation.

2. Modify the data source name (DSN) entries for the driver. For information about the DSN entries, see the appropriate topic for your operating system:

   • Installing and configuring the ODBC Driver for Linux

   • Installing and configuring the ODBC Driver for Windows

   Add the following (case-sensitive) parameters:

   AUTHENTICATOR = SNOWFLAKE_JWT

   Specifies to authenticate the Snowflake connection using key pair authentication with JSON Web Token (JWT).

   JWT_TIME_OUT = integer

   Optional. Specifies the length of time Snowflake waits to receive the JWT (in seconds) before timing out. If that happens, authentication fails and the driver returns an Invalid JWT token error. To resolve repeated occurrences of the error, increase the parameter value. Default: 30

   PRIV_KEY_FILE = path/rsa_key.p8

   Specifies the local path to the private key file you created (i.e. rsa_key.p8).

   The value set in DSN can be overridden by calling the SQLSetConnectAttr() function. For more details, see Snowflake-specific behavior of the SQLSetConnectAttr function.

   PRIV_KEY_FILE_PWD = <password>

   Specifies the passcode to decode the private key file.

   This parameter should be set only if the parameter PRIV_KEY_FILE is also set.

   The value set in DSN can be overridden by calling the SQLSetConnectAttr() function. For more details, see Snowflake-specific behavior of the SQLSetConnectAttr function.

3. Save the settings.

Using the OAuth 2.0 Authorization Code flow¶

The OAuth 2.0 Authorization Code flow is a secure method for a client application to obtain an access token from an authorization server on behalf of a user, without revealing the user’s credentials.

To enable the OAuth 2.0 Authorization Code flow:

1. Set the authenticator connection parameter to oauth_authorization_code.

2. Set the following OAuth connection parameters:

   • OAUTH_CLIENT_ID: Value of the client id provided by the identity provider for Snowflake integration (Snowflake security integration metadata).

   • OAUTH_CLIENT_SECRET: Value of the client secret provided by the identity provider for Snowflake integration (Snowflake security integration metadata).

   • OAUTH_AUTHORIZATION_URL: Identity provider endpoint supplying the authorization code to the driver. When Snowflake is used as an identity provider, this value is derived from the server or account parameters.

   • OAUTH_TOKEN_REQUEST_URL: Identity provider endpoint supplying the access tokens to the driver. When Snowflake is used as an identity provider, this value is derived from the server or account parameters.

   • OAUTH_SCOPE: Scope requested in the identity provider authorization request. By default, it is derived from the role. When multiple scopes are required, the value should be a space-separated list of multiple scopes.

   • OAUTH_REDIRECT_URI: URI to use for authorization code redirection (Snowflake security integration metadata). Default: http://127.0.0.1:{randomAvailablePort}.

Using the OAuth 2.0 Client Credentials flow¶

The OAuth 2.0 Client Credentials flow provides a secure way for machine-to-machine (M2M) authentication, such as the Snowflake Connector for Python connecting to a backend service. Unlike the OAuth 2.0 Authorization Code flow, this method does not rely on any user-specific data.

To enable the OAuth 2.0 Client Credentials flow:

1. Set the authenticator connection parameter to oauth_client_credentials.

2. Set the following OAuth connection parameters:

   • OAUTH_CLIENT_ID: Value of the client id provided by the identity provider for Snowflake integration (Snowflake security integration metadata).

   • OAUTH_CLIENT_SECRET: Value of the client secret provided by the identity provider for Snowflake integration (Snowflake security integration metadata)

   • OAUTH_TOKEN_REQUEST_URL: Identity provider endpoint supplying the access tokens to the driver. When Snowflake is used as an identity provider, this value is derived from the server or account parameters.

   • OAUTH_SCOPE: Scope requested in the identity provider authorization request. By default, it is derived from the role. When multiple scopes are required, the value should be a space-separated list of multiple scopes.

Authenticating with a programmatic access token (PAT)¶

Programmatic access token (PAT) is a Snowflake-specific authentication method. The feature must be enabled for the account before usage (see the Prerequisites for more information). Authentication with PAT doesn’t involve any human interaction.

Authenticating with workload identity federation (WIF)¶

Workload identity federation provides a service-to-service authentication method for Snowflake. This method enables applications, services, or containers to authenticate with Snowflake by leveraging their cloud provider’s native identity system, such as AWS IAM, Microsoft Entra ID, or Google Cloud service accounts. This approach eliminates the need for managing long-lived credentials and simplifies credential acquisition compared to other methods like External OAuth. Snowflake connectors are designed to automatically obtain short-lived credentials from the platform’s identity provider.

To enable the workload identity federation authenticator, do the following:

1. Set the authenticator connection parameter to WORKLOAD_IDENTITY.

2. Set the workload_identity_provider connection parameter to AWS, AZURE, GCP, or OIDC, based on your platform.

3. For OpenID Connect (OIDC), specify the token connection parameter.

Managing log files¶

To help you to track issues that might arise, you can enable logging in the ODBC driver. The ODBC driver provides the following configuration options that you can use to set up logging and manage log files:

• EnablePidLogFileNames: Adds the process ID to the name of a log file.

• LogFileCount: Sets the maximum number of saved log files.

• LogFileSize: Specifies the maximum size of a log file.

• LogLevel: Specifies the types of information to log.

• LogPath: Sets the location for log files.

You can use these parameters to manage how you name, store, and rotate log files. You can specify how large and how many log files you want to keep before replacing them with newly-created log files. The following example appends the process ID to file names to ensure uniqueness, sets the maximum file size to 30MB, and keeps the 100 most recent log files.

EnablePidLogFileNames = true  # Appends the process ID to each log file
LogFileSize = 30,145,728      # Sets log files size to 30MB
LogFileCount = 100            # Saves the 100 most recent log files

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

Verifying the OCSP connector or driver version¶

Snowflake uses OCSP to evaluate the certificate chain when making a connection to Snowflake. The driver or connector version and its configuration both determine the OCSP behavior. For more information about the driver or connector version, their configuration, and OCSP behavior, see OCSP Configuration.

OCSP response cache server¶

Snowflake clients initiate every connection to a Snowflake service endpoint with a “handshake” that establishes a secure connection before actually transferring data. As part of the handshake, a client authenticates the TLS certificate for the service endpoint. The revocation status of the certificate is checked by sending a client certificate request to one of the OCSP (Online Certificate Status Protocol) servers for the CA (certificate authority).

A connection failure occurs when the response from the OCSP server is delayed beyond a reasonable time. The following caches persist the revocation status, helping alleviate these issues:

• Memory cache, which persists for the life of the process.

• File cache, which persists until the cache directory (e.g. ~/.cache/snowflake or ~/.snowsql/ocsp_response_cache) is purged.

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

  Important

  If your server policy denies access to most or all external IP addresses and web sites, you must allowlist the cache server address to allow normal service operation. The cache server hostname is ocsp*.snowflakecomputing.com:80.

  If you need to disable the cache server for any reason, set the SF_OCSP_RESPONSE_CACHE_SERVER_ENABLED environment variable to false. Note that the value is case-sensitive and must be in lowercase.

If none of the cache layers contain the OCSP response, the client then attempts to fetch the validation status directly from the OCSP server for the CA.