Page History
| Table of Contents | ||
|---|---|---|
|
| Tip |
|---|
In Enablement Pack versions greater than the following, the use of SnowSQL |
...
* has been removed in favor of ODBC only, this change allows for a single point of authentication configuration for Snowflake and RED.
For earlier versions of RED, the EP, or for existing PowerShell scripts which you do not want to rebuild, consider |
| Tip |
|---|
| 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
| Info |
|---|
*Note: Snowflake have deprecated SnowSQL in favor of Snowflake CLI, but RED only requires ODBC alone. |
| Info |
|---|
| 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 The following guide 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 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).
...
SnowSQL Key Pair
Connecting through SnowSQL | Snowflake Documentation
...
authenticator= SNOWFLAKE_JWTSet either in the snowsql.conf file or as a command line parameter.
Set via RED - can 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 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_PASSPHRASESet via RED - can 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 configuration and test:
Add these snowsql.conf entries at the default level (or under a named connection):
| Code Block | ||
|---|---|---|
| ||
accountname = my_org username = my_user_name authenticator = snowflake_jwt private_key_path = C:\Users\Administrator\snowflake_rsa_key.p8 |
...
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.
...
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.
...
Gathering your accounts configuration settings.
...
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
| Info |
|---|
When WhereScape originally wrote the PowerShell 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. Now all the features that RED utilized SnowSQL for can be performed through the Snowflake ODBC driver directly. |
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.
...