MSSQL Server's Native ODBC Driver for Linux and PHP 5.4

Question

I have Apache 2.2.16 and PHP 5.4.3 on a Linux Debian 6 x64.

To install the MSSQL Server's Native ODBC Driver for Linux, I use the following instructions: http://www.codesynthesis.com/~boris/blog/2011/12/02/microsoft-sql-server-odbc-driver-linux/

I configured my odbc.ini file this way:

[mydsn]
Driver      = SQL Server Native Client 11.0
Database    = datbase
Server      = xxx.xxx.xxx.xxx,port

and my odbcinst.ini this way:

[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
Threading=1
UsageCount=1

To test, I run the following command:

$ isql -v mydsn dbusername dbpassword

And I got success:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Then, a use phpize to install unixODBC on PHP 5.4, using this: (The first command, ln -s ..., is used because ./configure can't find the headers of php on the default location)

$ sudo ln -s /usr/include/php5 /usr/include/php
$ phpize
$ ./configure --with-pdo-odbc=unixODBC && make && make test
$ sudo make install

On my phpinfo() I get:

PDO support - enabled
PDO drivers - odbc

PDO Driver for ODBC (unixODBC) - enabled
ODBC Connection Pooling        - Enabled, strict matching

Now it's time to test everything on a PHP 5.4 script:

<?php
    ini_set('display_errors', 1);
    error_reporting(E_ALL);

    $conn = new PDO('odbc:DSN=mydsn;UID='.$usr.';PWD='.$psw);

    $query = 'select * from my_table'; 
    $stmt = $conn->prepare($query);
    $stmt->execute();
    while ($row = $stmt->fetch()) {
        echo "<pre>";
        print_r($row);
        echo "</pre>";
    }
?>

But it doesn't work... I got this error message:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[01000] SQLDriverConnect: 0 
[unixODBC][Driver Manager]Can't open lib '/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0' : file not found' 
in /var/www/testemssql.php:17 
Stack trace: 
#0 /var/www/testemssql.php(17): PDO->__construct('odbc:DSN=mydsn...') 
#1 {main} thrown in /var/www/testemssql.php on line 17

So my question is: what is happen? What configuration I'm missing? How to set up correctly the MSSQL Server's Native ODBC Driver on Linux and PHP 5.4?

Ps.: When I try to use the odbc_connect() PHP says the function doesn't exist.

Solution 1

MSSQL Server's Native ODBC Driver for Linux has a bug on it

To connect MS SQL Server correctly, use FreeTDS See more details in : PHP 5.4 on Linux: How to connect with MS SQL Server 2008?

Solution 2

I know this is a little late, but since I hit this thread while banging my head against the exact same issue here are some suggestions for anyone running into it in future :-)

1) Check the permissions on libsqlncli-11.0.so.1790.0 to make sure whatever user Apache is running as can access it (should have read and execute)

2) Use ldd to check that none of the dependencies are missing - based on the fact that isql is working above I'd say they are OK (hint: you're looking for "not found"):

ldd /opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0

3) Try running your php script from the command line rather than through Apache. If it works like that, go to stop 4. If it doesn't, I'd suggest running through strace to see what it is actually doing.

4) This is the one that did it for me! Try turning off SELinux (i.e. set to not enforcing / permissive mode) and hitting the page in Apache again. I'm not sure exactly what it was blocking (haven't had time or inclination to get into the details yet) but as soon as it was off everything worked like a charm. For anyone with the inclination I'm you could dig into it and figure out how to fix this without disabling completely :-)

Exact commands for disabling SELinux may vary based on your OS but for me (on CentOS) this worked:

http://rbgeek.wordpress.com/2012/08/06/how-to-disable-selinux-on-centos-without-rebooting/

Good luck!

Solution 3

The same configuration, except for odbcinst.ini:

[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
UsageCount=1

Retry after removing this line:

Threading=1

And your php script works fine to me.

Hope this may help you.

Solution 4

If you patch and recompile php with the patch attached to https://bugs.php.net/bug.php?id=61777 it will resolve the issue.

Also check this blog post for examples for the DSN and usage:

http://strangenut.com/blogs/dacrowlah/archive/2012/04/13/installing-and-using-the-microsoft-sql-server-odbc-driver-for-linux.aspx

View more solutions