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

Posted on May 2, 2013 Updated on May 2, 2013

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