Keypair JDBC Authentication for Snowflake

Ignition
,
1

Is keypair authentication for a JDBC connection supported? Specifically with regard to Snowflake

Snowflake JDBC Docs

I'm not sure if I am doing something syntactically incorrect or if the way Ignition is setup does not allow for passing a file path in the connect URL property. Depending on what permutation of characters I put in, I either get an error that the private key provided is invalid or

Cannot create JDBC driver of class 'net.snowflake.client.jdbc.SnowflakeDriver' for connect URL

2

I should give further context that when using a different basic auth service account, I can connect and the configured Translator / connect strings are all good to go

3

You'll be the trailblazer figuring out if it works or not.

Based on this from the docs, I would guess it's possible since you can configure connection properties on the DB connection:

Next, choose one of the following three options to configure either the JDBC connection properties or the JDBC connection string.

Specify the private key via the privateKey property in the connection properties.

Specify the private key file name and password for that file as separate properties in the connection properties.

Specify the private key file name and password for that file as part of the connection string.

You'll have to share some actual config values and error messages for further guidance.

4

Got it, love being the first haha. I'll try a few more things and if I don't get anywhere, I'll upload specific error messages and connect URL / extra property values

5

For any future explorers, this did end up working. It changed status overnight, which makes me think there was some form of refresh of credentials or other configuration work on the Snowflake side of the exchange.

But the principle of using a key file and using the keypair Snowflake authentication mechanism with the JDBC driver in Ignition is good.

1 Like
6

Can you provided some details on how you ended up making this work? I'm trying to get this working now and I'm struggling.

7

Hi Jared, while I may only be partially able to help due to not having visibility into the Snowflake side of the configuration for the project in question, I can highlight what we did on the Ignition side. Some of the settings may not be 100% necessary

  1. I followed the IA documentation's instructions for deriving a translator for Snowflake after installing the relevant files.
  2. Connect URL

jdbc:snowflake://YOURROOTSNOWFLAKEURL/?authenticator=snowflake_jwt&db=YOURSNOWFLAKEDB&TRACING=ALL&private_key_file=YOURPATHTOPRIVATEKEYFILE&private_key_pwd=YOURPRIVATEKEYPASSWORD

Note that the Ignition service must have file access to that location.

  1. Connection Props:

CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=TRUE;JDBC_QUERY_RESULT_FORMAT=JSON;DEFAULT_ROLE="YOURROLENAME";DEFAULT_WAREHOUSE="YOURWAREHOUSENAME";

Let me know if that helps