ODBC, FreeTDS and Microsoft SQL (and php)

Submitted by ivan on Sun, 08/26/2007 - 07:56.

What is it all about

I had to connect to a MS SQL server from php from a Linux box. First I tried with php mssql_* functions, but they are not compiled nor the php MS SQL support is available through deb packages.
So I tried to use odbc_* functions but after specifying everything I thought reasonable for the connection I was still havign this error:

Warning: odbc_pconnect(): SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect in /home/ivan/Documents/kerberos/work/gorilla/scripts/cover_note/dbconn.php on line 11
no connection

I googled and I discovered I had to install ODBC drivers... at my surprise installing unixodbc was not enough.

I didn't want to install anything not in Debian, not I was willing to compile php and make my own deb package.

What you've to look at is here even if it has a small error (-t vs. -f in the examples):
Linux/UNIX ODBC and expecially here: unixODBC - MS SQL Server/PHP

Step by step guide

Getting the right stuff for odbc and FreeTDS in sarge and sid

In sarge and sid the odbc stuff has the same name unixodbc. You don't want to take it in directly if you're planning to use it with PHP since it will be taken in by php*-odbc.
odbcunix won't be enough to connect to MS SQL, you need a driver and the best easiest one to install I've found was FreeTDS.
You may find useful a set of GUI utilities in unixodbc-bin. I used ODBCConfig just to discover I didn't have any driver installed.

It may be puzling, even if it is written in the description of the package, to discover that the binaries of the lib in sarge are inside a -dev package: freetds-dev. In sid (and maybe testing/etch) the libs are in tdsodbc.

Setting up the driver and the DSN

  • Log as root
  • Create a file in your home a tds.driver.template (or any reasonabel choice) file with this content:
    [FreeTDS]
    Description = FreeTDS 0.63-3, unixodbc 2.2.11-13
    Driver = /usr/local/freetds/lib/libtdsodbc.so
  • Create a DSNNAME.datasource file with this content:
    [DSNNAME]
    Driver = FreeTDS
    Description = A good description of your DB
    Trace = No
    Server = [hostname]
    Port = 1433
    Database = [Databasename]
    #UID = [USERNAME]
    #PWD = [PASSWORD]
    #LogonUser=[USERNAME]
    #LogonAuth=[PASSWORD]
  • Run odbcinst -i -d -f tds.driver.template
  • Run odbcinst -i -s -l -f DSNNAME.datasource for a system DSN

Using a system DSN will make it available to other users. Otherwise create the user DSN from the user that will access the DB. Consider that if you're using php as cgi it may run under the web server user and it may be tricky to use a user DSN.

If you've several instance of MS SQL on the same box, you just have to adjust the Port as explained here:
Connection attributes
"Server: Hostname of a server. Used in an ODBC-only configuration. As of version 0.64 you can specify a Microsoft SQL Server instance in the form of server\instance. This isn't required to connect to an instance, and is in fact slightly slower to set up the conncection, because it entails an extra round trip to the server. To avoid that, ask to your administrator for the port number of each instance, and create separate server entries for each one."

Test the DSN and driver

isql -v DSNNAME USERNAME PASSWORD
This works on sid and sarge.

Installing and testing PHP support

Now install php4-odbc on sarge or php5-odbc on sid (testing/etch?) end test this code:

#!/usr/bin/php -q
<?php
$db_dsn="DSNNAME";
$db_user="USERNAME";
$db_pass="PASSWORD";

if ($conn=odbc_pconnect($db_dsn, $db_user, $db_pass)) {
    $SQLstr = "SELECT top 10 * FROM  sysobjects";
     $rs = odbc_exec($conn,$SQLstr);
    while($row = (odbc_fetch_array($rs)))
     print_r($serv);
} else { print "no connection\n"; }
?>

Unfortunately on sid I get a Segmentation fault but on sarge it works.

Security

Consider that when you access the DB through isql in the way explained above you're exposing the pw/un to anyone typing ps aux on your system.
I doubt storing them on your system DSN will improve the situation.

FreeTDS, Sybase and sqsh

isql is a bit of pain. You can't do things like \i somestuff.sql etc... as you can do in psql. But skipping ODBC and going directly through FreeTDS and use sqsh.
Here you can find some notes about using sqsh on MS SQL

PHP & mssql_ vs. sybase_

I haven't tested yet but if you need to connect to MS SQL through PHP another and maybe more efficient (performance and installation hassle) solution would be to use sybase_ class of functions.
To use them you'll have to install php4-sybase or php5-sybase

Other links

Accessing Microsoft SQL Server from PHP under Apache on UNIX or Linux
Accessing ODBC Databases with PHP and PEAR DB
Enabling ODBC support in PHP under Apache pointing out some problems you may have