Introduction
This a guide to authenticating to Snowflake with RED, it covers how to configure RED with the different authentication types but does not go into detail of Snowflake setup steps. For advanced Snowflake setup, including ODBC DSN registry settings, please refer to Snowflake's documentation.
This guide is based on these versions of Snowflake ODBC driver and SnowSQL, in earlier versions some options and settings may not be available or must be set using a different method, please refer to Snowflake documentation for your specific version.
Versions used in this guide:
- Snowflake ODBC driver version 3.10
- SnowSQL version 1.4
Snowflake Authentication Methods Supported by RED
The guides for each authentication type below are located here.
Authentication type | RED Advanced Connect | RED Legacy Connect |
|---|---|---|
Key Pair | Fully supported | Partially supported |
MFA | Fully supported | Partially supported |
PAT | Fully supported | Partially supported |
Other Snowflake authentication methods may also work with RED by following similar configuration methods outlined in this guide.
RED Legacy Connect vs Advanced Connect
The term ‘Legacy Connect’ in RED refers to the underlying ODBC Connection library used in earlier versions of RED which only accepted combinations of DSN, USER and PASSWORD. This connection method was an older ODBC standard and newer ODBC connection standards allow for full control of connection strings to support the wide variety of authentication types each ODBC driver implements.
In RED 9.0+ and RED 10.2+ the newer connection method was adopted and the feature was termed ‘Advanced Connect’. This feature allowed full control of ODBC connection strings in RED and the introduction of RED Profiles which securely store credentials outside of the RED metadata repository allowing every user of RED to have their own personal set of database credentials.
RED versions 8.6.x.x and below use Legacy Connect, as well as RED 10.0 - 10.1, therefore in order to work with emerging authentication types on these versions some workarounds are required.
Snowflake ODBC Connections - Legacy Connect
The following applies to RED Template Types: Python and PowerShell
Snowflake ODBC DSN configurations need to contain all the parameters required to connect including password or private key file passphrase, unless using RED with Advanced Connect.
RED Legacy Connect limitations:
There is a 128 char limit on the Password field length so PAT’s and other long Tokens can’t be stored in the metadata (limit does not apply to RED connections using Advanced Connect).
Snowflake User must be specified in the RED connection, it can not be blank, therefore the Snowflake User must be shared when using RED Legacy Connect. Passwords can be blank depending on authentication type.
Connection strings can’t be adjusted so Snowflake connection attributes such as priv_key_file_pwd can’t be added by RED.
SnowSQL Connections - both Legacy and Advanced Connect
Note: Snowflake have deprecated SnowSQL in favor of Snowflake CLI
Background
When WhereScape originally wrote the templates for Snowflake the Snowflake ODBC driver had limited functionality so some of the operations required for loading, such as uploading files to Snowflake, were only available via SnowSQL. Later when WhereScape developed the Python based templates the newer Snowflake ODBC driver was able to perform all operations we required so the Python templates used ODBC exclusively. It is possible via a small code change in the WhereScape PowerShell common module to convert calls to snowsql into ODBC allowing existing PowerShell RED host scripts to connect to Snowflake with ODBC exclusively.
The following applies to RED Template Type: PowerShell only:
All required connection attributes need to be set in snowsql.conf file, SnowSQL command line parameters and environment variables.
RED Snowflake PowerShell Template Limitations
RED PowerShell scripts set SnowSQL environment variables but only cater for a limited set covering the requirements for user/pwd authentication.
If using a SnowSQL authentication method outside of user/pwd then those required settings must be specified in your snowsql.conf and any required password set as the appropriate environment variable.
RED does not provide a way to dynamically alter the command arguments for SnowSQL this means:
Only the default snowsql.conf can be used, since we can’t specify the SnowSQL connection name nor allow setting a different snowsql.conf path.
Therefore each Windows user would set up their own snowsql.conf file.
There is a 128 char limit on the Password field length so PAT’s and other long Tokens can’t be stored in the metadata (limit does not apply to RED connections using Advanced Connect).