Using `odbc::snowflake()` with `authenticator = "SNOWFLAKE_JWT"`

[daniepi]

Hi,
I am trying to connect to Snowflake using the new drv = odbc::snowflake(). It seems to expect both uid and pwd to be supplied at the same time. But what if I want to connect using authenticator = "SNOWFLAKE_JWT" and use priv_key_file and priv_key_file_pwd instead of uid and pwd? It still requires UID argument

conn <- DBI::dbConnect(
  drv = odbc::snowflake(),
  account = "account",
  authenticator = "SNOWFLAKE_JWT",
  priv_key_file = ".../rsa_key.p8",
  priv_key_file_pwd = "pass"
)

gives

Error in `DBI::dbConnect()`:
! ODBC failed with error 00000 from [Snowflake][DSI].
✖  (20032) Required setting 'UID' is not present in the connection settings.
ℹ From nanodbc/nanodbc.cpp:1150.

When I try

conn <- DBI::dbConnect(
  drv = odbc::snowflake(),
  account = "account",
  authenticator = "SNOWFLAKE_JWT",
  uid = "user"
  priv_key_file = ".../rsa_key.p8",
  priv_key_file_pwd = "pass"
)

it throws

Error in `DBI::dbConnect()`:
! Both `uid` and `pwd` must be specified to authenticate.
ℹ Or leave both unset to use ambient Snowflake credentials.

While

conn <- DBI::dbConnect(
  drv = odbc::snowflake(),
  account = "account",
  authenticator = "SNOWFLAKE_JWT",
  UID = "user"
  priv_key_file = ".../rsa_key.p8",
  priv_key_file_pwd = "pass"
)

just hangs.

Is there a way to use odbc::snowflake() with SNOWFLAKE_JWT? Or should I just continue using drv = odbc::odbc() instead? Thanks for help. :)

[hadley]

@atheriel

[simonpcouch]

Thanks for the issue, @daniepi! Could you try installing the PR at #891 with pak::pak("r-dbi/odbc#891") and let me know if that does the trick for you?

[daniepi]

Hi @simonpcouch. I can confirm it does not require pwd anymore. However it still hangs as opposed to when using odbc::odbc() with dsn= defined. But I guess that is something on my side. :)

[simonpcouch]

@atheriel are you able to reproduce the above hang with authenticator = "SNOWFLAKE_JWT" and the linked PR? Would be excited to get a fix in for this issue in the release next week, though I don't have a Redshift env set up to test against at the moment.

[atheriel]

I can't confirm the hang, but my test environment is different in an important way: it does not have a keypair with a password. This does work for me with the linked PR:

DBI::dbConnect(
  odbc::snowflake(),
  account = Sys.getenv("SNOWFLAKE_ACCOUNT"),
  authenticator = "SNOWFLAKE_JWT",
  uid = Sys.getenv("SNOWFLAKE_USER"),
  priv_key_file = "rsa_key.p8",
)

[daniepi]

I will check with keypair without password later today.

[daniepi]

I can confirm it works well with and without password protected keypair. Thanks for fix.