R studio windows and unix connection to Microsoft SQL Server

Question

I am able to make connection from my laptop (window's) to the database using the below details

conn2 <- DBI::dbConnect(odbc::odbc(),
                        Driver   = "ODBC Driver 17 for SQL Server",
                        Server   =  "XXXX" ,
                        Database = "XXXX",
                        Trusted_Connection = "yes")

but when I try to connect to rstudio pro which is using a linux/unix machine the same code give me error

conn2 <- DBI::dbConnect(odbc::odbc(),
                        Driver   = "ODBC Driver 17 for SQL Server",
                        Server   =  "XXXX" ,
                        Database = "XXXX",
                        Trusted_Connection = "yes")

The error is

Error: nanodbc/nanodbc.cpp:983: 00000: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired  [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]MAX_PROVS: Error Locating Server/Instance Specified [xFFFFFFFF].   [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

I used the same code and the details are correct as I am able to make connection via window's machine not sure what is the issue , I also tried changing the driver from ODBC Driver 17 for SQL Server to SQL Server

and got this error

Error: nanodbc/nanodbc.cpp:983: 00000: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

I am not sure how to resolve this, would appreciate if someone could help me using the same syntax/ library DBI::dbConnect and odbc library as I need to make some read and write to table which is very fast using dbWriteTable as my data set is huge.

List of drivers installed

score 0 · Answer 1 · answered Sep 19 '20 at 17:16

You need to double-check that the ODBC driver is installed on that machine, and use it. Run odbc::odbcListDrivers() to enumerate all drivers available to you. On my machine the output looks like this:

odbc::odbcListDrivers()
#>                             name         attribute
#> 1                   ODBC Drivers PostgreSQL Driver
#> 2              PostgreSQL Driver            Driver
#> 3              PostgreSQL Driver         FileUsage
#> 4              PostgreSQL Driver         Threading
#> 5              SQL Server Driver       Description
#> 6              SQL Server Driver            Driver
#> 7              SQL Server Driver       DontDlClose
#> 8                         SQLite       Description
#> 9                         SQLite            Driver
#> 10                        SQLite             Setup
#> 11                        SQLite        UsageCount
#> 12                       SQLite3       Description
#> 13                       SQLite3            Driver
#> 14                       SQLite3             Setup
#> 15                       SQLite3        UsageCount
#> 16 ODBC Driver 17 for SQL Server       Description
#> 17 ODBC Driver 17 for SQL Server            Driver
#> 18 ODBC Driver 17 for SQL Server        UsageCount
#>                                                        value
#> 1                                                  Installed
#> 2                                               psqlodbcw.so
#> 3                                                          1
#> 4                                                          2
#> 5                                             FreeTDS driver
#> 6               /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
#> 7                                                          1
#> 8                                         SQLite ODBC Driver
#> 9                                           libsqliteodbc.so
#> 10                                          libsqliteodbc.so
#> 11                                                         1
#> 12                                       SQLite3 ODBC Driver
#> 13                                         libsqlite3odbc.so
#> 14                                         libsqlite3odbc.so
#> 15                                                         1
#> 16                   Microsoft ODBC Driver 17 for SQL Server
#> 17 /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
#> 18                                                         1

^{Created on 2020-09-19 by the reprex package (v0.3.0)}

The values in the name column (e.g. "Microsoft ODBC Driver 17 for SQL Server") are possible values to the Driver argument to DBI::dbConnect(). If the driver is not installed, you need to ask somebody with sufficient privileges to install it for you on RStudio Server Pro.

added link below after using isql snippet from terminal https://imgur.com/a/N9M4QT1 — Dexter1611, Sep 21 '20 at 03:41
The `/opt/...` values in the `value` column in `odbcListDrivers()` represent files. Do they exist? You can also try connecting to the database with `isql` to see what's wrong. Ultimately, if it's an installation problem, you will need to talk to the person/organization who maintains the RStudio Server Pro installation — krlmlr, Sep 21 '20 at 04:26

R studio windows and unix connection to Microsoft SQL Server

1 Answers1