Page History

Table of Contents
maxLevel 2

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

...

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

...

• 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.

...

...

• 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).

Key Pair Authentication

Follow Snowflake setup instructions here: Key-pair authentication and key-pair rotation | Snowflake Documentation

Configure a Snowflake Key Pair DSN

If optionally storing password and private key file within the DSN, then it’s best to create a ‘USER’ DSN rather than ‘SYSTEM’ DSN otherwise anyone on this system will be able to connect using this DSN.

Providing key file password in the DSN and saving it will store the key file password in plain text in the registry.

Authenticator should be set to ‘SNOWFLAKE_JWT’

Example from Snowflake ODBC driver version 3.10

Image Removed

RED Connection Setup with Snowflake Key Pair

RED 10 and RED 9 using Advanced Connect

• User: your snowflake user

• Password: your Private Key Password (for encrypted key files)

• Connection String: dsn=$DSN$;user=$USER$;priv_key_file_pwd=$PASSWORD$;

The connection property ‘PRIV_KEY_FILE’ will be taken from the DSN, this is why it is a good idea to create a ‘USER’ level DSN so that each user on the system can have a different private key file (and pwd).

RED 8 or RED 9 or RED 10 without Advanced Connect

Without the Advanced Connect feature, added in RED 9.0 and RED 10.2, your only option is to enter all the details into your DSN’s, including the user, key file and key file password. Then in RED your Snowflake connections would only populate the User name field as the rest of the connection attributes will be taken from the DSN.

MFA Authentication

Snowflake MFA authentication is designed for browser based applications, but it can be used for other applications such as RED also. This method of authentication can not be used via the scheduler as it requires a user interaction.

Authenticating with MFA requires a second ‘factor’ in the authentication process, for RED applications this second factor should be an ‘Authenticator App’ which will either supply a Time-Based One Time Password (TOTP) or require you to accept a push notification on your mobile device.

If using this form of authentication with RED it is recommended that the Snowflake Account Level Parameter to allow caching of the MFA token is enabled, this prevents the user having to keep entering a new TOTP each time a connection is made. Without this feature enabled RED UI connections to Snowflake would be unusable until RED supports oAuth style refresh workflows (planned for RED 10.6).

To allow MFA caching set this account parameter:

ALTER ACCOUNT SET ALLOW_CLIENT_MFA_CACHING = TRUE;

Configure a Snowflake MFA DSN

When connecting to an MFA TOTP enabled Snowflake DSN you would need to supply the TOTP concatenated on to the end of the password, this requires an additional connection attribute 'passcodeInPassword=on' which you can not enter into the DSN dialog, therefore you can not create the initial connection to Snowflake using the built in Snowflake DSN Test function unless you added this parameter to the registry for the DSN. If using DUO push MFA method you do not need this attribute.

Set the DSN Authenticator to ‘username_password_mfa’

Example from Snowflake ODBC driver version 3.10

Image Removed

RED Connection Setup with Snowflake MFA

RED 10 and RED 9 using Advanced Connect

• User: your snowflake user

• TOTP based MFA:

  • Password: your password + your TOTP (MFA code)

  • Connection String: dsn=$DSN$;user=$USER$;pwd=$PASSWORD$;passcodeInPassword=on;

• Duo Push Notification

  • Password: your password

  • Connection String: dsn=$DSN$;user=$USER$;pwd=$PASSWORD$;

The connection property ‘authentication=username_password_mfa’ will be taken from the DSN, but this could also be entered in the connection string.

For TOTP the initial authentication process would be, when you are ready to browse a Snowflake connection in RED UI you would enter your password along with the currently active TOTP from your authenticator app appended to your password, once connected if the ALLOW_CLIENT_MFA_CACHING = TRUE on your organization's account you will not need to provide a new TOTP for a few hours on the same device.

RED 8 or RED 9 or RED 10 without Advanced Connect

MFA is not really feasible without the Advanced Connect feature since Legacy Connect requires shared credentials in RED, but if you only have a single user working with RED then you can follow this workaround:

Without the Advanced Connect feature, added in RED 9.0 and RED 10.2, your only option is to enter all the details into your DSN’s. Then in RED your Snowflake connections would populate the User name and optionally Password fields (if not entered in DSN) as the rest of the connection attributes will be taken from the DSN.

Since the Snowflake connection attribute 'passcodeInPassword=on' can not be entered in the DSN and we are not using Advanced Connect in RED, you would need to add this to the registry for this DSN.

If using TOTP based MFA, then the password field, as mentioned earlier, would need to contain the password and the current TOTP from your authenticator app when first using the connection within the RED session.

Programmatic Access Token

Programmatic Access Tokens can be created for a user which provide a way to connect that doesn’t require MFA, in this method a token is generated for the account and is then used in the password field of Snowflake connections.

Configure a Snowflake PAT DSN

All you need to do for PAT is simply provide your user and in the password field your PAT
Image Removed

RED Connection Setup with Snowflake PAT

RED 10 and RED 9 using Advanced Connect

• User: your snowflake user

• Password: your PAT

• Connection String: dsn=$DSN$;user=$USER$;pwd=$PASSWORD$;

