Cannot connect to MS SQL Server using PDO

Question

The last 2 days I've spent a lot of time trying to figure out how to connect to MS SQL server hosted on Azure using PHP from an Ubuntu 14.04 server.

The code responsible to initiate the connection to the remote database is the following:

public function connect()
{
    try
    {
        $this->databaseConnection = new \PDO(
            'dblib:host='.$this->hostname.':'.$this->port.';dbname='.$this->databaseName.';',
            $this->username,
            $this->password
        );
        $this->databaseConnection->setAttribute(
            \PDO::ATTR_ERRMODE,
            \PDO::ERRMODE_EXCEPTION
        );
    }
    catch (\PDOException $e)
    {
        throw new \Exception('Failed to connect to the database: '.$e->getMessage());
    }
}

And every time I try to connect I receive:

Failed to connect to the database: SQLSTATE[01002] Adaptive Server connection failed (severity 9)

The problem is that this is a very general error and there's no way for me to understand what's causing the issue.

This is my /etc/freetds.conf configuration:

[placeholder.database.windows.net]
    host = placeholder.database.windows.net
    port = 1433
    tds version = 7.2
    client charset = UTF-8

And this is the tsql -C output:

Compile-time settings (established with the "configure" script)
                        Version: freetds v0.91
         freetds.conf directory: /etc/freetds
 MS db-lib source compatibility: no
    Sybase binary compatibility: yes
                  Thread safety: yes
                  iconv library: yes
                    TDS version: 4.2
                          iODBC: no
                       unixodbc: yes
          SSPI "trusted" logins: no
                       Kerberos: yes

In addition to this, I tried making some test using mssql_* functions and even if I was able to successfully connect to the database most of the queries didn't work.

Have you tried checking out the answers here: http://stackoverflow.com/questions/24895605/php-pdo-mssql-sqlstate01002-adaptive-server-connection-failed-severity-9 — slax0r, Apr 26 '16 at 08:40
I'm basically in the same condition. Anyway I that doesn't seem to help. — siannone, Apr 26 '16 at 08:45
@low_rents the driver *sqlsrv* is not available on Ubuntu (unless you compile it yourself I think). — siannone, Apr 26 '16 at 08:49
ah ok - didn't know that - just checked out how I connect to a MS SQL server. — low_rents, Apr 26 '16 at 08:51

score 1 · Answer 1 · answered Apr 27 '16 at 08:33

For a simple solution for one sqlsrv server connection in your application, you can modify the tds version to 8.0 under the [global] section of the freetds.conf, e.g.

[global]
    tds version = 8.0

Then test in PHP script:

try {
$pdo = new PDO("dblib:host=<azure_sql_name>.database.windows.net;dbname=<dbname>", "<username>", "<password>");
} catch (PDOException $e) {
echo "Error: " . $e->getMessage() . "\n";
}

It works fine on my side after the modification. Any further concern, please feel free to let me know.

siannone · Accepted Answer · 2016-07-04T14:27:28.187

The problem was caused by 2 different issues.

The first one was related to a wrong FreeTDS configuration. The freetds.con global section needs to be like this:

[global]
    tds version = 8.0

The second one was caused by the fact that the username must be specified in the following way when connecting to a MSSQL server hosted on Azure:

<username>@<freeTDSServerName>

Where the specific server configuration in freetds.conf looks something like this:

[<freeTDSServerName>]
    database = <databaseName>
    host = <serverName>.database.windows.net
    port = 1433
    tds version = 8.0

So the PHP DB connection will look like something like this:

$this->databaseConnection = new \PDO(
    'dblib:host='.$this->hostname.':'.$this->port.';dbname='.$this->databaseName.';',
    $this->username.'@<freeTDSName>',
    $this->password
);

Cannot connect to MS SQL Server using PDO

2 Answers2