RODBC MS SQL access from Ubuntu using FreeTDS

Question

I'm trying to access MS SQL server hosted on my Windows box from an Ubuntu machine. I have a shiny app that access MSSQL that works fine on Windows. I want to host this with shiny server on Ubuntu so that others can access the webpage and provide their SQL server as input to the app.

All of this works fine on Windows. Now I'm not able to get unixODBC working. I think I've got the odbc/freeTDS installation and configuration correct. I'm able to connect and query SQL DB instance from Ubuntu terminal using tsql -S . It took some googling but eventually it worked.

Now, when I try to connect from R I get this error.

sql <- odbcConnect("abc.xyz.com", "uname", "passwd")

In odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd") : [RODBC] ERROR: state 08001, code 0, message [unixODBC][FreeTDS][SQL Server]Unable to connect to data source 2: In odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd") : [RODBC] ERROR: state 01000, code 20002, message [unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed 3: In odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd") : [RODBC] ERROR: state 01000, code 20017, message [unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server 4: In odbcDriverConnect("DSN=abc.xyz.com;UID=uname;PWD=passwd") : ODBC connection failed

freeTDS.conf
[abc.xyz.com]
        host = abc.xyz.com
        port = 49475
        tds version = 8.0

odbcinst.ini
[FreeTDS]
Description   = FreeTDS unixODBC Driver
Driver        = /usr/local/lib/libtdsodbc.so
Setup         = /usr/local/lib/libtdsodbc.so

odbc.ini
[abc.xyz.com]
Description = Shiny testing
Driver = FreeTDS
Trace = No
Server = abc.xyz.com\instance_name
Database = dbanme
port = 49475

This error "Unexpected EOF from the server" is not new to me. I got the same error when I was trying to connect using tsql. I was able to overcome this by adding "tds version = 8.0" line to freeTDS.conf. Not sure how I can get RODBC to use this config. I've read posts were other users were able to sql-server using freeTDS. Not sure what is missing here. I also tried re-installing RODBC.

Answer 1

I fixed this. TDS version was needed in odbc.ini file.

TDS_Version = 8.0

tsql reads version info from freeTDS.conf and worked. isql was failing with same error and it was also looking for this config in odbc.ini. So test with isql if you are configuring for R/Python.

Answer 2

I don't know what is causing the error on your side. Hope this helps:

These are my settings for RODBC 1.3-10 to connect to MSSQL Server 2012. I am also on ubuntu and this works for me. I think I installed the RODBC package from the Ubuntu repos:

apt-cache policy r-cran-rodbc
r-cran-rodbc:
  Installed: 1.3-10-1
  Candidate: 1.3-10-1
  Version table:
 *** 1.3-10-1 0
        500 ftp://ftp.fu-berlin.de/linux/ubuntu/ trusty/universe amd64 Packages
        100 /var/lib/dpkg/statu

uname -a

Linux xxx 3.13.0-30-generic #54-Ubuntu SMP Mon Jun 9 22:45:01 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

odbc.ini

[mydsn]
APP = unixodbc
Description     = master on vmXX
Driver          = TDSdriver
Server          = vmXX
Database        = master
Port            = 1433
#Trace           = No
#TraceFile       = /var/log/freetds/freetds--odbc.log
TDS Version     = 7.2

freetds.conf

[global]
        # TDS protocol version
;       tds version = 4.2

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
        #dump file = /tmp/freetds.log
        #debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.  
        # Try setting 'text size' to a more reasonable limit 
        text size = 64512




[vmXX]
        host = 111.222.333.555
        port = 1433
        tds version = 7.2
        client charset = UTF-8

Output from

ps aux  | grep "/R"
knb      56969 ....
lsof -p 56969 | grep -i odbc

R       56969  knb  mem    REG     8,1    72408  8528592 /usr/lib/x86_64-linux-gnu/libodbcinst.so.1.0.0
R       56969  knb  mem    REG     8,1   400608  8521896 /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
R       56969  knb  mem    REG     8,1   419680  8525415 /usr/lib/x86_64-linux-gnu/libodbc.so.1.0.0
R       56969  knb  mem    REG     8,1    96845  8933205 /usr/local/lib/R/site-library/RODBC/libs/RODBC.so

Answer 3

I had a similar error and realized R was just unable to read my user id correctly as it had a slash in it. I placed my credentials in a text file which solved the problem for me.

cred <- "/myPath/ODBC_cred.txt"

sql <- RODBC::odbcConnect("abc.xyz.com", uid=readLines(cred)[1], pwd=readLines(cred)[2])