RED 8 or RED 9 or RED 10 without Advanced Connect (10.0 - 10.1)

Without the Advanced Connect feature, added in RED 9.0 and RED 10.2, your only option is to enter the PAT directly into the DSN (or registry), USER DSN preferred. The PAT can’t be entered into the RED UI password field due to the length restrictions of the field when using Legacy Connect.

In RED you would only optionally enter the User name field, password should be left blank and will be picked up by the settings stored in the DSN.

SnowSQL configuration for WhereScape PowerShell based templates

Consider changing all RED SnowSQL calls to ODBC, to simplify authentication configuration, by using the method described here.

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.

Affects 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 RED Legacy Connect 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).

Configure SnowSQL Key Pair

Connecting through SnowSQL | Snowflake Documentation

Snowflake Key Pair auth with SnowSQL requires the following settings:

• authenticator = SNOWFLAKE_JWT

  • Set either in the snowsql.conf file or as a command line parameter.

  • Set via RED - can not be set in RED.

• private-key-path = Path to private key file.

  • Set in the snowsql.conf file, as a command line parameter.

  • Set via RED - can not be set in RED.

• private_key_passphrase= passphrase for protected private key file [required when private key is protected]

  • Set by environment variable only SNOWSQL_PRIVATE_KEY_PASSPHRASE

  • Set via RED - can not be set in RED OOTB, but can be set via HSL command-line hack.

• username = Snowflake User.

  • Set either in the snowsql.conf file, as a command line parameter or environment variable.

    • RED currently uses the environment variable method.

  • Set via RED - you can use the normal “Username” field in your connections.

Example:

Add these snowsql.conf entries at the default level (or under a named connection)

accountname = my_org username = my_user_name authenticator = snowflake_jwt private_key_path = C:\Users\Administrator\snowflake_rsa_key.p8

Add your environment variable for the private key passphrass (if required) and run a test:

set SNOWSQL_PRIVATE_KEY_PASSPHRASE=myP@ssw0rd snowsql -q "select 1"

Adding the private key passphrase environment variable

Since SNOWSQL_PRIVATE_KEY_PASSPHRASE environment variable is required for protected private keys any scripts using SnowSQL and Key Pair will need this variable set.

Method 1 - Adjusting the PowerShell Host Script Language Definition in RED

• Use the DSS User Password field of your Windows Runtime connection in RED to hold the key file passphrase. This will be set as WSL_PWD environment variable in scripts (only valid in RED 9.0.x.x and below).

  Image Removed

• Then adjust your PowerShell Host Script Language Definition in RED to have the following command that uses the DSS User Password to set SNOWSQL_PRIVATE_KEY_PASSPHRASE.

  PowerShell -ExecutionPolicy Bypass -Command "$env:SNOWSQL_PRIVATE_KEY_PASSPHRASE=$env:WSL_PWD; PowerShell -ExecutionPolicy Bypass -File '$SCRIPT_NAME$'"

Method 2 - Add the environment variable machine wide:

You could also add the environment variable at a machine level via Windows System Properties - Environment Variables

Method 3 - Start RED UI with a script that prompts for and sets the environment variable:

Use a simple batch or PowerShell script, launched via a desktop shortcut, that prompts for any required Snowflake environment variables then sets them for the session and launches RED under that session thus inheriting the environment variables set by the script.

Configure SnowSQL MFA

Theoretically MFA caching is possible with SnowSQL but this can be problematic to implement. Instead WhereScape recommends moving SnowSQL calls to ODBC via this method or choosing a different method of authentication.

Connecting through SnowSQL | Snowflake Documentation

Gathering your accounts configuration settings.

Configure SnowSQL PAT

Snowflake PAT authentication with SnowSQL requires the following settings:

• username = Snowflake User.

  • Set either in the snowsql.conf file, as a command line parameter or environment variable.

    • RED currently uses the environment variable method and sets SNOWSQL_USER.

  • Can be set via RED - you can use the normal “Username” field in your connections.

• password = Snowflake PAT.

  • Set either in the snowsql.conf file, as a command line parameter or environment variable.

    • RED currently uses the environment variable method and sets SNOWSQL_PWD.

  • Can be set via RED

    • Advanced Connect - you can use the normal “Password” field in your connections.

    • Legacy Connect - you must set this via snowsql.conf file due to field length limitation in Legacy Connect for RED

Migrate RED SnowSQL calls to Snowflake ODBC

Since all RED PowerShell Templates produce scripts which import the common module ‘WslPowershellCommon’ there is opportunity to change the behavior of all existing scripts such that calls to SnowSQL are instead converted to calls via ODBC, thus eliminating the need to configure authentication aspects in both SnowSQL and ODBC.

The common modules are usually installed here: ‘C:\ProgramData\WhereScape\Modules\WslPowershellCommon’ or in later Enablement Packs the common modules are stored as a host scripts in the RED metadata.

To convert all SnowSQL calls to ODBC, add this code to the WslPowershellCommon module on all machine running RED or the RED Scheduler:

...

• • , nor in RED 9.0.2.3+ Legacy Connect).