postgresからSQLサーバーのデータを参照したいがAdaptive Server is unavailable or does not existとなる

Question

実現したいこと

postgresからtds_fdwでSQLサーバーのデータを参照したい。

前提

全てイントラ内の話なのですが、CentOS7（192.168.10.1）上のpostgres14から、別の場所にあるSQLサーバーのデータを参照したく、CentOS7側にtds_fdwをインストールし、CREATE EXTENSION tds_fdw;しました。

参照したいSQLサーバーは2台。
①192.168.20.2：インスタンス１つ（MSSQLSERVER）
②192.168.30.3：インスタンス２つ（SQLSVRABCDEFG,SQLSVRHIJKLM）

現在、①には無事postgres（192.168.10.1）から接続出来たのですが、
②については、postgres内に作成した外部表をセレクトしようとすると、以下エラーとなってしまいます。どなたか、解決に向けてこういうこと調べるといいよ、など、アドバイスをいただけましたら幸いです…。

発生している問題・エラーメッセージ

mypgdb=# select count(*) from mssql_fdw_ng1_dbo_顧客種別 ;
ERROR:  DB-Library error: DB #: 20009, DB Msg: Unable to connect: Adaptive Server is unavailable or does not exist (192.168.30.3\SQLSVRHIJKLM), OS #: 0, OS Msg: 成功です, Level: 9

該当のソースコード

Last login: Fri Mar 10 13:40:02 2023 from myWinPC.my-company.local
[root@myCentOS7 ~]# tsql -LH 192.168.20.2
     ServerName DCSVDB01
   InstanceName MSSQLSERVER
    IsClustered No
        Version 12.0.5000.0
            tcp 1433
             np \\DCSVDB01\pipe\sql\query
[root@myCentOS7 ~]# psql -h192.168.10.1 -Upostgres -W mypgdb
ユーザ postgres のパスワード:
psql (9.2.24, サーバー 14.7)
注意： psql バージョン 9.2, サーバーバージョン 14.0.
         psql の機能の中で、動作しないものがあるかもしれません。
"help" でヘルプを表示します.

