Connecting to Snowflake from R Workbench

[preethin93]

Hello,

Our Snowflake is setup as SSO login via Okta. We are having issues connecting to SnowFlake via Okta from a R session (in Workbench).

Scenario 1:

We have authenticator as Snowflake and this works FINE.

myconn <- DBI::dbConnect(odbc::odbc(), "SnowflakePRD", uid="[name@company.com]", PWD="secret_password", authenticator = "snowflake")

Scenario 2:

When we have the authenticator as Okta like this we run into issues:

con <- DBI::dbConnect(odbc::odbc(),

• "SnowflakePRD",

• "jdbc:snowflake://companyname.snowflakecomputing.com/?authenticator=https://companyname.okta.com/",

• "[username@companyname.com]")

Error in OdbcConnection(dsn = dsn, ..., timezone = timezone, timezone_out = timezone_out, :

all(has_names(args)) is not TRUE

We tried adding the time zone of the Workbench server (UTC) in the code, still the same error.

con <- DBI::dbConnect(odbc::odbc(),

• "SnowflakePRD",

• "jdbc:snowflake://companyname.snowflakecomputing.com/?authenticator=https://companyname.okta.com/",

• "[username@companyname.com]")

timezone=”UTC”,

timezone_out=”UTC”)

Error in OdbcConnection(dsn = dsn, ..., timezone = timezone, timezone_out = timezone_out, :

all(has_names(args)) is not TRUE

We recently got to know that we use external browser for authentication. Okta is in sync with Active directory. It validates with Active directory and logs us in.

For this case, does the user still need a password to log in?

Please let us know what are we missing?

Thank you

[Wolfpack2]

Have you tried using scenario 1 but changing the authenticator to your Okta URL? That’s how it allegedly works if you’re using native Okta. Here’s their documentation on the topic: Managing/Using Federated Authentication — Snowflake Documentation

[preethin93]

@preethin93

myconn <- DBI::dbConnect(odbc::odbc(), "SnowflakePRD", uid="[name@company.com]", PWD="secret_password", authenticator = "snowflake")

Thank you for the response. I tried that. But get this error Error: nanodbc/nanodbc.cpp:1021: 00000: [RStudio][Snowflake] (30)
Failed to connect to okta. Error code=401

When we use Okta as authenicator are we still supposed to give password? As it doesn't accept an input with authenticator URL and no password.

[Blair09M]

@preethin93 welcome to the community! Thank you for your question. If you're using the RStudio Pro Drivers to make your connection to Snowflake, then you can use Okta by setting the following in your connection string:

Authenticator = https://[okta_account].okta.com

In this example, okta_accountis your Okta account name. Further details about authentication to Snowflake can be found starting on page 34 of the Snowflake driver documentation. It's worth noting that the docs state If you are using the internal Snowflake authenticator or native Okta as your authentication method, set the PWD property to the password that you use to authenticate your connection. So, you will still need to include PWD when authenticating with Okta.

Hope that helps!

[preethin93]

Hello,

Thanks for your response.

We recently got to know that Snowflake uses external browser for authentication. Okta is in sync with Active directory. It validates with Active directory and logs us in.

For this case, does the user still need a password to log in?

[Blair09M]

The authentication methods supported by the ODBC driver are outlined in the following table:

Unfortunately, external browser authentication isn't supported for ODBC connections made from a server, given the following statement from the Snowflake docs:

With browser-based SSO, the Snowflake-provided client (for example, the Snowflake JDBC driver) needs to be able to open the user’s web browser. For this reason, the Snowflake-provided client and the client application that uses it need to be installed on the user’s machine. Browser-based SSO does not work if the Snowflake-provided client is used by code that runs on a server.

[preethin93]

That makes sense. What do you recommend in order to connect to snowflake from R Workbench without password using external browser. Is there any workaround for this?

[Blair09M]

Unfortunately, I'm not aware of any mechanism that would allow external browser authentication to take place from RStudio Workbench. If you're looking to avoid passing in a password with each connection, perhaps key pair authentication is a valid option.

[preethin93]

I am trying to connect using Okta since we use both Okta or external browser. I am facing this error.
> myconn <- DBI::dbConnect(odbc::odbc(), "dsn", uid="emailid", PWD="password", server = "company.snowflakecomputing.com", authenticator = "https://company.okta.com/home /snowflake/0oa1ik4wwzcT8b5KY0h8/54889", proxy = "usw2-envt-rstudio01.company.com")
OOB curl_easy_perform() failed: Couldn't connect to server
OOB curl_easy_perform() failed: Couldn't connect to server
OOB curl_easy_perform() failed: Couldn't connect to server
OOB curl_easy_perform() failed: Couldn't connect to server
OOB curl_easy_perform() failed: Couldn't connect to server
Error: nanodbc/nanodbc.cpp:1021: 00000: [RStudio][Snowflake] (4)
REST request for URL https://company.snowflakecomputing.com:443/session/authenticator- request failed: CURLerror (curl_easy_perform() failed) - code=7 msg='Couldn't connect to server' osCode=111 osMsg='Connection refused'.

Any inputs on how to resolve this?