odbc not working in Rstudio-server ("Can't open lib"), but works in cli

[ospangler]

I have confirmed this is working if I'm running R through terminal on Amazon Linux 2 vm. I went ahead and installed rstudio-server (rstudio-server-rhel-1.2.5033-x86_64.rpm) on the same ec2, but cannot get the connection to work. It sounds similar (but the opposite problem) to this question .

> myconn <- DBI::dbConnect(odbc::odbc(), "snowflake", uid="...", pwd='...')
Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/home/ec2-user/snowflake_odbc/lib/libSnowflake.so' : file not found

I've slogged through this with zero system/linux admin experience and have little idea of how to proceed. My only ideas are to try the RODBC package instead, or venture into the world of "setting paths" or "symlinked" or environment variables.

Since I can successfully connect through R in the terminal is there any sort of command I could try (at r-terminal) to gather more info on what the issue is? Why would it work through r-terminal, but not rstudio-server?

Thoughts?

More background:

• Since I'm using rstudio-server while signed in as an "user" I thought it was a permissions issue. I've changed permissions to /home/ec2-user/snowflake_odbc/lib/libSnowflake.so, still with no luck.

• I've tried odbc::odbcListDrivers() from both r-terminal and rstudio-server and they return the exact same results.

• Configured to specific port following: https://support.rstudio.com/hc/en-us/articles/200552316-Configuring-the-Server

• rstudio-server-rhel-1.2.5033-x86_64

• snowflake_linux_x8664_odbc-2.22.1

[cole]

Welcome to RStudio Community!! And thanks for the thorough post!

RStudio Server definitely changes some things (LD_LIBRARY_PATH, PATH, etc.) that could cause trouble.

I would compare environment variables between the place where it works and the place where it doesn't.

Also, it might be worth trying with isql from the

• terminal outside RStudio
• R session outside RStudio
• terminal inside RStudio
• R session inside RStudio (can even do system("isql"))

If you can isolate the problem to odbc, then I believe you are deeper than I know how to go, and might have something to do with how odbc is loading libraries.

I normally configure system-wide (rather than in a user-specific location) by using:

• /etc/odbc.ini
• /etc/odbcinst.ini

I know there are user specific analogues for odbc.ini (~/.odbc.ini, maybe?), and there may be similar for odbcinst.ini (which points at driver .so files). It may be worth taking a look at those to see if they resolve your issue!!

[ospangler]

following the isql idea...

@cole

• terminal outside RStudio
• R session outside RStudio

Both of these work, no problem

@cole

terminal inside RStudio

No luck, with slightly different error:

[01000][unixODBC][Driver Manager]Can't open lib '/home/ec2-user/snowflake_odbc/lib/libSnowflake.so' : file not found
[ISQL]ERROR: Could not SQLConnect

@cole

R session inside RStudio (can even do system("isql") )

Also no luck, with system("isql -v snowflake... ") gives same [ISQL]ERROR: Could not SQLConnect

Is the idea that this has narrowed it down to R-studio Server's environment variables, since attemping with isql is independent of the odbc package?

I originally followed Snowflake's configuring-with-unixodbc doc , which mentions "By running unixodbc_setup.sh , you don’t need to set any environment variables."

[ospangler]

@ospangler

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/home/ec2-user/snowflake_odbc/lib/libSnowflake.so' : file not found

Ok, figured it out, snowflake's install odbc documentation is lacking a little. For whatever reason Installing the RPM Package steps worked. Turns out there is a note in there:

"The installation directory is /usr/lib64/snowflake/odbc/ . You’ll need the location later in the instructions."

Probably obvious to lots of people, but /usr is accessible to any user. Versus the TGZ File steps do not hint at the odbc's location being significant when it comes to multiple users.

I originally was following the steps for the TGZ File:
"Copy the resulting snowflake_odbc folder to the directory where you want to install the driver. Make note of this directory. You’ll need the location later in the instructions."

In the case of multiple users on rstudio-server, turns out its significant where this folder sits. So one might still use the TGZ File, as long as they had a clue about where to copy it to.

I got the RPM package to work, so I'll stop there.