I'm following up this page and this page to ODBC connect to Snowflake with R/Rstudio. I had faced a few missing Linux libs such as unixODBC
, wslu
, xdg-utils
, and google-chorme
which I was able to yum install. My org activated Azure Active Directory so I don't really have any other option to connect to snowflake except authenticator : externalbrowser
.
My R sessionInfo and capabilities are as follow
> sessionInfo()
R version 4.2.2 (2022-10-31)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: Red Hat Enterprise Linux 8.9 (Ootpa)
Matrix products: default
BLAS/LAPACK: /usr/lib64/libopenblasp-r0.3.15.so
locale:
[1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 LC_MONETARY=en_US.UTF-8
[6] LC_MESSAGES=en_US.UTF-8 LC_PAPER=en_US.UTF-8 LC_NAME=C LC_ADDRESS=C LC_TELEPHONE=C
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
attached base packages:
[1] stats graphics grDevices utils datasets methods base
loaded via a namespace (and not attached):
[1] compiler_4.2.2 parallel_4.2.2 tools_4.2.2
> capabilities()
jpeg png tiff tcltk X11 aqua http/ftp sockets libxml fifo cledit iconv
TRUE TRUE TRUE TRUE FALSE FALSE TRUE TRUE FALSE TRUE TRUE TRUE
NLS Rprof profmem cairo ICU long.double libcurl
TRUE TRUE TRUE TRUE TRUE TRUE TRUE
Here are the outputs of odbc.ini
, odbcinst.ini
, and simba.snowflake.ini
:
$ cat /etc/odbc.ini
[snowflake]
Description=SnowflakeDB
Driver=SnowflakeDSIIDriver
Locale=en-US
SERVER=SF_ACCOUNT.snowflakecomputing.com
PORT=443
SSL=on
ACCOUNT=SF_ACCOUNT
$ cat /etc/odbcinst.ini
[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/usr/lib64/snowflake/odbc/lib/libSnowflake.so
DriverODBCVer=03.52
SQLLevel=1
UsageCount=1
$ cat /usr/lib64/snowflake/odbc/lib/simba.snowflake.ini
[Driver]
DriverManagerEncoding=UTF-16
DriverLocale=en-US
ErrorMessagesPath=/usr/lib64/snowflake/odbc/ErrorMessages
LogNamespace=
LogPath=/tmp
ODBCInstLib=libodbcinst.so
CURLVerboseMode=false
#LogLevel=6
CABundleFile=/usr/lib64/snowflake/odbc/lib/cacert.pem
ANSIENCODING=UTF-8
Here are the init codes I ran that go fine
library(odbc)
library(DBI)
odbc::odbcListDrivers()
name attribute value
<.... Output Trimmed ....>
23 SnowflakeDSIIDriver APILevel 1
24 SnowflakeDSIIDriver ConnectFunctions YYY
25 SnowflakeDSIIDriver Description Snowflake DSII
26 SnowflakeDSIIDriver Driver /usr/lib64/snowflake/odbc/lib/libSnowflake.so
27 SnowflakeDSIIDriver DriverODBCVer 03.52
28 SnowflakeDSIIDriver SQLLevel 1
29 SnowflakeDSIIDriver UsageCount 1
Then I EXPORT
these two which I'm not sure if they are helping or hurting
Sys.setenv(BROWSER = "google-chrome")
Sys.setenv(DISPLAY = "localhost:10.0 && /usr/bin/google-chrome")
And finally this is here I got error and was not able to tackle so far.
con <- DBI::dbConnect(odbc::odbc(),
driver = "SnowflakeDSIIDriver",
server = "<MyCompany>.east-us-2.azure.snowflakecomputing.com",
UID = "<MyFirstName>.<MyLastName>@<MyCompanyName>.COM",
database = "OCDP_PRD_CDCM_DB",
warehouse = "OCDP_PRD_QUERY_WH",
role = "AR_PRD_<FirstName>_<LastName>_<Company>_ROLE",
schema = "CDCM",
authenticator = 'externalbrowser'
)
Initiating login request with your identity provider. A browser window should have opened for you to complete the login. If you can't see it, check existing browser windows, or your OS settings. Press CTRL+C to abort and try again...
[406202:406202:0324/125407.993308:ERROR:ozone_platform_x11.cc(243)] Missing X server or $DISPLAY
[406202:406202:0324/125407.993352:ERROR:env.cc(257)] The platform failed to initialize. Exiting.
[406236:406236:0324/125408.073365:ERROR:ozone_platform_x11.cc(243)] Missing X server or $DISPLAY
[406236:406236:0324/125408.073414:ERROR:env.cc(257)] The platform failed to initialize. Exiting.
[406270:406270:0324/125408.148619:ERROR:ozone_platform_x11.cc(243)] Missing X server or $DISPLAY
[406270:406270:0324/125408.148660:ERROR:env.cc(257)] The platform failed to initialize. Exiting.
xdg-open: no method available for opening 'https://login.microsoftonline.com/db05faca-c82a-4b9d-b9c5-0f64b6755421/saml2?SAMLRequest=lZJfb9owFMW%2FSuQ9J3bShD8WUNEiNCTWsgJD7ctknBvw6tiZ7TTtt58TQOoeWqlvkXOOf8f33NH1aymDFzBWaDVGcURQAIrrXKjDGG0383CAAuuYypnUCsboDSy6nowsK2VFp7U7qgf4W4N1gb9IWdr%2BGKPaKKqZFZYqVoKljtP19MeSJhGhzFowzuPQ2ZJb4VlH5yqKcdM0UXMVaXPACSEEkyH2qlbyDb1DVJ8zKqOd5lpeLK%2F%2BTR8gYkzSFuEVnrA6G2%2BEOo3gM8r%2BJLL0%2B2azClf36w0KppfX3Wpl6xLMGsyL4LB9WJ4CWJ%2BgPh5CXbm65MzA7yOLrNJNIdkzcF1WtfOXRv4LF5BjqQ%2FCj2oxG6PqWeQ327kkdcyUlj%2BPQ9as73bZIxT99e5gNG9W8e6x6onZn%2FtlylHw61Js0ha7sLaGhWrrdP6IJGlIrsIk3cR9mqWU9KP%2BcPCEgpmvUyjmOuclc5cjKgU32urCaSWFgi5lvidZwTgL%2BSBhYbof5uF%2ByLOQFL103%2BtnWZrEuC0tQafFoV0QM%2FniOEb4vfm8gne%2BlcVspaXgb8Fcm5K5j0uLo7g7EXlYdFIKJRNymucGrPXlSambWwPM%2BU13pgaEJyfq%2F7s%2B%2BQc%3D&RelayState=60243'
Error: nanodbc/nanodbc.cpp:1138: 00000
[Snowflake][Snowflake] (38)
Failed to authenticate a user by external browser: 3.
What I'd expected to see, is an external browser to be opened up so that I can authenticate and get pass this step.
Here is what I see when I try it in Jupyter NB in the same server.
import pandas as pd
import numpy as np
import snowflake.connector
ctx = snowflake.connector.connect(
user="<MyFirstName>.<MyLastName>@<MyCompanyName>.COM",
account="<MyCompany>.east-us-2.azure",
role="AR_PRD_<FirstName>_<LastName>_<Company>_ROLE",
warehouse="ECT_PRD_ECDH_READ_WH",
database="ECT_PRD_ECDH_DB",
authenticator='externalbrowser'
)
Which gives me this output that I can click on and authenticate.
My initial hunch is that this is related to X11 forwarding which I have very limited knowledge.
Appreciate any words of wisdom anyone can share.
Thanks!