Unable to connect to SQL server through unix environment

[heramb]

Hi,

I am trying to connect to SQL server through odbc package and its giving me a weird error. Below are the connection strings that I have tried so far.

library(odbc)
library(DBI)
con1 <- dbConnect(odbc(), .connection_string =  'driver={SQLServer};
server=<server_alias>;database=<db_name>;uid=<user_id>;pwd=<pwd>')

con2 <- dbConnect(odbc(), .connection_string =  'driver={SQLServer};
server=<server_name>\\<instance>;database=<db_name>;uid=<user_id>;pwd=<pwd>')

con3 <- dbConnect(odbc(), .connection_string =  'driver={SQLServer};
server=<server_name>\\<instance>,<port_number>;database=<db_name>;
uid=<user_id>;pwd=<pwd>')

con4 <- dbConnect(odbc(), .connection_string =  'driver={SQLServer};
server=<server_name>\\<instance>;database=<db_name>;uid=<user_id>;pwd=<pwd>;
Trusted_Connections=TRUE')

con1 and con2 work perfectly fine when executed on Windows. In Unix it gives below error:

Error: nanodbc/nanodbc.cpp:950: 08S01: [unixODBC][RStudio]Connection broken

If I change the server name and the corresponding credentials; it works well in unix as well. So I know its not the issue with the odbc package or even the odbcinst.ini file. Could someone point me towards a possible root-cause for this error?

Below is the session info from both OS

Windows::

sessionInfo()
R version 3.5.1 (2018-07-02)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 17134)

Matrix products: default

locale:
[1] LC_COLLATE=English_India.1252  LC_CTYPE=English_India.1252    LC_MONETARY=English_India.1252
[4] LC_NUMERIC=C                   LC_TIME=English_India.1252    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] DBI_1.0.0  odbc_1.1.6

loaded via a namespace (and not attached):
[1] bit_1.1-14      compiler_3.5.1  hms_0.4.2       tools_3.5.1     Rcpp_1.0.0      bit64_0.9-7    
[7] blob_1.1.1      pkgconfig_2.0.2 rlang_0.3.0.1  

UNIX (Red Hat Enterprise Linux Server)::

 sessionInfo()
R version 3.4.4 (2018-03-15)
Platform: x86_64-redhat-linux-gnu (64-bit)
Running under: Red Hat Enterprise Linux

Matrix products: default
BLAS/LAPACK: /usr/lib64/R/lib/libRblas.so

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C
 [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8
 [7] LC_PAPER=en_US.UTF-8       LC_NAME=C
 [9] LC_ADDRESS=C               LC_TELEPHONE=C
[11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] DBI_0.8    odbc_1.1.5

loaded via a namespace (and not attached):
[1] bit_1.1-12      compiler_3.4.4  hms_0.4.2       Rcpp_0.12.16
[5] bit64_0.9-7     blob_1.1.1      pkgconfig_2.0.1 rlang_0.2.0

Thanks!
Heramb

[shizidushu]

Install the driver first. Check https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

con <- DBI::dbConnect(odbc::odbc(),
                      Driver = 'ODBC Driver 17 for SQL Server',
                      Server = server,
                      UID    = uid,
                      PWD    = pwd,
                      Port   = port,
                      Database = database
)

[heramb]

@shizidushu : Thanks for your response! We had the drivers installed correctly. The issue was with the particular DB which was not communicating with the unix machine.

For future reference; in case of Connection broken error; try nslookup <server_name> on Shell
This can be a good pinpoint for the DB-teams.

Thanks!
Heramb