unixODBC - MS SQL Server/PHP - with specifics for Red Hat/Fedora Linux Core 4 and Apache

Feb 2006

Contents

Introduction

This document will help unixODBC users access an MSSQL Server from a unixODBC equipped client. As an added feature, it will show you how to make this all work with php. Specific details have been added for an installation of Red Hat Fedora Core 4 Linux and Apache using PHP5.

Credit has to go to the original author, Peter Harvey, and the previous person who updated it, Brian Morton. The original document can be found here: http://www.unixodbc.org/doc/FreeTDS.html I used the original document to install FreeTDC, unixODBC on Fedora Core 4 (FC4) server running Apache and PHP5 to access a Windows 2003 server and Microsoft MS SQL 2000.

We will assume that you have a recent version of unixODBC installed on your system and that you have an MS SQL Server running somewhere. The SQL Server should allow remote access and support connections using TCP/IP. Connections using named pipes are currently unsupported. Of course; you will want a login id and password.

Background

First; a bit of background. MS SQL Server was spawned by a partnership between Microsoft and Sybase. Subsequently these two SQL Servers are very similar. In fact; so similar that the communication protocol used by client software to communicate with these servers are virtually identical. This protocol is called Tabular Data Stream (TDS).

Brian Bruns started a open source project to provide an implementation of the TDS protocol to anyone who wanted it. This project is called FreeTDS.

Getting It

FreeTDS comes with several interfaces to TDS including ODBC. As of the previous version of this article, the features of the FreeTDS ODBC driver were limited, but I cannot speak of them at this point. I would imagine they have improved somewhat.

FreeTDS works well with unixODBC but you want to get a recent version. v0.63 is the most recent version at this time. This document is primarily aimed at MSSQL Server 2000, which is the most popular version out there presently. For other versions, you may need to change the TDSVer flag when compiling FreeTDS and configuring unixODBC. Consult http://www.freetds.org/userguide/choosingtdsprotocol.htm#TAB.PROTOCOL.BY.PRODUCT for what TDS version corresponds to your server version. 8.0 is the TDS protocol version of MSSQL 2000.

You will also need:The source files for unixODBC. Installing the RPM in RedHat only installs configured files and does not install the source unless you have chosen to include the source. You will need to compile it to create the lib files needed for FreeTDS compile.
KDE installed if you want to use the GUI utility ODBCConfig. This GUI really simplifies things but you will have to modify one file, odbc.ini, because the GUI does not allow you to set the "Server =" parameter.
File named odbc.so for PHP. Find it here: http://rpmfind.net/linux/rpm2html/search.php?query=odbc.so It is installed into /usr/lib/php/modules for FC4.

Installing

The easiest way to get FreeTDS is through your distro's package manager, if one is available. I did this on a debian 3.1 unstable system. Unstable is a package release level of debian that corresponds to packages more stable than bleeding-edge, but not yet stable enough to be in their stable repository. To change from stable to unstable, simply edit your /etc/apt/sources.list file and change stable to unstable, except for the security source. As of writing, the debian package tdsodbc is in unstable. Downloading and installing this package will add the odbc driver for FreeTDS/Sybase and if you have debconf configured and unixODBC already installed (a prerequisite for this tutorial), it will register the driver entry with unixODBC. Your mileage may vary, consult your distro's package repository if you are in doubt. If you are able to do this, you can skip to the last step of configuration, creating an odbc datasource name.

If your package managed doesn't have such a driver available, you have to download the FreeTDS source code and compile it. Usually installing from source is a great way to go but it is more involved for the average user.

# tar zxvf freetds-0.63.tar.gz
# cd freetds-0.63
unpacking

Once you have unpacked the source (typically using something like "tar zxvf freetds-0.63.tar.gz") you will find all of the information you need to "./configure", "make", and "make install" (as root) FreeTDS in the "INSTALL" and "README" files.

Start by setting some environment variables. For example; bash shell user can add the following to /etc/profile.

#
# TDS
#
SYBASE=/usr/local/freetds
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$SYBASE/lib
 
export SYBASE LD_LIBRARY_PATH
 
set environment variables for bash

The "configure" options can be viewed by executing "./configure --help". The main thing to take note of is the "--with-tdsver" configure option.


# ./configure --with-tdsver=8.0 --with-unixodbc=/usr/local
# make
# make install
Building FreeTDS

