Connect to SQL Azure from PHP on Ubuntu

Question

I'm attempting to connect to a SQL Azure database via PHP running on an Ubuntu 11.04 server.

The server is running PHP Version => 5.3.5-1ubuntu7.11.

I've installed freetds-bin, freetds-common, tdsodbc, odbcinst, php5-odbc and unixodbc using apt-get install multiple times. I attempted to compile FreeTDS with SSL support, but am not sure that was successful.

At this point, I receive an error "08S01 - Communication link failure" when attempting to connect using the isql tool. A Microsoft article explains the error as "The communication link between the driver and the data source to which the driver was attempting to connect failed before the SQLDriverConnect function completed processing." Some research on that specifically points to lack of SSL support in FreeTDS, but I'm unclear how to verify that has been enabled.

I will using either PHP Data Objects or mssql_* functions to connect to the SQL Azure database. I'm less familiar with PDO, but it seems that PDO does not necessarily use ODBC? I'm quite unclear on that, and I suspect it's leading me to troubleshoot problems seen by isql that are unrelated to the problems I'm seeing in PHP. Do connectivity problems with the isql tool relate to connectivity problems in either PDO or mssql_* functions in PHP?

My latest attempt, using PDO, is:

<?php

$c = new PDO("odbc:Driver=FreeTDS;Port=1433;Server=sssssssssss.database.windows.net;Database=db_xxxxx_xxx_xxx;UID=db_xxxxx_xxx_xxx_ExternalWriter;PWD=ppppppppp");

?>

This code generates the following errors in my Apache log file:

[Tue Dec 24 13:23:10 2013] [error] [client 10.1.1.11] PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[08S01] SQLDriverConnect: 20004 [unixODBC][FreeTDS][SQL Server]Read from the server failed' in /var/www/test/pdo.php:3\nStack trace:\n#0 /var/www/test/pdo.php(3): PDO->__construct('odbc:Driver=Fre...')\n#1 {main}\n thrown in /var/www/test/pdo.php on line 3

My /etc/freetds/freetds.conf:

[global]
        # TDS protocol version
        tds version = 9.1

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
        dump file = /tmp/freetds.log
        debug flags = 0xffff

        # Command and connection timeouts
        ;       timeout = 10
        ;       connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.
        # Try setting 'text size' to a more reasonable limit
        text size = 64512

# A typical Microsoft server
[FreeTDS]
        host = ssssssssss.database.windows.net
        port = 1433
        tds version = 9.1
        client charset = UTF-8

/etc/odbc.ini:

[TS]
Description             = "test"
Driver                  = FreeTDS
Server                  = sssssssssssss.database.windows.net
Port                    = 1433
Database                = db_xxxxxxx_xxx_xxx

/etc/odbcinst.ini

[FreeTDS]
Description     = tdsodbc
Driver          = /usr/lib/odbc/libtdsodbc.so

Any help on this mess would be very appreciated. I'm clearly lost at this point. Thanks!

Answer 1

I've not tried it with Azure specifically, but on the local SQL Server machines we have here I found the php5-sybase module with PDO to be massively easier to live with than freetds:

apt-get install php5-sybase

<?php
$dsn = 'dblib:dbname=TestDB;host=sqlserver;charset=UTF-8';
$dbh = new PDO($dsn, 'username', 'password');

Also, when troubleshooting issues, I find looking at Wireshark traces of the DB traffic to be enlightening, as there are often very helpful messages emitted by SQL server that don't make it out in the PDO error.