RODBC MS SQL access from Ubuntu using FreeTDS

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.

Solution

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.