Snowflake Authentication with RED

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

Setup Tips

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: 

• If using User DSN's then a Scheduler on the same machine running under 'Local System' will not have access to the DSN. Solution: configure a System DSN for the Scheduler service, or move the service to a specific windows user and configure a User DSN for the service user.
• If the Scheduler is on a remote machine, repeat the DSN authentication setup steps on the scheduler machine,
• If also using SnowSQL then the same considerations above should be applied to SnowSQL authentication setup, or apply the WslPowershellCommon module workaround on each machine running RED or the RED Scheduler.

Note: Some system level changes on the Scheduler machine may not be pickup up until the service is restarted.

Snowflake Authentication Methods Supported by RED

The guides for each authentication type below are located here:

• Snowflake ODBC
• SnowSQL

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.

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)

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, nor in RED 9.0.2.3+ Legacy 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

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, nor in RED 9.0.2.3+ Legacy Connect).