PHPでSQLServerの名前付きインスタンスに接続する

概要

• PHP7.1のPDO/php_sqlsrvではポート番号指定が必要。
• SQL Server Browserにポート番号を教えてもらって、指定すればOK。

経緯

• PHP7.1で、SQLServerの名前付きインスタンスに接続できなかった。
• PHP5.6の時は問題なく接続できていた。
• PHP.netの説明を読んでも、名前付きインスタンスへの接続については書かれていない。
• ググってみても、名前付きインスタンスをやめて固定ポートにすれば解決するという情報ばかり。
• ほかのアプリでも使っているSQLServerなので、インスタンスの公開方法を変えるのは避けたい。

環境

PHPはremiリポジトリからyumでインストールしたもの。

• CentOS6 / PHP5.6 ：php_pdo, php_mssql
• CentOS7 / PHP7.1 ：php_pdo, php_sqlsrv

接続するのにFreeTDSを使っていたのが、microsoft製のドライバ経由に変わったらしい。

接続するコード

// PHP5.6で接続できたDSN
$dsn = "dblib:host={$ipaddr}\\SQLEXPRESS;dbname={$dbname};charset=UTF-8";

// 以前テストしたときにwindows上のphp_pdo_sqlsrvをつかって接続できたDSN
// 当時の環境は不詳。PHP5.3だったような。
$dsn = "sqlsrv:server={$ipaddr}\\SQLEXPRESS;database={$dbname}";

// PDOで接続。
$pdo = new PDO($dsn, 'dbuser', 'password');

PDOで接続する部分は一緒なので、以下はDSNのみ記載する。
windowsで成功していたのと同じように書いてみるが、つながらない。

//接続に失敗する
$dsn = "sqlsrv={$ipaddr}\\SQLEXPRESS;database={$dbname}";

失敗する様子をWiresharkでモニタしてみたところ、tcp/1433に接続しようとして応答がない状態。
インスタンスの動的ポート番号を調べたところ49174だったので、PDO_SQLSRV DSNの説明に従ってポート番号を入れてみると接続に成功した。

//つながった
$dsn = "sqlsrv={$ipaddr}\\SQLEXPRESS,49174;database={$dbname}";

動的ポートに対応する

SQLServerの名前付きインスタンスは動的ポート番号でListenしているため、ポート番号を調べる必要がある。
SQLServerのポート番号を解決するために、SQL Server Browserというサービスが動いているらしい。

SQL Server Browserに問い合わせる方法....

udp/1434でListenしていることは分かったものの、どうすればいいのかは調べきれず。

実際に問い合わせて観察してみた

PHP5.6ではポート番号を指定しなくても接続できたので、PHP5.6 (dblib) はSQL Server Browser に問い合わせているはず。
Wiresharkでパケットをモニタしながら接続してみたところ、

「SQLEXPRESS」という文字列の前に0x04、後に0x00をつけて送っていた。

手動で問い合わせてみると...

シェルから問い合わせてみた結果 (PC名は伏せ字に修正)。

$ echo -en "\04SQLEXPRESS\00" | nc -u 192.168.18.99 1434
?ServerName;*****;InstanceName;SQLEXPRESS;IsClustered;No;Version;9.00.4035.00;tcp;49174;np;\\*****\pipe\MSSQL$SQLEXPRESS\sql\query;;

セミコロン区切りで、項目名・内容の繰り返しで返ってくるようで、「tcp;<ポート番号>」を拾えばOKな印象。

PHPで接続する

クラスメソッドにして、コンストラクタから呼ぶ形にする。

class MyPDO_SQLSRV extends PDO {
    public function __construct($ipaddr, $dbuser, $password, $dbname) {
        $port = self::querySqlBrowser($ipaddr);
        if ($port === false) {
            throw new Exception('failed to find port number.');
        }
        $dsn = "sqlsrv:Server={$ipaddr}\\SQLEXPRESS,{$port};Database={$dbname}";
        parent::__construct($dsn, $dbuser, $password);
    }
    
    public static function querySqlBrowser($ipaddr) {
        $fp = fsockopen("udp://{$ipaddr}", 1434, $errno, $errstr);
        fwrite($fp, "\04SQLEXPRESS\00");
        $res = fread($fp, 1024);
        fclose($fp);
        if (!preg_match('/;tcp;([1-9][0-9]{0,4});/', $res, $matches)) {
            return false;
        }
        return $matches[1];
    }
}

//接続できた
$mypdo = new MyPDO_SQLSRV($ipaddr, 'dbuser', 'password', 'dbname');