Tableau - Snowflake key-pair authentication

How to give Tableau the keys to the Snowflake warehouse?

Connecting Tableau to Snowflake using key-pair authentication

Since Biztory’s recent announcement about partnering up with Snowflake and Fivetran, our goal is to share content about these tools as well. In this blog we’ll write about Tableau as well as Snowflake (let’s call it Biztory’s sweet spot).

How can we use key-pair authentication to connect Tableau to Snowflake?

1. Using the ODBC parameters

When opening Tableau Desktop and choosing Snowflake as a data source, the following screen is prompted.

Obviously it’s possible to authenticate using the following protocols:

• Username and Password

• SAML IdP

• OAuth.

Clearly, an option to authenticate using key-pairs is not within that list. When going through the documentation of Snowflake, it clearly states that it’s possible though.

So how can we use key-pairs to connect to Snowflake using the Tableau Desktop interface?

At the bottom part of the authentication screen, it’s possible to Enter custom driver parameters. These parameters can be selected to pass additional configuration parameters to Snowflake if needed. Using this section will give us the possibility to use key-pairs. The below screenshot shows how to configure the custom driver parameters section.

These parameters are used to establish the connection to Snowflake using key-pairs:

• Server, Role: These are the standard server and role (optional) you want to select even with a username/password combination.

• Authentication: Username & Password can be selected although that does not really cover completely the way of authentication.

• Username: Enter your username.

• Password: This section can be left blank. The passphrase for the keys will be specified in the custom section.

• AUTHENTICATOR: This should be specified in the custom section and should state SNOWFLAKE_JWT. This stands for Snowflake JSON Web Token and warns Snowflake that authentication with key-pairs is used.

• PRIV_KEY_FILE: Mention the path to the keys on your computer. This can be a .pem or a .p8 file.

• PRIV_KEY_FILE_PWD: Indicate the passphrase here if one is required. If none is required, you can remove this parameter.

Other parameters can be set too. These can be found in the Snowflake documentation.

1. Using a .TDC file

Tableau also supports the use of .tdc files to tune ODBC connections. This file will recognise the connection type (fe Snowflake) and will overwrite the given parameters with the parameters defined in the .tdc file. More information about .tdc files can be found here.

An example use case of these .tdc files is automation. It’s possible to give Tableau Desktop developers the possibility to log in to Snowflake using named credentials (username & password). When they upload their work to Tableau Server, where a .tdc is stored, the connection string will be changed and it is possible to automatically assign a technical user on Tableau Server that uses key-pair authentication.

The content of this .tdc file needs to be the following (after changing the credentials).

<connection-customization class='snowflake' enabled='true' version='18.1'>
<vendor name='snowflake' />
<driver name='snowflake' />
<customizations>
<customization name='odbc-connect-string-extras' value='UID=USERNAME;PWD= ;AUTHENTICATOR=SNOWFLAKE_JWT;PRIV_KEY_FILE=PATH-TO-KEY-FILE;PRIV_KEY_FILE_PWD=PASSPHRASE' />
</customizations>
</connection-customization>

Save the above content as a file with .tdc as extension locally in the Datasources folder in the Tableau Repository folder. This will be applicable to when working with Tableau Desktop.

If the .tdc file will be used on Tableau Server, the following documentation is recommended. The location of where to save the file, is depending on the operating system and Tableau Server version.