Using DSN-less ODBC Connections from Alteryx and other software to the Snowflake Data Cloud

I’ve learned over the years that there are many situations when it is better to avoid setting up database connections in an odbc.ini file or using the Microsoft Windows ODBC control panel. I avoid setting up these data source names (DSN) because they can cause issues when you migrate content or share connections with other people. Instead, you can create “DSN-less connections” that provide all the necessary parameters in the connection string itself. I am going to provide instructions here specifically for connecting from Alteryx and R to Snowflake but you should be able to use similar connection strings with a large number of other software that uses ODBC. DSN-less connections also work for other databases including Oracle and Microsoft SQL Server.

Specifically for Alteryx, DSN-less connections to Snowflake make it much simpler to create and distribute connections from an Alteryx server to desktop clients and to publish workflows from a desktop client to the server. The server and clients must still all have the Snowflake ODBC 64-bit driver installed but you will not need to create a DSN in each computer’s ODBC settings. Especially when you will need to define multiple connections with different service accounts for different teams, it would become a big challenge to manually set up all the different DSN on the server and desktop computers.

Additional background information:

• You can download the Snowflake ODBC driver from here
• Snowflake’s documentation describes all the available parameters

Sample DSN-less Connection String for Managed Data Connections in Alteryx

The following connection string will allow you to more easily create managed data connections in a gallery that are portable between Alteryx desktop and Alteryx server. I recommended keeping the {} brackets so that you won’t have to worry about special characters. Some of these parameters are optional but you must provide the DRIVER, SERVER, and UID parameters. You must also provide the PWD parameter if you are using passwords to authenticate.

odbc:
DRIVER={SnowflakeDSIIDriver};
SERVER={myorganization-myaccount.snowflakecomputing.com};
UID={Username};
DATABASE={My_Snowflake_Database_Name};
SCHEMA={My_Snowflake_Schema};
WAREHOUSE={My_Snowflake_Virtual_Warehouse};
ROLE={My_Snowflake_Role};
AUTHENTICATOR={snowflake};
PWD={Password};

Some examples of how to use the AUTHENTICATOR parameter:

• Native Snowflake password: AUTHENTICATOR={snowflake};PWD={Password};. Alteryx has a feature that encrypts this password to allow IT administrators to set the password in the server without sharing it with end-users.
• Single-sign-on (SSO) connections from a desktop client (will not work on Alteryx Server): AUTHENTICATOR={externalbrowser};
• Key Pair authentication requires the private key file to be copied to the exact same location on the server and desktop computers to be portable.

AUTHENTICATOR={snowflake_jwt};
PRIV_KEY_FILE={path/rsa_key.p8};
PRIV_KEY_FILE_PWD={Private Key Passphrase};

DSN-less Connection String for R to Snowflake

First, Install ODBC and Verify it sees your Snowflake Driver

First, you should make sure that you have the latest ODBC R library installed from CRAN:

install.packages("odbc")
library(odbc)

Once you have the ODBC package installed and loaded in R, you should use the odbc::odbcListDrivers() command to check whether you can see a Snowflake database driver listed. If you see a Snowflake driver, you can proceed to the next step but if not, you should check the following and proceed once you can list the driver.

• If you are on Linux or MacOS, have you installed the unixODBC library? Depending on your package manager, that could be done using one of the following:

yum install unixodbc
apt-get install unixodbc
brew install unixodbc

• Have you installed the Snowflake driver? If you just installed unixODBC, you might want to reinstall the Snowflake driver so that it adds entries into the odbcinst.ini file. You should be able to identify the location of the odbcinst.ini file with the following command: odbcinst -j.
• If you still don’t see the drivers, try to copy the system-wide odbcinst.ini file to a user configuration file in your home directory. Based on the output of your odbcinst -j command, you may copy the system-wide file with one of the following:

cp /Library/ODBC/odbcinst.ini ~/.odbcinst.ini
cp /etc/odbcinst.ini ~/.odbcinst.ini

• Finally, if you still can’t see a driver listed, you can provide the exact location of your ODBC driver as the Driver in the next step. You may be able to find it in one of the following locations

/opt/snowflake/snowflakeodbc/lib/universal/libSnowflake.dylib
/usr/lib64/snowflake/odbc/lib/libSnowflake.so
~/snowflake_odbc/lib/libSnowflake.so

Finally, Connect R to Snowflake

Assuming you found a driver with the name “SnowflakeDSIIDriver” in the previous step, we can connect R to Snowflake DSN-less with the following command. You should notice that the exact same set of parameters is available as our earlier Alteryx connection string, including the authenticator.

conn <- DBI::dbConnect(
  odbc::odbc(),
  Driver = "SnowflakeDSIIDriver",
  server = "myorganization-myaccount.snowflakecomputing.com",
  uid = "Username", 
  database = "My_Snowflake_Database_Name",
  schema = "My_Snowflake_Schema",
  warehouse = "My_Snowflake_Virtual_Warehouse",
  role = "My_Snowflake_Role",
  authenticator = "snowflake",
  pwd = "Password"
)