key pair connection with snowflake

[ericfreitas3]

In snowflake recents upgrade, for security, it was included a new feature that classifies users into personal access or service access, users with personal access are forced to use MFA and users with service access are forced to use a RSA key pair connection.

Until then i've been trying to find a way to connect trino to snowflake using service access (key pair), but the connector doesnt give that option and i didnt find any PR about it, today im using a legacy definition of user in snowflake because of that, there is another way? I think it will be a great advance for snowflake users who work with trino.

[hashhar]

cc: @dprophet

[ebyhr]

You can use connection-url as the workaround. I tried it a few month ago and it worked well.
Note that the value will be printed in bootstrap.

[bluck7]

Can you provide an example url with private key path and passphrase?

[ericfreitas3]

i've used like this: connection-url=jdbc:snowflake://XXXXXXXXXXXXX&private_key_path=XXXXXXXXXX i've tested without passphrase but i wil try with it.

[bluck7]

Thank you! I finally got it to work using private_key_path and private_key_pwd

[ericfreitas3]

your key format is p8?

[ericfreitas3]

i've tried to use the connection url but im getting this error:

Query 20241206_174018_00134_28n39 failed: Connection string is invalid. Unable to parse.

i've used like this: connection-url=jdbc:snowflake://XXXXXXXXXXXXX&private_key_path=XXXXXXXXXX

[ericfreitas3]

it almost worked, now im getting this error:

failed: Private key provided is invalid or not supported

tried to convert to .PEM but also doesnt work.

thats my url now: jdbc:snowflake://XXXXXXXXX.snowflakecomputing.com/?private_key_file=XXXXXXXXXXXXX&private_key_pwd=XXXXXXXXXXXXXX

[rponsaerts]

perhaps try first with the decrypted private key.

I assume you have the other parameters as well in your snowflake.properties catalog file:

connector.name=snowflake
connection-url=jdbc:snowflake://<account>.snowflakecomputing.com/?private_key_file=/etc/trino/my_privkey.crt
connection-user=<user>
snowflake.account=<account>
snowflake.role=<role>
snowflake.database=<database>
snowflake.warehouse=<warehouse>

[rponsaerts]

Also, looks like you need to replace private_key_pwd in the url by private_key_file_pwd.

[ericfreitas3]

connector.name=snowflake
connection-url=jdbc:snowflake://XXXXXXXXXXXX.snowflakecomputing.com/?private_key_file=rsa_key_service.p8&private_key_file_pwd=XXXXXXXXXXXX
connection-user=XXXXXXXXXXXX
connection-password=XXXXXXXXXXXX
snowflake.account=XXXXXXXXXXXX
snowflake.database=XXXXXXXXXXXX
snowflake.role=XXXXXXXXXXXX
snowflake.warehouse=XXXXXXXXXXXX

this is the structure of my catalog (adjusted after the last comment), im getting this error:

failed: Private key provided is invalid or not supported

i'm using this key in another ingestion proccess (snowpark), also generated a .PEM version of it and it gives the same error.

[harsh-im]

were you able to resolve this @ericfreitas3 ?

[TrungHuynhPB]

I got it to work as I have to create a new key (.p8) without encrypted password.

Adding Snowflake Bouncy Castle options to jvm.config:
-Dnet.snowflake.jdbc.enableBouncyCastle=TRUE
-Dorg.bouncycastle.asn1.allow_unsafe_integer=true

and also in docker container settings for Trino:
environment:
- _JAVA_OPTIONS=-Dfile.encoding=UTF-8 -Dnet.snowflake.jdbc.enableBouncyCastle=TRUE -Dorg.bouncycastle.asn1.allow_unsafe_integer=true

My .properties:
connector.name=snowflake
connection-url=jdbc:snowflake://.snowflakecomputing.com/?user=&warehouse=<WH_name>&db=&schema=&private_key_file=/secrets/rsa_key.p8
connection-user=user
connection-password=na #not use it but still keeps the var here
snowflake.account=account
snowflake.database=DB_name
snowflake.role=RL_name
snowflake.warehouse=WH_name