configuring iodbc to use FreeTDS for MS SQL Server under cygwin

Question

I'm trying to use RODBC to make a connection to a SQL Server database from R under cygwin.

I've installed libiobbc and libiodbc-devel, which I understand provide the driver manager that the RODBC package needs.

I installed freetds, which I understand provides an ODBC driver that can connect to a MS SQL server database. I used its tsql command-line tool to confirm that I can make a connection to a SQL Server.

I now understand the task to be pointing the driver manager (iODBC) to the driver (freeTDS), so that I can make a connection to the SQL Server database from R's RODBC library

The User Guide to freeTDS hints that the driver that freeTDS provides should be at /usr/local/freetds/lib/libtdsodbc.so, and that something like the following should define this driver to idobc:

;
; odbcinst.ini
;
[FreeTDS]
Driver = /usr/local/freetds/lib/libtdsodbc.so

But I don't have libtdsodbc.so on my system, and it appears that the cygwin package freetds does not provide it (listing of installed binaries and support files)

How can I configure idobc to use the freeTDS driver to connect to MS SQL?

EDIT

A colleague located /lib/cygtdsodbc.dll and /usr/lib/cygtdsodbc.dll on my system. Might this be the cygwin equivalent of libtdsodbc.so?

I created an odbcinst.ini file with the following content to define the driver:

[ODBC Drivers]
FreeTDS = Installed

[FreeTDS]
Driver = /usr/lib/cygtdsodbc.dll

and an odbc.ini file defining a data source my_db, using the driver defined above:

[ODBC Data Sources]
my_db = FreeTDS

[my_db]
Driver = /usr/lib/cygtdsodbc.dll
Database = db_name
Host = server_dns_name
Port = 1433

running iodbctest to check things out gives

$ iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0812.0326

Enter ODBC connect string (? shows list): ?

DSN                              | Driver
------------------------------------------------------------------------------
my_db                            | FreeTDS

Enter ODBC connect string (? shows list): my_db
1: SQLDriverConnect = [iODBC][Driver Manager]Driver's SQLAllocEnv() failed (0) SQLSTATE=IM004
1: ODBC_Connect = [iODBC][Driver Manager]Driver's SQLAllocEnv() failed (0) SQLSTATE=IM004

Answer 1

The iodbctest program indicates it can't find my_db; that's your clue that it's not configured correctly.

Have another look at the FreeTDS User Guide, specifically at what properties can and can't be in odbc.ini. You need a server or servername property in your odbc.ini, depending on whether or not you want to use your freetds.conf file to identify the server.