Ubuntu: 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  


Solution: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.


Solution: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  


Solution: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.)


Solution: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!


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »