LAMP環境からSQLServerインスタンスの接続ポート番号を取得する

制作中の業務用ウェブシステムがSQLServer縛りなので、ここ2~3年急にSQLServerをいじることが多くなってきています💦 別の大半の仕事は MySQLかSQLiteなのですが、やはり WindowsのデファクトスタンダードのDBMSはSQLServer、ツール類は断トツに整備されていじりやすい!

そんなことはともかく。いつも前置きが長いです。すみません。

通常SQLServerインスタンスへコンピューター名(or IPアドレス)\インスタンス名 で接続するので特に接続ポート番号(動的ポート)を意識せずとも接続できると思われます。

ただ、僕はLAMP環境メインでやってきているので、ASP.NETとかいうのは肌に合いませんし💦 今更関わろうとも思いません。(C#でのデスクトップアプリやツール類の作成は趣味でやっているのですが・・・)
Linux側からWindows Server+SQLServerにする場合、大抵の場合SQLServerインスタンスのポートを固定して、ファイヤーウォールでそのポート番号を開けて・・・というやり方が一番ラクですし、今開発中のものもそういう環境でやると思います。

で、ふと思ったのですが、動的ポートって「動的」って言うんだから変わることを前提にしないといけないなぁ・・・と漠然と思いまして、カンタンなSQLServerインスタンスの情報を取得する方法がないかと思いまして・・・このエントリはその時に調べた内容です。

Windowsクライアントメイン(大概がAccessランタイムアプリ)の職場環境だと、大抵の場合「名前付きインスタンス」で動的ポートになっていると思います。職場環境によってはポート固定への変更を頑なに拒まれることもあろうかと思いますし、結局のところ一体何番で接続していいのか困ることがあります。

とりあえず、ググるが、SQLServer Browserサービスに直接問合せることで、SQLServerインスタンスの情報を得られますよ~、的な、事しかわかりません。

マイクロソフトのドキュメントによると、SQLServer Browserへの問合せはSSRP(SQL Server Resolution Protocol)というプロトコルでアクセスするみたいです。
[MC-SQLR]: SQL Server Resolution Protocol

これを読むと、UDP/1434ポート(SQLServer Browserサービス)へ、下図のように、インスタンス名の前に0x04をつけて問い合わせると、セミコロン区切りで情報を投げてくれるそうです。

(上記URLのドキュメントより抜粋)

なんだ!カンタンじゃん!(ほんとかよ。。。)

もちろんですが、SQLServerホストのファイヤーウォールでUDP/1434ポートを開けておく必要がありますが、普通は空いているでしょう、さもないと外部クライアントから一切接続できなくなるし。

とりあえず、ターミナルからローカルで動いているSQLServer(Deverlopper edition)ホストへ netcat で叩いてみる。
インスタンス名は既定のインスタンス(MSSQLSERVER)。

$ echo -ne "\04mssqlserver" | nc -u localhost 1434
Y ServerName;XXXXXX;InstanceName;MSSQLSERVER;IsClustered;No;Version;14.0.1000.169;tcp;1433;;

一応ローカルで動かしているSQLServerは固定ポートで動かしているので 1433になってまつね。。。
これだけだとプロトコル・ポート番号とインスタンスのSQLServerのバージョンなどが取れます。IsClusteredは・・・なんなんでしょうね?無知ですみません。

結果のフォーマットは、先頭(0バイト名)に1バイトの識別記号?の\x05、1バイト目にペイロード長(終端ヌルを除く)を示す16ビット整数、3バイト目からペイロード・・・という風な感じ。ペイロードはセミコロン区切りで、名前1;値1;名前2;値2;….;; という風なフォーマット。これなら簡単に連想配列かなんかにパースできます。

んで、インスタンスの簡易情報を取得するコードを書いてみる。

<?php
/*************************************************************************************
SQLServer名前付きインスタンスの情報を得る。
File: query_sqlbrowser.php
*************************************************************************************/
function queryNamedInstance($host,$instanceName,$queryPort = 1434)
{
  static $timeout = 5;
  $fp = fsockopen('udp://'.$host,$queryPort,$errno,$errstr,$timeout);
  if($fp === false)
    throw new RuntimeException($errstr);

  $str = sprintf('%s%s%s',hex2bin('04'),$instanceName,hex2bin('00'));

  if(false === fwrite($fp,$str,strlen($str)))
    throw new RuntimeException(_('failed to fwrite to resouce pointer'));

  $rv = fread($fp,1500);
  fclose($fp);

  $head = substr($rv,0,1);
  list(,$len) = unpack('v',substr($rv,1,2));
  $data = explode(';',substr($rv,3,$len));
  $rv = array();
  $i = 0;
  while(array_key_exists($i,$data) && array_key_exists($i+1,$data))
  {
    if(!empty($data[$i]))
      $rv[$data[$i]] = $data[$i+1];

    $i+=2;
  }

  return $rv;
}

この関数を実行してみると

<?php
require 'query_sqlbrowser.php';
print_r(queryNamedInstance('IPアドレス','インスタンス名'));

/***** 出力 *****
Array
(
  [ServerName] => コンピュータ名
  [InstanceName] => インスタンス名
  [IsClustered] => No
  [Version] => 10.50.4000.0
  [tcp] => 49179
)
****************/

とりあえす、これで得られたポート番号をキャッシュしておき、接続試行がタイムアウトしたら、キャッシュ更新・・・という感じで組み込もうかと思います。

WSL(Ubuntu)-PHPからSQLServerへの道のり

2019年5月20日 若干修正


「CentOSからWindows上のSQLServerへの道のり」のつづきです。

LinuxからWindows上の SQLServerへのアクセス方法については、世間的にはあまり需要がないのでしょう。まぁ、Linuxとかのunix系のOSでは、SQLServerなんて採用しないし、逆も然り。。。困ったもんだ。。。

さて、Linuxの各ディストリビューション用のMicrosoft製のODBCドライバが提供されていて手順を踏むと、LinuxからSQLServerへODBC経由でアクセスすることができました。

今度は LAMP環境、PHPから文字化けさせずにCRUDすることができるか、ウェブアプリケーションをちゃんと実装できるか、確認です。とりあえずLinux各ディストリビューション用にマイクロソフト製のODBCドライバをインストールしていることが前提です。

PHPでたとえば、以下のようなコードでテーブルを作成し、SQLServer Management Studioとかで確認すると、日本語が全部文字化けします。
sqlcmdコマンドで日本語交じりのSQL文を発行すると化けないので、おそらく、PHPの問題でしょう。DSNで odbc: を使うのがだめなんでしょうね。odbcドライバをインストールしたんだから、当然odbcプレフィックスを使うものだと勘違いしてまして、これが罠でした・・・。日本語関係の処理が全くされない。。。この辺、よくわからん・・・。知識が圧倒的に足りない。SQLServerのODBCドライバとsqlsvr(PHP SQLServer用のドライバ?)の関係が全然わからない。PHPのマニュアル見てもよくわからない・・・。

// これが間違い
$pdo = new PDO('odbc:Driver={ODBC Driver 13 for SQL Server};Server=localhost;Database=Sample','dbuser','dbpass');
$pdo->exec('CREATE TABLE tbl_hoge(hoge_id int NOT NULL,hoge_txt nvarchar(100))');
$pdo->exec("INSERT INTO tbl_hoge values(1,N'日本語で挿入・・・なんかエロいな')");

接続文字列でエンコーディング関係のパラメータがあるのかなと、調べてみたけどないっぽい。さらにネットでの情報がほとんどなく。。。ヒットするのはFreeTDSを使ったものばかりで、多くはPHP5の情報ばかりで、PHP7以降のものは出てこず。。。

で、困ったときはグーグルで検索・・・ではなく、まずマイクロソフトの公式のドキュメントを漁りましょうってことですね(^^;
ググらなくても最近のマイクロソフトはちゃんとドキュメントを用意してくれてます。(MSも変わったな。。。)

Build an app using SQL Server


上記サイトの PHP ⇒ UBUNTU をクリックすると、Linux版 SQL Serverのセットアップから詳細な手順が書かれています。英語ですが、簡単な英文なので読みましょう!英語は喋れなくても、聴き取れなくても一向に問題はないけど、英語の読解能力は必須!ちゃんと読めるようにしましょう!!!
日本人がいくら優れたシステムを作れたとしても、英語で説明できなければ、世界には通用しませんし、この先世界中の開発者がこぞって日本語を理解してくれるとは到底思えないからです。

今までSQLServer用のODBCドライバとPHPでのSQLServer用のドライバ、それぞれが別々に説明がされてて、一体何がどうなってってるのか理解できなかったけど、上記サイトで Ubuntu + PHP では、この手順、Redhat系では、この手順、という風にまとめて手順が説明されているので、これで悩まなくて済むようになった。PHP7以降限定ですけどね。 まぁPHP5系はこの先消えていく(deprecated)のでまぁいいか、という感じ。

要するに ODBCドライバに加えて、peclでsqlsvr/pdo_sqlsvrをインストールすれば完了。やっと 非Windows環境でも労せずSQLServerへフツーにアクセスできるようになりました。

上記マイクロソフトのサイトには、Linux用のSQL Serverのセットアップから説明されていますが・・・SQLServerは Windows用のものをインストールしましょう。というか、開発マシンがWindowsの場合は、素直にWindows用のSQLServer Developper Editionをインストールした方がいいです。開発目的ならライセンスフリーですし。(サーバー環境で運用はできないけど)
開発マシンがMacやLinuxの方は・・・そもそもSQLServerに接続するような仕事してないでしょ?w

WSL(Ubuntu)でPHPをインストールしていない場合、aptで適当にphp7系をインストールしてください。その際、php-dev パッケージは必須です。
また、最新のsqlsrv,pdo-sqlsrvドライバはphp7.0をサポートしないので、最新に拘る人はphp7.1以上にするべし?php7.0をサポートするMicrosoftのsqlsrv/pdoドライバのバージョンは5.3です。最新は確か5.6だったかな?
システム要件:https://docs.microsoft.com/ja-jp/sql/connect/php/system-requirements-for-the-php-sql-driver

$ sudo apt install php php-cli php-dev php-... (必要なパッケージ)
# apacheで動作するには必要
$ sudo apt install libapache2-mod-php

1, 先に マイクロソフトのサイトからODBC Driver for SQLServer をインストールしておく(上述)

2, sqlsrv/pdo_sqlsrv のインストール (ubuntu 1804 の場合)

$ sudo apt install php-dev
$ sudo pecl install sqlsrv
$ sudo pecl install pdo_sqlsrv
$ sudo su 
# echo "extension=pdo_sqlsrv.so" > /etc/php/7.2/mods-available/pdo_sqlsrv.ini
# echo "extension=sqlsrv.so" > /etc/php/7.2/mods-available/sqlsrv.ini
# exit
$ sudo phpenmod -v 7.2 -s ALL  pdo_sqlsrv sqlsrv

apache2な人は再起動するべし。
参考リンク:the installation instructions on Microsoft Docs.

3, PHPで確認
PDOで、DSNのプレフィックスをodbc: ではなく sqlsrv: で。

$pdo = new PDO('sqlsrv:Server=localhost;Database=Sample','dbuser','dbpass');
$pdo->exec('CREATE TABLE tbl_サンプル(hoge_id int NOT NULL,hoge_txt nvarchar(100))');
$pdo->exec(sprintf('INSERT INTO tbl_hoge values(1,N%s)',$pdo->quote('日本語で挿入・・・なんかエロいな'));

参考リンク: https://github.com/Microsoft/msphpsql/tree/master/sample

文字列リテラルをNプレフィックスをつけずにINSERTとすると絵文字が化ける。文字列リテラルの場合は Nプレフィックスを忘れずに。。。
ただ、プリペアドステートメントを利用する場合、自動的にNプレフィックスはつけてくれるみたい。SQLServer Data Profilerで実際実行されたSQL文を確認すると、PDO::execでは自動的にNプレフィックスはつけてくれないみたい。まぁ当たり前といえば、そうなんだけど。。。

あと、東京オリンピックまでには、apt install php-sqsrv / yum install php-sqlsrv とか標準のリポジトリでインストールできるようにしてほしい。。。

\(^o^)/

CentOS から Windows上のSQLServerへの道のり

副題: Road to SQLServer on Windows. (^^;

【追記 2019年3月12日】
CentOS7 と PHP7系については、こちらにまとめてます。
https://ptsv.jp/repository/rhel7-php71-sqlserver/


備忘録です。

今まで、CentOS6からSQLServerへのアクセスは、FreeTDS を使っていました。PHPからはPDO_DBLIB 経由。これは結構簡単にセットアップできました。yum で FreeTDSとか、もっと簡単に、php-sybaseなりphp-mssqlなり入れれば依存関係で必要なライブラリは勝手にインストールされますしね。設定自体も、/etc/freetds.conf と /etc/locales.conf とかいじればあとは、PDOからアクセスできます。

で、非常に疎かったので知らなかったんですが、Microsoftから Linux向けのSQLServer ODBCドライバが供給されている、とのこと。

今更何言ってんの??? って感じですが、知らなかったものはしょうがない。説明通りにやってみます(^^;

# curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit root

$ sudo ACCEPT_EULA=Y yum install msodbcsql
$ sudo ACCEPT_EULA=Y yum install mssql-tools
$ sudo yum install unixODBC-devel
$ sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd
$ sudo ln -sfn /opt/mssql-tools/bin/bcp /usr/bin/bcp

エラーもなく、インストール完了。
ここまでは説明書通り。

さて、ここから設定。

/etc/odbcinst.ini を開くと、以下のセクションが追加されているはず。

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.6.0
UsageCount=1

そして、/etc/odbc.ini を編集します。僕の環境ではodbc.ini は空のファイルでした。
データソースを記述していきます。

[Development]
Driver = ODBC Driver 13 for SQL Server #odbcinst.ini に追加されたセクション名
Description = for development databse #適当
Trace = Yes #わからん。 
Server = 10.1.1.1 #稼働中のSQLServerのIPアドレスもしくはホスト名 ファイヤーウォールでアクセス許可を出すこと。
Port = 1433 #SQLServer Configuration Manager で TCP/IP を有効に。
Database = Sample  #データベース名

この状態で、まず、isql コマンドで接続してみます。書式は、isql [データソース名] [ユーザー名] [パスワード]ですね。

$ isql Development dbuser dbpass
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from hogehoge
+------------+
|            |
+------------+
| 5500       |
+------------+
SQLRowCount returns 0
1 rows fetched
SQL>

無事接続できました!

今度は、マイクロソフトが提供しているツールを使用してみましょう。

$ sqlcmd -S 10.1.1.1  -U dbuser
Password: xxxxxx
1> use Sample
2> go
データベース コンテキストが 'Sample' に変更されました。
1> select count(*) from hogehoge
2> go

-----------
       5500

(1 rows affected)
1>

接続できました。

次に、PHP で PDO_ODBC経由で接続してみます。
DSNには、odbc.ini で設定した、データソース名ではなく、ドライバ名・サーバー名・データベース名をそれぞれ直接指定してみました。

$ php -a
Interactive shell

php > $pdo = new PDO('odbc:Driver={ODBC Driver 13 for SQL Server};Server=10.1.1.1;Database=Sample','dbuser','dbpass');
php > $sth = $pdo->query('select count(*) from hogehoge');
php > echo $sth->fetchColumn();
5500
php >

接続できました。

日本語(UTF-8)が正常にCRUDできるか、まだテストしていませんが、とりあえずは、正常に接続できることを確認できたことでヨシとしましょう。

:continue 続きはこちら

SQLServer on Linux !?

これは、マイクロソフトがWindowsを捨て去る布石なのか??

来年中盤以降に マイクロソフトのサーバーソフトウェア群における中核とも言える SQLServer の Linux 用・・・(たぶん Redhat Enterprise Linux用だと思うんだけど)をリリースするという・・・。
http://blogs.microsoft.com/blog/2016/03/07/announcing-sql-server-on-linux/

SQLServerがLinux上で稼働する、ということは、次に来るのは当然 “.NET Framework on Linux”とか???(Monoプロジェクトがありますけど・・・) もしそうなったら、もうWindows Serverである意味がないやん!

サーバーOSに関して考えればOSを選んでからサーバーウェアを選ぶという時代じゃないですよね。実現したいサービスがあって、それを実現するためのサーバーウェアを選択して、それから最終的にOSを選ぶ・・・っていう。

さらにクラウドコンピューティングとか仮想化とか・・・、正直、OSなんか、もう、どれでもいいんですよね。なんかもうWindowsとかLinuxとかMacOSとかBSDとか、どれでも、いいんすよ。

ま、こんなこと、今更感が満載なんですけどね。
追記:
そういえば、マイクロソフトは Xamarinも買収しましたよね。SQLServer on Linuxも含め、いずれはmonoプロジェクトも取り込んで、ネット上のクライアントとなるアプリ開発、サービス開発に必要なバックエンドソフトウェアと、必要なものがほとんどすべてマイクロソフトからリリースされることになったわけで。