Search code examples
odbcrstudio-serverazure-sql-serverr-dbi

ODBC SQL server not found in R Studio Server

I'm trying to connect an Azure SQL instance to my R Studio Server that is served on Ubuntu. 

library(DBI)

library(odbc)

con_string <- paste(c('DRIVER', "PORT", "SERVER", "DATABASE", "UID", "PWD"),
                    c('{ODBC Driver 17 for SQL Server}', 1433,
                      "mydatabase.database.secure.windows.net", "DB1",
                      "me", "foo"),
                    sep = "=", collapse = ";")


con <- DBI::dbConnect(odbc::odbc(), .connection_string = con_string)

The error I get:

Error: nanodbc/nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found

I've tried all of the other drivers: 

{ODBC Driver 17 for SQL Server}
{ODBC Driver 13 for SQL Server}

I can't connect. I've used pyodbc in other environments and been able to connect with the same string.

I confirm that the ODBC libraries are installed:

ubuntu@ip-1-2-3-4:~$ sudo apt-get install unixodbc unixodbc-dev --install-suggests
Reading package lists... Done
Building dependency tree       
Reading state information... Done
unixodbc is already the newest version (2.3.1-4.1).
unixodbc-dev is already the newest version (2.3.1-4.1).
The following packages were automatically installed and are no longer required:
  linux-aws-headers-4.4.0-1048 linux-aws-headers-4.4.0-1049 linux-headers-4.4.0-1048-aws linux-headers-4.4.0-1049-aws linux-image-4.4.0-1048-aws
  linux-image-4.4.0-1049-aws
Use 'sudo apt autoremove' to remove them.
0 upgraded, 0 newly installed, 0 to remove and 107 not upgraded

.It appears that the drivers are not available from within my R environment or I'm calling it the wrong thing. This is the R Server docs that I'm reading

Solution

  • The Problem doesn't comes from the unix-odbc lib. Indeed as you can see in this: 

    [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found

    2 Solutions are to try:

    • The problem is the driver is not found. It can be due because your driver is not correctly installed in your computer. Verify you followed the installation step.

    • It can also be caused by a missing -Wl,-rpath,<Link to your gcc driver library> option in the compilation of your C++ application. But as the R Language is written in C this should not influence you.