Access Snowflake using Snowsql without Password Prompt

There are many ways you can connect to Snowflake cloud data warehouse server. The options include Snowsql, JDBC and ODBC drivers, python connector, Spark connectors, etc. In this article, we will discuss how to access Snowflake using Snowsql without password prompt. We will also check Snowsql environment variables.

Password less access is useful when your application requires to connect to Snowflake frequently.

There are multiple options that you can use to achieve password less communication to Snowflake server.

Export Snowflake Database Server Environmental Variables
Add Credentials to Snowsql config file

Export Snowflake Database Server Environmental Variables

In order to access Snowflake database with no password prompt, you need to set up snowsql environmental variables.

This is one of the easiest ways you can connect to the Snowflake database without needing to enter a password on prompt every time you connect.

Following are the Snowsql environmental variables:

Environment Variable	Description
$SNOWSQL_ACCOUNT	Snowflake account name that is assigned to you.
$SNOWSQL_USER	Username to connect to Snowflake.
$SNOWSQL_PWD	Snowflake account password.
$SNOWSQL_DATABASE	Database to be connected
$SNOWSQL_SCHEMA	Schema to be connected
$SNOWSQL_ROLE	Role name to use.
$SNOWSQL_WAREHOUSE	Warehouse to use.
$SNOWSQL_HOST	Snowflake host address for the connection.
$SNOWSQL_PORT	Port number for the connection.
$SNOWSQL_PRIVATE_KEY_PASSPHRASE	Path to private key file in PEM format

Example to Connect Snowflake without Password Prompt

Following example demonstrates usage of Snowsql environment variables in Linux environment.

On the Windows command prompt, you can use the SET command to set environment variables.

# Export environment variables
$ export SNOWSQL_ACCOUNT=xyz112233.us-east-2
$ export SNOWSQL_USER=snuser
$ export SNOWSQL_PWD=Password@123
$ export SNOWSQL_DATABASE=demo_db
$ export SNOWSQL_SCHEMA=public

# Connect to snowsql
$ snowsql
* SnowSQL * v1.2.1
Type SQL statements or !help
snuser#COMPUTE_1@DEMO_DB.PUBLIC>

As you can see in the examples, Snowsql does not prompt for the password. It honors the values set in the environmental variables.

Add Credentials to Snowsql config file

This is another option that you can use to store credentials. Snowsql contains the config file. You can add connection property something like the one explained in following example.

Snowsql config file location.

Linux: ~/.snowsql/config
Windows – %USERPROFILE%/.snowsql/config

Following example demonstrates creating newConnection parameters that you can use with Snowsql.

[connections.newConnection]
accountname = xyz112233.us-east-2
username = snuser
password = Password@123
dbname = demo_db
schemaname = public

# Use -c for connection parameter
~$ snowsql -c newConnection
* SnowSQL * v1.2.1
Type SQL statements or !help
snuser#COMPUTE_1@DEMO_DB.PUBLIC>

Hope this helps 🙂