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:
Since RED Scheduler can run on a different machine and/or under a different user it is important to make sure that the service user the RED Scheduler is running under has access to the ODBC DSN which has been configured and tested for a given authentication type. Each machine running RED or the RED Scheduler will need to be configured to suit the desired authentication type.
Examples:
The guides for each authentication type below are located here:
Authentication type | RED Advanced Connect | RED Legacy Connect |
|---|---|---|
Fully supported | Partially supported | |
Fully supported | Partially supported | |
Fully supported | Partially supported |
Other Snowflake authentication methods may also work with RED by following similar configuration methods outlined in this guide. In RED 10.6 we will add a script based authentication feature that enables extended oAuth style workflows. |
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.
In order to benefit from the RED Advanced Connect feature, you should either be on RED 9 or RED 10.2+. RED 9 versions are the only versions to offer both Legacy and Advanced Connect feature. RED 10.2 and beyond only support Advanced Connect.
| RED Version | Legacy Connect | Advanced Connect |
|---|---|---|
| 8.6.x.x (or earlier) |  |  |
| 9.0.x.x | | |
| 10.0.x.x | | |
| 10.1.x.x | | |
| 10.2.x.x (or greater) | | |
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.
Note: Snowflake have deprecated SnowSQL in favor of Snowflake CLI |
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).