Having Issues Connecting to Microsoft SQL Server Express (2008) from PHP

Question

As the title says, I'm having issues connecting to MSSQL from a PHP Script.

The setup:-

• PHP is running on an Apache Linux Server.
• Microsoft SQL Server 2008 is on an XP Machine.
• I've got Remote Connections turned on in the MSSQL Server.
• The database bggs does exist.
• The database is running (I can see a green arrow).
• I have no firewall on the XP Machine.

If you have any debugging questions, don't hesitate to ask.

I'm using the following test script.

<?php
    include('adodb5/adodb.inc.php');
    $db =& ADONewConnection('odbc_mssql');
    $db->debug = true;
    $dsn = "Driver={SQL Server};Server=ozmodiar;Database=bggs;";
    $db->Connect($dsn,'user','password');
    $rs = $db->Execute('select * from admin_users');
    print "<pre>";
    print_r($rs->GetRows());
    print "</pre>";
?>

Getting the following result.

Warning: odbc_connect() [function.odbc-connect]: SQL error: [unixODBC][Driver Manager]Data source name not found, and no default driver specified, SQL state IM002 in SQLConnect in /mnt/filestore/vhost/bggs/hr_database/Dev/includes/adodb5/drivers/adodb-odbc.inc.php on line 60 (odbc_mssql): SET CONCAT_NULL_YIELDS_NULL OFF
Warning: odbc_exec(): supplied argument is not a valid ODBC-Link resource in /mnt/filestore/vhost/bggs/hr_database/Dev/includes/adodb5/drivers/adodb-odbc.inc.php on line 530 IM002: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

ADOConnection._Execute(SET CONCAT_NULL_YIELDS_NULL OFF, false) % line 1017, file: adodb.inc.php ADOConnection.Execute(SET CONCAT_NULL_YIELDS_NULL OFF) % line 62, file: adodb-odbc.inc.php ADODB_odbc._connect(Driver={SQL Server};Server=192.168.70.130;Database=bggs;, sa, knd121, ) % line 524, file: adodb.inc.php ADOConnection.Connect(Driver={SQL Server};Server=192.168.70.130;Database=bggs;, sa, knd121) % line 9, file: test_db.php

Driver={SQL Server};Server=192.168.70.130;Database=bggs;: [unixODBC][Driver Manager]Data source name not found, and no default driver specified (odbc_mssql): select * from admin_users
Warning: odbc_exec(): supplied argument is not a valid ODBC-Link resource in /mnt/filestore/vhost/bggs/hr_database/Dev/includes/adodb5/drivers/adodb-odbc.inc.php on line 530 IM002: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

ADOConnection._Execute(select * from admin_users, false) % line 1017, file: adodb.inc.php ADOConnection.Execute(select * from admin_users) % line 10, file: test_db.php

Fatal error: Call to a member function GetRows() on a non-object in /mnt/filestore/vhost/bggs/hr_database/Dev/includes/test_db.php on line 12

Answer 1

Disclaimer: I've only queried data from Microsoft SQL Server 2000, but through PDO_DBLIB although I believe the pre-requisite of having freetds drivers for either ADODB or PDO_DBLIB a certainty.

Judging by the initial error message, you do not have necessary drivers installed. In order for your application to access the server, you will need to install freetds, which will install the necessary drivers and allow you to set up the the ODBC connections. Check out: http://www.linuxjournal.com/article/6636

It's important to note that you need to set the "tds version = x.x" in your freetds global config or specific OBDC connection (/etc/freetds/freetds.conf by default on debian) exactly right, or the protocol freedtds uses with your server will not be correct and the script will bomb.