The FreeTDS ODBC driver should be installed in /usr/local/freetds/lib - look for libtdsodbc.so.

Configuration

Registering the ODBC Driver With unixODBC

unixODBC needs to know about all ODBC drivers you intend to use. The best way to accomplished this is using the ODBCConfig graphical program which comes with unixODBC. An alternative method is to use the 'odbcinst' command which also comes with unixODBC. We will focus on using the odbcinst command. Create a file named tds.driver.template with a few lines describing the driver.

[FreeTDS]
Description     = v0.63 with protocol v8.0
Driver          = /usr/local/freetds/lib/libtdsodbc.so
tds.driver.template

Execute odbcinst, telling it to install a driver entry using the tds.driver.template file. Note that you must leave a space between the '-t' switch and the template file name.


# odbcinst -i -d -f tds.driver.template
 
register ODBC driver

Creating an ODBC Data Source Name

ODBC client applications will typically work with ODBC Data Source Names (DSN). The best way to create, edit and remove a DSN is to use the ODBCConfig tool. Again we will use the odbcinst command instead. We will do this because the ODBCConfig program is fairly self explanatory and because not all users will have the unixODBC GUI tools installed. Again, we start by creating a template file - this one is called tds.datasource.template and contains some options such as the default database and UID. Note; you will want to use your own Server address.

[MSSQLTestServer]
Driver  = FreeTDS
Description     = Northwind sample database
Trace   = No
Server      = 192.168.1.25
Port      = 1433
Database        = Northwind
tds.datasource.template

Valid attributes for use in odbc.ini (or the connection string) can be found at: http://www.freetds.org/userguide/odbcconnattr.htm

[SybaseTestServer]
Driver  = FreeTDS
Description     = Test Sybase Database with FreeTDS
Trace   = No
Server      = 192.168.1.25
Port      = 5050
TDS Version      = 5.0
Database        = testdb
tds.datasource.template

