Cannot Connect to MS SQL server from R shiny app deployed on R shiny server installed on Ubuntu 18.04

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

  1. am I doing something wrong in the connection string
  2. do I need to configure an environment variable so shiny server knows where to look for the installed drivers
  3. 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.

I know from experience it can be frustrating setting up MS SQL-Server in *nix. Which MSSQL driver are you using? FreeTDS? Microsoft? Simba?

Thanks for your reply.

The driver installed is at the location
/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1

Driver name :Microsoft ODBC Driver 17 for SQL Server

Thanks

That's the Microsoft ODBC driver. If you're using NTLM security, you may have problems as I don't think that's supported in Linux.

The MSFT site has some good debugging tips including how to turn on a tracefile for debugging:

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017#troubleshooting-connection-problems

This topic was automatically closed 21 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.