CODEWITHSUNDEEP
odbcsnowflake-cloud-data-platform
user14328853
user14328853

Reputation: 434

odbc.ini file configuration for when using key value pair (not PWD)

Reading documentation here.

I would like to use an odbc connection with Snowflake. I'm already able to get this working by adding .odbc.ini to my home directory with a configuration that looks similar to this:

[snowflake]
Description=SnowflakeDB
Driver=SnowflakeDSIIDriver
Locale=en-US
SERVER=ourco.us-east-1.snowflakecomputing.com
PORT=443
SSL=on
ACCOUNT=ourco.us-east-1
UID=MY_NAME
PWD=$MyPassword

This works. With this config, I'm able to create a connection and query our database.

However I would like to use odbc with keys instead of my password. Followed guide here on creating keys and added them within SF interface.

I then updated my odbc.ini file:

[snowflake]
Description=SnowflakeDB
Driver=SnowflakeDSIIDriver
Locale=en-US
SERVER=ourco.us-east-1.snowflakecomputing.com
PORT=443
SSL=on
UID=MY_NAME
ACCOUNT=ourco.us-east-1
PRIV_KEY_FILE=/home/rstudio-blah/keys/rsa_key.p8 # this is where I stored the key
SNOWSQL_PRIVATE_KEY_PASSPHRASE=potatoes{ # not my real pwd

When attempting to connect with this set up I got error message:

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Snowflake][DSI] (20032) Required setting 'PWD' is not present in the connection settings.

I tried adding PWD= just trying to trick it with a null value but then got:

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Snowflake][Snowflake] (31) Password not found.

I then tried adding AUTHENTICATOR=SNOWFLAKE_JWT which gave error:

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Snowflake][Snowflake] (44) Error finalize setting: Marshaling private key failed.

How can I connect via odbc using a key value pair as opposed to a PWD?

Upvotes: 2

Views: 3159

Answers (1)

Richard
Richard

Reputation: 722

Found this post when trying to solve a similar issue with Snowflake and Posit (RStudio). I noticed most of it it's already in the comments but, in summary this worked for me:

at odbc.ini

[snowflake]
Description=       SnowflakeDB
Driver=            SnowflakeDSIIDriver
SERVER=            ourco.us-east-1.snowflakecomputing.com
PRIV_KEY_FILE=     /home/rstudio-blah/keys/rsa_key.p8 
AUTHENTICATOR=     SNOWFLAKE_JWT
PRIV_KEY_FILE_PWD= definedPassPhrase

Couple of additional notes/learnings from my side the path and file need to be reachable and readable by the user that will be executing the ODBC connection, do not configure things with root.

In my case I used an Active Directory service account to connect which was linked to the key pair, this needs to also be linked at Snowflake:

ALTER USER "[email protected]" SET
RSA_PUBLIC_KEY='YOURPUBLICKEY';

Double quotes for the user, single quotes for the public key value

Upvotes: 0

Related Questions