Connecting MS SQL using freetds and unixodbc: isql - no default driver specified

Question

I am trying to connect to the MS SQL database using freetds and unixodbc. I have read various guides how to do it, but no one works fine for me. When I try to connect to the database using isql tool, I get the following error:

$ isql -v TS username password
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

Have anybody already successfully established the connection to the MS SQL database using freetds and unixodbc on Ubuntu 12.04? I would really appreciate some help.

Below is the procedure I used to configure the freetds and unixodbc. Thanks for your help in advance!

Procedure

First, I have installed the following packages with:

sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc

and configured freetds as follows:

--- /etc/freetds/freetds.conf ---
[TS]
host = SERVER
port = 1433
tds version = 7.0
client charset = UTF-8

Using tsql tool I can successfully connect to the database by executing

tsql -S TS -U username -P password

As I need an odbc connection I configured odbcinst.ini as follows:

--- /etc/odbcinst.ini ---
[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout =
CPResuse  =
client charset = utf-8

and odbc.ini as follows:

--- /etc/odbc.ini ---
[TS]
Description = "test"
Driver = FreeTDS
Servername = SERVER
Server = SERVER
Port = 1433
Database = DBNAME
Trace = No

Trying to connect to the database using isql tool with such a configuration results the following error:

$ isql -v TS username password
[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
[ISQL]ERROR: Could not SQLConnect

Answer 1

Thanks, your post was very useful for me. I was able to get it to work by eliminating the following lines from my odbcinst.ini file

Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout =
CPResuse  =
client charset = utf-8

so now my odbcinst.ini file looks like this:

--- /etc/odbcinst.ini ---
[FreeTDS]
Description = FreeTDS
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

and my odbc.ini file looks like this now:

--- /etc/odbc.ini ---
[TS]
Description = "test"
Driver = FreeTDS
Server = SERVER
Port = 1433
Database = DBNAME

Once I simplified everything it worked great. I still can't get it to work with RODBC but it worked with isql.

I don't know if this will help but your post helped me. Thanks.

Answer 2

This is a minimal but complete example how to connect to Azure SQL Database with isql from Ubuntu 14.04.1 LTS. The example is extracted from How To Connect Azure SQL Database From Ubuntu (disclaimer: it's my personal wiki).

Install necessary packages

$ sudo apt-get -y install freetds-bin tdsodbc unixodbc

Configure FreeTDS

File /etc/freetds/freetds.conf

[global]
tds version = 7.1

[<SERVERNAME>]
host = <HOST>.database.windows.net
port = 1433

Test connection

At this point connecting with tsql should work:

$ tsql -S <SERVERNAME> -U <USERNAME>@<HOST> -P <PASSWORD>

Note that @<HOST> is required. Otherwise the connection ends with an error:

Msg 40531 (severity 11, state 1) from [<SERVERNAME>] Line 1:
    "Server name cannot be determined.  It must appear as the first segment of the server's dns name (servername.database.windows.net).  Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername).  In addition, if both formats are used, the server names must match."
Error 20002 (severity 9):
    Adaptive Server connection failed
There was a problem connecting to the server

Configure ODBC driver

File /etc/odbcinst.ini

[FreeTDS]
Description = FreeTDS Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Configure ODBC data sources

File /etc/odbc.ini

[<DATA_SOURCE_NAME>]
Driver = FreeTDS
Servername = <SERVERNAME>
Port = 1433
Database = <DBNAME>

<SERVERNAME> is the same than in freetds.conf.

Connect with isql

$ isql -v <DATA_SOURCE_NAME> <USER>@<HOST> <PASSWORD>
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select current_timestamp
+------------------------+
|                        |
+------------------------+
| 2015-01-02 09:05:55.593|
+------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

Note that @<HOST> is required. Otherwise the connection ends with an error:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[37000][unixODBC][FreeTDS][SQL Server]Server name cannot be determined.  It must appear as the first segment of the server's dns name (servername.database.windows.net).  Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername).  In addition, if both formats are used, the server names must match.
[ISQL]ERROR: Could not SQLConnect

Answer 3

In my case the problem arose because of simple indents in my config file. So in /etc/odbc.ini, I removed all indents and voila!

(odbcinst.ini behaves like a normal kid and doesn't seem to throw any tantrums.)

Answer 4

Ubuntu prior to 12.04 had a different odbc path in the /etc/odbcinst.ini file.

The old driver path was:

Driver = /usr/lib/odbc/libtdsodbc.so

I changed it to:

Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Here's the full configuration:

--- /etc/odbcinst.ini ---
[FreeTDS]
Description = tdsodbc
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1
CPTimeout = 5
CPReuse = 5

Works like a charm now! Thanks!