Shiny app not Connecting to snowflake through SSO

shiny
1

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!

2

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?

3

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?

4

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.

5

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

6

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?

7

Hello rstub,

I only see this:

    not a dynamic executable

Thank you!

8

What is the output of

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

?

9

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

10

This indicates that your ODBC driver installation is broken. Here how it should look like (using RStudio Pro Drivers, but that does not matter here):

ralf@barra:~$ file /opt/rstudio-drivers/snowflake/bin/lib/libsnowflakeodbc_sb64.so 
/opt/rstudio-drivers/snowflake/bin/lib/libsnowflakeodbc_sb64.so: ELF 64-bit LSB shared object, x86-64, version 1 (GNU/Linux), dynamically linked, stripped

So either reinstall Snowflake's ODBC driver and see if that fixes things or try the RStudio Pro Drivers.

11

Hello rstub,

Thank you for helping me find the root cause. I will go ahead and see if I can sync the driver from prod to dev. If not will reinstall it.

Thank you for you time!

12

Hello rstub,

I am able to fix the driver now and it works for other app.

/usr/lib/snowflake/odbc/lib/libSnowflake.so: ELF 64-bit LSB shared object, x86-64, version 1 (GNU/Linux), dynamically linked, BuildID[sha1]=ecc98d436b29e2ba42a30e298bfaf1a3ee94ea8d, with debug_info, not stripped

I also followed the snowflake documentation and added all the parameter they require:
https://docs.snowflake.com/en/user-guide/odbc-parameters.html#required-connection-parameters

[snowflake-app-1-user]
Driver=SnowflakeDSIIDriver
DSN=snowflake-app-1-user
Port=443
Server=abc.us-east-1.def.snowflakecomputing.com
uid= zyx@abc.app.com
pwd=abcdefghj

I can manually login fine with these info.

But here, now i have this error:

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

It works for another app where the username and password is created not through sso but through sysadmin role. I wonder what am I missing here?

Thank you!

Regards,
Pratik

13

Please ignore the space after uid. There is no space there.

14

Can you explain what you did here? My guess would be that creating username and password through sysadmin role corresponds to what is described at User management | Snowflake Documentation, right?
How do you create username and password using SSO?

15

Yeah, creating a username and password through the sysadmin role works fine. That is how we created users earlier.
But now, we started creating users through AD group for snowflake access, and this particular username and password was created that way.

So basically, when I manually log in, I see an SSO login link on the console, and it lets me into the snowflake account vs for older users, I can click the login button in the console.

I wonder if there is some other setup that I need to do for users created this way?

console
console1755×841 28.6 KB

16

Let's look at the documentation how SSO works with Snowflake:

One approach is browser based SSO. That works for all identity provider that support SAML, however it has a harsh requirement:

Browser-based SSO does not work if the Snowflake-provided client is used by code that runs on a server.
This is the case here where the R code for your Shiny app runs on the Connect server.

The alternative would be native SSO which would be ideal for connecting from a Shiny app hosted on a Connect server. However, this is Okta only. And it seems you are using Azure AD, right?

So it looks like currently you cannot use usename/password together with SSO. An alternative might be to use Key-pair authentication and key-pair rotation | Snowflake Documentation with a passphrase-less key.

17

Hello rstub,

Thank you for your guidance. That makes perfect sense. I was reading through that but was not sure if my understanding was correct.

I will go ahead and go through the key pair route for now. Or even the user creation through the traditional approach (using sysadmin role) should work.

I appreciate your time!

1 Like
18

This topic was automatically closed 54 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.