ERROR: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

Posted on Updated on

One of our server getting odbc_connection error after migration, this will usually  happens because ODBC module has not enabled on the server,  While I tried to set up ODBC with freeTDS in order to connect to a MSSQL server faced several issues.

MSSQL uses Tabular Data Stream (TDS) as a communication protocol which is same like in Sybase. freeTDS is an implementation of TDS protocol.

Before trying to connect with freetds to the mssql server, make sure, that your MSSQL server has remote access to connect.

[02-May-2013 02:21:31 America/Denver] PHP Warning: odbc_connect() [<a href='function.odbc-connect'>function.odbc-connect</a>]: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect in /home/anand/public_html/mssql2000.php on line 69

We need below packages installed to get this done.

1, php-odbc
2, unixODBC
3, freetds

Installation : 

#pecl  install php-odbc
#yum install unixODBC-devel unixODBC freetds-devel freetds

Or,

Download unixODBC and untar it

#wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz
#tar –xzf unixODBC-2.3.0.tar.gz
#cd unixODBC-2.3.0 ; ./configure ; make ; make install

Installing freeTDS
Set environmental variable at /etc/profile. Add following lines at end

#vi /etc/profile
 # TDS
SYBASE=/usr/local 
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib 
export SYBASE LD_LIBRARY_PATH 
#http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
#tar –xzf freetds*.tar.gz ; cd freetds* ; ./configure --with-tdsver=8.0 --with-unixodbc=/usr/local ; make ; make install

Configuration:
Note: tdsver=8.0 if you use SQL 2000, tdsver=7.0 if you use SQL 7.0, More info about freetds.conf here

root@server [~]# cat /etc/freetds.conf
[MSSQLSERVER]               
host = 11.222.333.44
port = 1433
tds version = 8.0

Setup ODBC:
Create template for both driver and configuration. before creating make sure that the driver files are valid.

root@server [~]# vi tds.driver.template
#Driver for MS SQL
[FreeTDS]                       #"FreeTDS" is the unique name for this driver
Description = FreeTDS driver
Driver = /usr/lib64/libtdsodbc.so.0
Setup = /usr/lib64/libtdsS.so.1
FileUsage = 1
UsageCount =1

root@server [~]# vi tds.datasource.template
[MSSQL]               #"MSSQL" is the DSN name that we call to connect database
Driver = FreeTDS      # name that we specified in the driver file
Description = MSSQL ReflectiveLearning
Trace = No
TraceFile = /var/log/freetds.log
Servername = MSSQLSERVER    # this  name specified in the freetds.conf
Port = 1433
Database = reflective_Live
TDS_Version = 8.0
 Now install the data source and driver

The below commands will create configuration file for odbc but odbc.ini has create in users home dir by default we have to move it manually to /etc/

odbcinst -i -d -f tds.driver.template
odbcinst -i -s -f tds.datasource.template
cp -rpf ~/.odbc.ini /etc/odbc.ini
mv /etc/odbc.ini /usr/local/etc
mv /etc/odbcinst.ini /usr/local/etc
cd /etc
ln -s /usr/local/etc/odbc.ini
ln -s /usr/local/etc/odbcinst.ini
chmod 0664 /usr/local/etc/odbc*.ini
cd ~

check by using command line
#tsql -H Hostname_OR_IP -p PortNumber -U Username

root@server [~]# isql -v DSNname Username 'Password'
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
That’s all, now the application will works fine.
some PHP code to connect to it
<?php
//*************************************************************************
//Open Database Connection
//************************************************************************* 
$dbserver="ipaddress";
$dbusername="tester4";
$dbpassword="password1234";
$defaultdb="testdb";
$cn = mssql_connect($dbserver,$dbusername,$dbpassword) or die("Connection Error");
$db = mssql_select_db($defaultdb,$cn) or die("Database Error");
echo "Connection Success"
?>

Some of the useful commands,

odbcinst -j         # to check the ODBC version and configuration.
odbcinst -q -d   # View loaded drivers.
odbcinst -q -s   # View DSN entry
tsql -LH 22.33.55.44   #To list MSSQL server instant name,version etc.,
osql -S DSN -U Username -P ‘Password’ #list the configuration files loaded to connect ODBC

Advertisements

3 thoughts on “ERROR: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

    itechonology said:
    May 3, 2013 at 11:45 am

    Reblogged this on ITechonology.

    David said:
    May 14, 2014 at 1:17 am

    I’ve tried several different config options based on your post here as well as a very similar post at http://askubuntu.com/questions/167491/connecting-ms-sql-using-freetds-and-unixodbc-isql-no-default-driver-specified however when I try to test the connection with isql I continue to get “[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
    [ISQL]ERROR: Could not SQLConnect”.

    I am running Ubuntu 12.04 LTS server and installed freeTDS via apt-get.

    results of odbcinst -q -d:

    @cabana:/etc# odbcinst -q -d
    [TDSDRIVER]

    results of odbcinst -q- s

    @cabana:/etc# odbcinst -q -s
    [MSSQL]
    [SQL01]

    tds.driver.template:
    @cabana:/etc# cat tds.driver.template
    [TDSDRIVER]
    Description = FreeTDS driver
    Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    FileUsage = 1
    UsageCount =1

    tds.datasource.template
    @cabana:/etc# cat tds.datasource.template
    [SQL01]
    Driver = TDSDRIVER # name that we specified in the driver file
    Description = MSSQL ODBC Driver
    Trace = No
    TraceFile = /var/log/freetds.log
    Server = 10.0.0.200 # this name specified in the freetds.conf
    Port = 1433
    Database = MyDatabase
    TDS_Version = 8.0

    freetds.conf
    @cabana:/etc# cat freetds.conf
    [global]
    tds version = 8.0
    [MICROSOFT]
    host = 10.0.0.200
    port = 1433
    tds version = 8.0

    odbc.ini
    @cabana:/etc# cat odbc.ini
    [MSSQL]
    Driver = FreeTDS # name that we specified in the driver file
    Description = MSSQL ODBC Driver
    Trace = No
    TraceFile = /var/log/freetds.log
    Servername = 10.0.0.200 # this name specified in the freetds.conf
    Port = 1433
    Database = MyDatabase
    TDS_Version = 7.0

    [SQL01]
    Driver = TDSDRIVER # name that we specified in the driver file
    Description = MSSQL ODBC Driver
    Trace = No
    TraceFile = /var/log/freetds.log
    Server = 10.0.0.200 # this name specified in the freetds.conf
    Port = 1433
    Database = MyDatabase
    TDS_Version = 8.0

    odbcinst.ini
    @cabana:/etc# cat odbcinst.ini
    [TDSDRIVER]
    Description = FreeTDS driver
    Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    FileUsage = 1
    UsageCount = 2

    tsql is not installed so testing with isql I get this:
    @cabana:/etc# isql -v SQL01 user pass
    [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified
    [ISQL]ERROR: Could not SQLConnect

    What am I doing wrong?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s