mypgdb=# CREATE SERVER mssql_fdw_ok1 FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '192.168.20.2', port '1433', database 'oksqlsvdbname');
CREATE SERVER
mypgdb=# CREATE USER MAPPING FOR postgres SERVER mssql_fdw_ok1 OPTIONS (username 'oksqlsvusername', password 'oksqlsvpass');
CREATE USER MAPPING
mypgdb=# grant usage on foreign server mssql_fdw_ok1 to postgres;
GRANT
mypgdb=# CREATE FOREIGN TABLE mssql_fdw_ok1_common_staff (
mypgdb(# "staff_cd" varchar(16) not null,
mypgdb(# "staff_name" varchar(20)
mypgdb(# )
mypgdb-# SERVER mssql_fdw_ok1
mypgdb-# OPTIONS (query 'SELECT staff_cd, staff_name FROM common.staff');
CREATE FOREIGN TABLE
mypgdb=# select count(*) from mssql_fdw_ok1_common_staff ;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
WARNING:  Table definition mismatch: Foreign source has column named staff_cd, but target table does not. Column will be ignored.
WARNING:  Table definition mismatch: Foreign source has column named staff_name, but target table does not. Column will be ignored.
 count
-------
  10
(1 行)

mypgdb=# \q
[root@myCentOS7 ~]# tsql -LH 192.168.30.3
     ServerName HNSDB01
   InstanceName SQLSVRABCDEFG
    IsClustered No
        Version 15.0.2000.5
            tcp 51214

     ServerName HNSDB01
   InstanceName SQLSVRHIJKLM
    IsClustered No
        Version 15.0.2000.5
            tcp 51267
[root@myCentOS7 ~]# psql -h192.168.10.1 -Upostgres -W mypgdb

mypgdb=# CREATE SERVER mssql_fdw_ng1 FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '192.168.30.3\SQLSVRHIJKLM', port '51267', database 'ngsqlsvdbname');
CREATE SERVER
mypgdb=# CREATE USER MAPPING FOR postgres SERVER mssql_fdw_ng1 OPTIONS (username 'ngsqlsvusername', password 'ngsqlsvpass');
CREATE USER MAPPING
mypgdb=# grant usage on foreign server mssql_fdw_ng1 to postgres;
GRANT
mypgdb=# CREATE FOREIGN TABLE mssql_fdw_ng1_dbo_顧客種別 (
mypgdb(# "顧客種別ＩＤ" int,
mypgdb(# "顧客種別" varchar(50)
mypgdb(# )
mypgdb-# SERVER mssql_fdw_ng1
mypgdb-# OPTIONS (query 'SELECT "顧客種別ＩＤ", "顧客種別" FROM dbo.顧客種別');
CREATE FOREIGN TABLE
mypgdb=# select count(*) from mssql_fdw_ng1_dbo_顧客種別 ;
ERROR:  DB-Library error: DB #: 20009, DB Msg: Unable to connect: Adaptive Server is unavailable or does not exist (192.168.30.3\SQLSVRHIJKLM), OS #: 0, OS Msg: 成功です, Level: 9
mypgdb=# \q
[root@myCentOS7 ~]# tsql -H '192.168.30.3\SQLSVRHIJKLM' -p 51267 -U ngsqlsvusername -P ngsqlsvpass
locale is "ja_JP.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> USE ngsqlsvdbname
2> select count(*) from 顧客種別
3> go

10
(1 row affected)
1>
2> quit
[root@myCentOS7 ~]# sqlcmd -S '192.168.30.3\SQLSVRHIJKLM' -U ngsqlsvusername -P ngsqlsvpass
1> use [ngsqlsvdbname]
2> select count(*) from dbo.顧客種別
3> go
データベース コンテキストが 'ngsqlsvdbname' に変更されました。

-----------
          10

(1 rows affected)
1>
2> quit
[root@myCentOS7 ~]#

試したこと

• fdwでなくdb_linkは？と調べたが、postgres同士でないとだめみたいだった。

• エラーメッセージで検索し、HITしたページを読んでいきました。特に一番上に出てきた こちら は入念に読み、内容を確認しました。

• 接続出来たサーバーのポートは標準の1433で、出来ない方は51267という違いがあることを疑った。CentOSからSQLサーバーに以下が通るので、51267で問題ないと考えた。

tsql -H '192.168.30.3\SQLSVRHIJKLM' -p 51267 -U ngsqlsvusername -P ngsqlsvpass

• 念の為、接続出来なかったSQLサーバーのWindowsDefenderファイアウォールに、送受信の規則[TCP51267をパブリック除いて許可]を追加したがだめ。

• 192.168.30.3\SQLSVRABCDEFG（もう一個のインスタンス）でも試したが、結果は同じだった。

• 同じCentOS7上のapache上のphpから、pdoでは、'192.168.30.3\SQLSVRHIJKLM'に普通に接続出来ている。

Answer 1

SQLSVRHIJKLMインスタンスのポートを1433に変更

1. 接続したいHNSDB01にrdpログイン
2. SQLサーバー構成マネージャにてSQLサーバーネットワークの構成→SQLSVRHIJKLMのプロトコル
3. TCP/IP右クリック→プロパティ→プロトコルはすべて受信待ち「はい」なので→IPアドレス→IPALL→TCP動的ポート「51267」削除し、TCPポートに「1433」入力して「適用」
4. SQLサーバーのサービス→SQLサーバー「SQLSVRHIJKLM」右クリック→再起動

外部サーバー構成定義を変更

• 前：CREATE SERVER mssql_fdw_ng1 FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '192.168.30.3 \SQLSVRHIJKLM ', port ' 51267', database 'ngsqlsvdbname');
• 後：CREATE SERVER mssql_fdw_okninatta FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '192.168.30.3', port ' 1433', database 'ngsqlsvdbname');

→これでアクセス出来ました。

HNSDB01のもう一つの方のインスタンスに恒常的に同時にアクセスする方法は以前不明だが、同時にアクセス出来なくても、かわりばんこに切り替えながら使えれば当面は大丈夫なので、一旦終了…。'IPアドレス\インスタンス名'が鬼門だったのか、1433以外がサポートされていないのか、なんだったのか…。なお、外部表定義時、SQLサーバー側のセレクトは、ダブコでなく[] にしないとだめだったので、そこは後で修正しました。ここ、セレクト文でなくtable　みたいな文法も使えるみたいなので、それ出来るならその方が場合によっては簡潔でいいかも。