Problem:
unable to connect to SQL server from R shiny app when the app is deployed to R shiny server installed on UBUNTU 18.04.
The R shiny app connects well to MS SQL Server in the development environment (windows based - specs below)
Deployment Environment:
OS - Ubuntu 18.04
R version - 3.5.2
R Packages used - DBI (version 1.0.0), odbc (version 1.1.6), shiny(1.2.0)
development environment
Rstudio IDE
Windows 10 Server
R version 3.5.2
R Packages used - DBI (version 1.0.0), odbc (version 1.1.6), shiny(1.2.0)
In the production environment we have installed unixODBC driver manager and installed the MSSQL driver.
I have created the DSN in the odbc.ini (/etc/odbc.ini) file
the odbcinst.ini file lists the MSSQL driver correctly.
The connection string used in the R shiny app
con <- dbConnect(odbc(), "DSN name", UID = "", PWD = "")
The code snippet in R shiny app
library(odbc)
library(DBI)
con <- dbConnect(odbc(), "DSN name", UID = "", PWD = "")
The above code causes the app to crash. When I run the above code in RStudio installed on the same machine Rstudio also crashes with a fatal error.
When i test the connection to MSSQL on UBUNTU TERMinal it succeeds using the same DSN as below
isql -v <DSN_name> username password
So to debug further i ran the following code in R studio on the UBUNTU server
library(odbc)
library(DBI)
odbc::odbcListDrivers()
The above code snippet does not return any of the installed drivers listed in /etc/odbcinst.ini
I have checked the error logs on the SQL server and they do not show any attempt by r shiny app to connect.
Any help in debugging further will be much appreciated ... Thoughts so far
- am I doing something wrong in the connection string
- do I need to configure an environment variable so shiny server knows where to look for the installed drivers
- is it a permissions issue with the r shiny app user in ubuntu
I have successfully deployed and run R shiny apps on the same server that do not connect to SQL server and the shiny server install seems fine.