Page History
...
| 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 |
| 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. |
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
...
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:
...