How to connect your linux box to a MS SQL Server via ODBC using PHP5 and ADOdb
Rev. 0.1 - 28/Oct/2010
.lowfidelity heavy industries
by smalltalk in a pretty cold night
This paper is talking about setting up and configuring a connection to a Microsoft SQL Database Management System from your Ubuntu Linux box using an ODBC connection. The ODBC driver manager of our choice is unixODBC.
It provides ODBC on non windows platforms. The corresponding ODBC driver that is able to talk to the MS SQL database will be FreeTDS.
Furthermore we will make a connection to the MS SQL Server using PHP5, ODBC and the PHP ADODB Library. Instead of this library probably also built in php functionality could've been used for the php programming part. Refer to mssql_connect and php5-sybasepackage for further information.
The information presented in this paper focuses on the configuration mentioned above. Anyway most parts of the text are pretty generic as far as it concerns ODBC on your linux box. Working-Title: How to prevent passing out on a park bench while waiting for the nearby Mc Donalds Restaurant to open their breakfast buffet.
The debug environment
The box running the PHP application that connects to the MS SQL Server
- Linux 2.6.3x, Ubuntu 9.10 Karmic Koala
- Apache 2.2.x (Ubuntu)
- PHP 5.2.10-2ubuntu6.5 with Suhosin-Patch 0.9.7 (cli)
- ADOdb 5.11 (not installed from apt)
The machine running the MS SQL Server
Boring Database Connectivity Theory
| ADOdb delivers many different ways to connect to a database server. We're going to use an ODBC based approach which should pretty easily be portable among different systems. The PHP application uses ADOdb library's connect function and a corresponding connection string to connect to the database. The connection string uses a "DRIVER=" parameter to grab the necessary information about the driver (and, depending on our configuration, about the database server. ie. host, port, username) from the ODBC Driver Manager's settings file. Finally all the "talking" to the Database is done by the FreeTDS ODBC Driver. The freetds.org website states that:
The TDS protocol was designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine in 1984. The problem Sybase faced then still exists: There was no commonly accepted application-level protocol to transfer data between a database server and its client. To encourage the use of their product, Sybase came up with a flexible pair of products called netlib and db-lib. [...] In 1990 Sybase entered into a technology sharing agreement with Microsoft which resulted in Microsoft marketing its own SQL Server. Microsoft kept the db-lib API and added ODBC. |  |
Installing ODBC libraries and drivers
Let's see what packages we have to put on our shopping list:
- ODBC Driver Manager - unixodbc
- ODBC Driver for MS SQL - tdsodbc
- PHP's ODBC libraries - php5-odbc
- ADOdb library for PHP - php5-adodb, libphp-adodb
In this test I just
downloaded the ADOdb library and extracted it in a folder together with my test.php script. If you're installing it from the apt repository you'll have to care for proper include paths in your php file.
Let's fire up apt-get and get those packages installed:
#get all the odbc packages
apt-get install unixodbc tdsodbc php5-odbc
#extract to your working directory
unzip adodb511.zip
#if you wanna use the same include paths as used in this example
mv adodb5 vadodb
Registering the ODBC Driver for MS SQL Server connections
After installing tdsodbc you'll find the new FreeTDS ODBC driver on your box:
/usr/lib/odbc/libtdsodbc.so
The packages also bring you
odbcinst, a commandline tool which assists you in installing drivers and data sources into your system. Following
Save Lono's approach I didn't use the tool for ODBC configuration at all. I manually registered the required ODBC Driver (FreeTDS) by editing the ODBC Driver Manager's config file:
/etc/odbcinst.ini
[FreeTDS]
Driver = /usr/lib/odbc/libtdsodbc.so
odbcinst.ini is the place to register all ODBC drivers that your application is going to refer to in it's DSN connection strings. If you forgot to register a driver here you'd most likely end up with an error message like:
Warning: odbc_exec(): supplied argument is not a valid ODBC-Link resource [...]
IM002: [unixODBC][Driver Manager]Data source name not found, and no default driver specified.
Test if MS SQL Server can be reached from your host
I strangled a while with kryptic php and adodb error messages before I realized, that I was stoopid enough not to set up proper port forwardings in our NAT. You should really make sure that connecting to your database works as it's supposed to. Even if you are a person like me, that always just supposes that the most obvious mistakes have not been made ... go and check again:
- Dial into your windows box running the MS Sql Server Service and make sure you can connect to the database using Microsoft SQL Server Management Studio. It important to set up the server to allow TCP / IP connections and not only named pipes. The default port would be 1433. Anyway MS SQL Server 2008 comes with the SQL Server Browser Service which can be used to dynamically assign port numbers to multiple server instances running on one box. In order to follow this paper make sure your server listens on a specific tcp port (1433 by default) and your firewall and NAT is configured to allow connections to it.
- When local connections work, jump onto any other windows box which can connect to your ms sql server via network/internet. Ping the host to make sure it's reachable. telnet into it (ie. 192.168.0.23 1433) and make sure the connection can be opened. No worries if it's dropped after a few seconds. Just make sure it can be opened up. Then create a new odbc data source. Fill in all the necessary info till you reach the last configuration screen and click the Test Data Source button. If you're presented with a TESTS COMPLETED SUCCSESSFULLY! message, you're good to continue. Otherwise stick your head out of the window and scream: "I'm as mad as hell and I'm not gonna take this anymore!" or just read Microsoft's MS SQL Server Installation and Configuration Guide
- If the connection from the windows box was made successfully you're free to jump over to your linux box. Again ping the host and telnet to it's port 1433. If this works install the FreeTDS command-line utilities package (apt-get install freetds-bin) and fire up tsql:
tsql -S 1.2.3.4 -p 1433 -U username -P password
if this returns something like:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
you can lean back, put on your relaxed smile and turn the music a bit louder, before you type in
quit. The connection to your server works as it's supposed to and the odbc driver is installed properly. Just make sure you register it in odbcinst.ini. Again, plz refer to
Save Lono's great paper for a testing approach using
isql commandline utility which could also tell you if your ODBC driver is registered properly.
For my part, I just supposed it's ok and went on implementing my PHP/ADOdb test script which should use our newly configured ODBC data source.
Using ADOdb from PHP5 for a odbc_mssql connection
If you followed this paper from the very beginning (which I would most likely not have done) you got everything set up now. Refer to the last chapter if you jumped right into the text at this position.
For our debugging purpose we now create a new folder on our linux box and
download n extract the adodb library into this directory. Rename the extracted directory to
vadodb or make sure that you fix the include paths in the example source below.
Then create a new php file and add the code below.
//optional includes
include_once("vadodb/adodb-error.inc.php");
include_once("vadodb/adodb-exceptions.inc.php");
//you're gonna need that one
include_once("vadodb/adodb.inc.php");
try
{
//define
connection type: ms sql server via odbc
$db = &ADONewConnection('odbc_mssql');
$db->debug = true;
//create DSN connection string. make sure driver is registered in /etc/odbcinst.ini
$myDSN="DRIVER=FreeTDS;" .
"SERVER=servers_ip_address;PORT=1433;" .
"UID=your_username;PWD=your_password;" .
"DATABASE=your_database_name;";
$db->Connect($myDSN);
}
catch (Exception $e)
{
die($e);
}
//let's assume i got a table named dbo.DrGonzo with 2 columns in my ms sql database
//let's query the first 10 results from that table
$rs = $db->Execute('SELECT TOP 10 * FROM dbo.DrGonzo');
if (!$rs)
{
echo "nothing returned :( ";
}
else
{
while (!$rs->EOF)
{
echo $rs->fields[0].' '.$rs->fields[1].'
';
$rs->MoveNext();
}
$rs->Close();
}
Most probably it would be a huawei device and I assume it is.
