Hello there,

I cannot connect to my snowflake account from the shiny app in RSConnect.

The odbc.ini file uses the UID and password.

Added below is what my odbc.ini file looks like in the RStudio-Connect:

[snowflake-abc-app-user]
Driver=SnowflakeDSIIDriver
Server= private.snowflake.account
UID=40E94D11C969EFA0CBC004F2655B858E
Password=EBEFE1B0815705419D22AFAE5CE307D0
CLIENT_SESSION_KEEP_ALIVE=True

I tried with snowflake UID and password generated through the admin user privilege, and it connects fine, but since the AD user is what is recommended for my task, I see a connection issue.
What configuration should I change to get this connection working? Is it adding something to the .gcfg file, or is there any parameter that could resolve this issue?

I would appreciate your help.

Thank you!

What does the R code look like? What error message do you get? Can you use the configured DSN outside of R, e.g. with isql snowflake-abc-app-user?

Hello rstub,

Thank you for the reply.

It's a shiny application that is deployed in rsconnect. We have a key that we use in our prod and it works well there.

But for the dev environment, I am trying to use the password. I am not able to authenticate snowflake-with-Azure-AD-SSO-from rstudio connect.

I have setup the configuration as per the documentation:

The IdPMetaData and Address is available in my configuration file.

[Server]
SenderEmail = test@example.com
; Replace Address with the value you specified in the Server.Address field under the General tab in Okta.
Address ='I set this up'

[Http]
Listen = :3939

[Authentication]
Provider = saml

[SAML]
Logging = true
; Replace IdPMetaData with the value from step 3
IdPMetaData = 'I set this up'
IdPAttributeProfile = Okta
SSOInitiated = IdPAndSP
GroupsAutoProvision = true

My odbc.ini is setup like this:

[snowflake-precision-med-user]
Driver=SnowflakeDSIIDriver
Server=abc.us-east-1.privatelink.snowflakecomputing.com
UID=UID=40E94D11C969EFA0CBC004F2655B858E
PWD= EBEFE1B0815705419D22AFAE5CE307D0

However, I am still not able to connect to the snowflake account using this command

isql -v snowflake-precision-med-user

This is the error that I am getting

[28000][unixODBC]Incorrect username or password was specified.
[ISQL]ERROR: Could not SQLConnect

Is there anything that I am missing here?

Which ODBC driver do you use? The name SnowflakeDSIIDriver hints at Snowflake's driver. Is that correct? If yes, it is of course important to follow the documentation for that driver. The DSN definition from your first message used Password= instead of PWD=. And in the second message you had UID=UID=... and an additional space after PWD=. Removing these things I get:

[snowflake-precision-med-user]
Driver=SnowflakeDSIIDriver
Server=abc.us-east-1.privatelink.snowflakecomputing.com
UID=40E94D11C969EFA0CBC004F2655B858E
PWD=EBEFE1B0815705419D22AFAE5CE307D0

Can you connect to the DB using this DSN? If not, what is the error message? For now I would focus on getting the connection working using isql -v <DSN-name>. Once that works connection from R should be as simple as DBI::dbConnect(odbc:odbc(), <DSN-name>).

Side note: For now it is irrelevant how users in Connect are authenticated since SAML/OIDC credentials are not forwarded to the R process supporting the Shiny application.

Side note 2: You could also make use of the RStudio Professional Drivers, which are covered by our support agreement.

Hello rstub,

Thanks for your follow up.

Sorry, that UID specified twice was a typo. I have odbc.ini file as what you have attached with the email.

[snowflake-precision-med-user]
Driver=SnowflakeDSIIDriver
Server=abc.us-east-1.privatelink.snowflakecomputing.com
UID=40E94D11C969EFA0CBC004F2655B858E
PWD= EBEFE1B0815705419D22AFAE5CE307D0

Yes, the SnowflakeDSIIDriver points to the snowflake driver that is mentioned in this documentation:
https://docs.snowflake.com/en/user-guide/odbc-linux.html

Here is what the file looks like:

[SnowflakeDSIIDriver]

APILevel=1

ConnectFunctions=YYY

Description=Snowflake DSII

Driver=/usr/lib/snowflake/odbc/lib/libSnowflake.so

DriverODBCVer=03.52

SQLLevel=1

UsageCount=1

I enabled logging in the /etc/odbcinst.ini file path as I could not find any files in the location that you mentioned and here is what my error says:

root@ip-10-251-4-55:/usr/bin# isql -v snowflake-precision-med-user

[01000][unixODBC][Driver Manager]Can't open lib '/usr/lib/snowflake/odbc/lib/libSnowflake.so' : file not found

[ISQL]ERROR: Could not SQLConnect

I double checked and the driver is available there in that file location. These were the files available in /usr/lib/snowflake/odbc/ path:

libSnowflake.so libSnowflake.so.save simba.snowflake.ini

The symlink is also not broken. I am thinking somehow the driver is corrupted?

I was just wondering what would be the best way to debug and fix this problem? I was thinking of fixing with this driver than trying the Rstudio pro driver with simba odbc data connector.

Can I reinstall the driver without purging the old driver? Or if there is a way to check if my driver has some issue and do some broken fix?

I found the Simba connector documentation little confusing to understand. I didn't know where to obtain the tarball name and follow that process .

Thank you!

Regards,
Pratik

Most of the time errors like this point to missing libraries, i.e. the file that cannot be opened is linked against libraries that do not exist. You can test this using

ldd /usr/lib/snowflake/odbc/lib/libSnowflake.so

Does that list any libraries as not found?

Hello rstub,

I only see this:

    not a dynamic executable

Thank you!

What is the output of

ls -l /usr/lib/snowflake/odbc/lib/libSnowflake.so
file /usr/lib/snowflake/odbc/lib/libSnowflake.so

?

Output from the first command:
-rwxr-xr-x 1 root root 161880513 Aug 3 09:14 /usr/lib/snowflake/odbc/lib/libSnowflake.so

Output from the second command:
/usr/lib/snowflake/odbc/lib/libSnowflake.so: data