Note: Do NOT use "Servername" in an ODBC-only configuration! Use of "Server" should be preferred over "Address" (see http://lists.ibiblio.org/pipermail/freetds/2004q2/016086.html) This cannot be be emphasized more. If you have a Servername = <name_or_ip> in odbc.ini your setup will simply NOT work. Change Servername = to Server = and things will start working. In addition, the ODBCConfig GUI program does NOT let you set Server, only Servername. If you use this GUI, leave Servername field empty and manually edit odbc.ini.

Note; we have executed previous commands as root (denoted by leading '#' character on given commands) but here we execute the command as a regular user. This is significant. All users of the system share FreeTDS and the ODBC Drivers but each user has his/her own list of DSN's (view odbcinst output for help on registering as a system DSN available to all users). So create the DSN as the user who is going to be using it.


$ odbcinst -i -s -f tds.datasource.template -l
 
create ODBC data source

Now, to make this work with PHP is very easy. Assuming you have a package manager, download and install the odbc extension for PHP if it isn't already installed. If you don't have a package manager, you will have to compile odbc.so. To enable odbc in PHP, just modify the php.ini file, usually located in /etc. Add this line anywhere in the file (convention dictates that it should be placed with the other extension calls, so search your file for the word "extension").

http://rpmfind.net/linux/rpm2html/search.php?query=odbc.so


extension = odbc.so
 
php.ini

Note: In FC4 and PHP5, do not put the "extension = odbc.so" in php.ini. Instead, when PHP loads it looks in directory /etc/php.d for .ini files to load. Create a file in /etc/php.d called odbc.ini:


extension=odbc.so
 
odbc.ini

Now, reload apache as root to make the changes effective.


# apachectl graceful
 
reloading apache as root

In FC4:


# /sbin/service httpd restart
 
reloading apache as root in FC4

Gotchas

In FC4 I found that FreeTDS and unixODBC looks for odbc.ini and odbcinst.ini in the directory /usr/local/etc. Apache's PHP5 looks for those two files in /etc. The ODBCConfig graphical program in KDE modifies those two files in /usr/local/etc. I recommend putting them in /usr/local/etc and creating symbolic links in /etc to those two files.


$ cd /etc
$ ln -s /usr/local/etc/odbc.ini odbc.ini
$ ln -s /usr/local/etc/odbcinst.ini
 
create symbolic links for PHP

Using ODBCConfig for configuration

ODBCConfig GUI makes the configuration a whole lot easier. You do NOT have to make the tds.driver.template or tds.datasource.template and install them. ODBCConfig will create the files in /usr/local/etc with proper format with the one exception. ODBCConfig does NOT have a field to enter Server = . It only has Servername. LEAVE THIS FIELD BLANK if you are only using unixODBC. You will have to edit the two files /usr/local/etc/odbc.ini and /usr/local/etc/odbcinst.ini after ODBCConfig creates them, add your line Server = in each and then make the symbolic links in /etc. Hint: If you don't know where to stick the Server = line in the two files, put your server info into Servername in ODBCConfig. Then edit the files and change Servername = to Server =. Save your file.

Steps in running ODBCConfig. You will run ODBCConfig, create a Driver, then Create a System DNS.

Log in as root. Run the command ODBCConfig
Click tab "DRIVERS"
Click "Add"
Fill in:
   Name
   Description
   Driver
      (you can browse for the driver or enter /usr/local/lib/libtdsodbc.so)
   Setup
      (you can browse for the setup file or enter /usr/local/lib/libtdsS.so)
Clear out anything in Driver64 and Setup64 unless you are using 64 bit version of Linux
Click the check mark button to save.

Click tab "System DNS"
Click "Add"
Select a driver from the list. Look for the one you just created. Select it by clicking once. Click "Ok".
Fill in:
   Name
   Description
   Servername (leave blank or enter your IP or servername and edit the .ini files later changing Servername to Server.)
   Database
      (this is your MS SQL database name)
   Port
      (Leave blank UID, PWD. You will supply user and password from your PHP code or isql command line test below.)
Click the check mark in upper left corner to save.
Click OK to exit ODBCConfig

Edit the files /usr/local/etc/odbc.ini and /usr/local/etc/odbcinst.ini fixing Servername = and Server =.

A few notes on MS SQL

I won't go into detail here. I assume since you use that propriatry MeSs Microsoft sells, you have the basic configuration done. MS SQL must be installed in mixed mode authentication. We will check this later after creating a user for access from your *nix system.

Using Enterprise Manager in MS SQL 2000, Navigate through the tree to Security, Logins. Create a New Login.
Enter:
   Name
   Check the radio button for "SQL Server Authentication"
      Enter a password
Click OK

Navigate Tree to your Database, Users.
Create a New Database User
   Select name from the drop down list.
   Check the proper "Permit in Database Role" selections
      For reading only, "Public" and "db_datareader" would be checked.
Select "Permissions" and check the proper boxes.

Follow these instructions to check your installation is in Mixed Mode: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319930

Test

unixODBC comes with a variety of tools which allow you to test. We will use the command line tool 'isql'. isql allows us to submit commands (typically SQL statements) to the DSN and see the results.


$ isql -v MSSQLTestServer <username> <password>
SQL>
 
test using isql

You should see a connected message and an SQL prompt. If this fails then you may have a configuration problem or you may simply be using the incorrect UserName and PWD.

Now try a simple SQL statement.


SQL> SELECT contactname FROM customers
 
simple sql statement

To make sure that PHP is able to communicate with your SQL server, paste this code into a file with a .php extension and place it in your apache document root. Be sure to put an opening php tag before the code. I couldn't place one here because it would invoke my php interpreter on this page.



# connect to a DSN "MSSQLTest" with a user "cheech" and password "chong"
$connect = odbc_connect("MSSQLTest", "cheech", "chong");

# query the users table for all fields
$query = "SELECT * FROM users";

# perform the query
$result = odbc_exec($connect, $query);

# fetch the data from the database
while(odbc_fetch_row($result)) {
$field1 = odbc_result($result, 1);
$field2 = odbc_result($result, 2);
print("$field1 $field2\n");
}

# close the connection
odbc_close($connect);

?>
 
test using odbc and PHP

Getting Help

OK, so things went wrong somewhere... what do you do? Start by making sure you can access your dedicated server hosting remotely over TCP/IP. You can do this by using a Windows box with a different set of tools. If this works then the problem likely lies within the realm of what we covered here.

Check the FreeTDS folks and see if you can get an answer there. If FreeTDS seems be ok then check the unixODBC folks.


 
 


Peter Harvey
CodeByDesign
 
Updated By: Brian Morton
Nerd Happens
 
Updated 2/11/2006 by: Bob Abbott
TechniServe Corporation