Snowflake key-pair authentication and key file

[jjimenezRe]

I'm using the Snowflake ODBC Driver for Linux with the SNOWFLAKE_JWT authenticator.

This authenticator has a parameter called PRIV_KEY_FILE, which I use to point to the file containing the private key, inside my dbConnect() calls.

From what I understand its not possible to pass key pair itself directly to dbConnect() , just the file name containing the key.

Since I'm pulling the key pair from a vault and prefer not to write the key to a file, I tried using the attribute called SQL_SF_CONN_ATTR_PRIV_KEY .

I see it's not currently supported after checking the attributes listed here.

[detule]

Hey there:
Thanks for writing up this use case - sounds like something we should attempt to support.
I'll look into it in the coming weeks.

[detule]

Hi -

Just a note that we haven't forgotten about this. Let me provide a bit more context:

• Docs you linked specify the connection attribute payload as being a pointer to an EVP_PKEY struct. I could very well be wrong in my interpretation here; there is very little in the way of actual documentation and zero examples that I could find on how to use this connection attribute.

• EVP_PKEY is an object from the openssl API. I don't think, when building our package, we want to explicitly link to the openssl libs (despite, I think, it being a defacto requirement for most (all?) odbc drivers at this point).

• Fortunately there is a very mature package:openssl. I thought maybe we could collaborate with them to ask them if they can, either as part of an existing call, or as a new method return the EVP_PKEY resource that we could then forward to snowflake.

• Before doing that, I thought we should test out if we are on the right track. Hacked package:openssl here to return the resource to the [R] user as an external pointer.

• Hacked together a branch of package:odbc to then set the attribute as part of a dbConnect call.

Wasn't successful in making it work - maybe you can spot if I am doing something wrong.

library(odbc)
library(openssl)

# Verify my private/public key is correctly set up.  This works:
> conn <- dbConnect(
  odbc::odbc(),
  dsn = "snowflake",
  uid = "<snip>",
  PRIV_KEY_FILE = "<path-to-file-on-disk>",
  authenticator = "SNOWFLAKE_JWT")
> 

# Attempt to use EVP_PKEY.  Doesn't work.
key <- read_key_hack("<path-to-file-on-disk>")
> conn <- dbConnect(
  odbc::odbc(),
  dsn = "snowflake",
  uid = "<snip>",
  attributes = list("sf_jwt_priv_key" = attributes(key)$evp_pkey),
  authenticator = "SNOWFLAKE_JWT")
Adding private key attribute
Error in `dbConnect()`:
! ODBC failed with error 00000 from [Snowflake][Snowflake].
✖  (42)
•  Error using private key: Public Key extract failed.
•

• Nature of the error makes me think we are on the right track here in terms of the format of the payload - at times past when I've passed a wrong payload to one of these complex attributes, I would get a segfault pretty reliably. However kind of at a loss as to what exactly I am doing wrong.

This might be worth reaching out to the driver vendor/snowflake for clarification on how exactly this attribute is supposed to work. In the meantime, I heard from the Posit team that maintains the pro drivers, that their vendor might be working on a feature to make it possible to pass the key as BASE64 encoded string. That would be easier to work with. Also the jdbc driver I think already has that feature; that would provide for a convenient way to establish a baseline.

I am out of time (and inspiration!) right now; but may pick this up later again. I'll also keep an eye open for the posit driver release (though I am not sure if there is a specific timeline).

Feel free to experiment with those branches in case you have some ideas.

[jjimenezRe]

I had a similar impression when I was reading their docs. I have opened a support case referencing this issue and asking more details about SQL_SF_CONN_ATTR_PRIV_KEY.

[jjimenezRe]

Hello again

It didn't work for me either the first time. I think I was just using the wrong key format.

I tried again today several times and it seems to be working!

Here's what I tried:

vault <- key_vault("***************", as_managed_identity=TRUE)
secret_key<- vault$secrets$get("***************")$value
writeLines(secret_key, "private_key")

key <- read_key_hack("private_key")

con <- dbConnect(
  odbc::odbc(),
  driver = "SnowflakeDSIIDriver"
  server = "***************.snowflakecomputing.com",
  uid = "***************",
  authenticator = "snowflake_jwt",
  attributes = list("sf_jwt_priv_key" = attributes(key)$evp_pkey),
)

The connection is successful, and the output:

Connection string is:  authenticator=snowflake_jwt;driver=SnowflakeDSIIDriver;server=***************.snowflakecomputing.com;uid=***************
Adding private key attribute

If it helps you this is what I'm using:

• 2048 RSA PEM formatted key
• OpenSSL 3.0.13 30 Jan 2024
• Snowflake ODBC driver v3.6.0

Also, they updated the docs with two more parameters ( Snowflake ODBC driver => v3.4.0 )

SQL_SF_CONN_ATTR_PRIV_KEY_CONTENT

SQL_SF_CONN_ATTR_PRIV_KEY_PASSWORD

https://docs.snowflake.com/en/developer-guide/odbc/odbc-api#snowflake-specific-behavior

[detule]

Excellent.

I saw the update to their docs, and was able to make this work using the SQL_SF_CONN_ATTR_PRIV_KEY_CONTENT attribute, without needing package:openssl or any hacks. Hopefully this is more robust to linking against different openssl versions (my box has 3.1.5) which is why, I suspect, the EVP_PKEY approach is working for you but not for me.

Should be able to put something together before EOD Sunday.

Thanks for reporting back!

[detule]

Alright the "clean" PR is up - if you get a chance to take it for a ride, please let me know if it works for your use case. I had a chance to test it locally with both encrypted and un-encrypted keys, as well as with both the OEM and pro drivers and found no isses.

[jjimenezRe]

Hello again,

@detule It works perfectly. I could use the sf_private_key attribute pointing to a copy of the private key in memory, and the connection was successful.

Thank you, good job!