windows11にインストールされたSQLServerにODBC接続できない

Question

前提

お客様が受注管理でAccessを使用しており、かなり古いバージョンのAccessだったため、
Access2016　＋　SQLserverExpress 2019
の組み合わせに変更しようということになりました。オリジナルのシステムは別の会社が作っており、その会社からのサポートはもうないとのことです。

やったこと
サーバもどきマシンにて
1.windows11マシンに SQL serverExpress2019、SSMS をインストール（混合モード）
2.当方のPCでmigrationしたデータベースをリストア
3.当該マシンで、Accessのアプリケーションが動作することを確認
4.ネットワーク構成マネージャーで、TCP/IPを有効にした
クライアントPCで
5.ODBC for SQL server をインストール
6.ODBC接続を行おうと、ユーザーDSNから、追加を選び
ODBC Driver 18 for SQL server を選択して設定に移る
7.データソース名を入力して、サーバー(s)を選ぶ時に時間がかかるが、正しいインスタンスが表示されるので、それを選択
8.ログインIDとパスワードを入力して、次の画面に移動する
9.規定のデータベースを以下に変更するにチェックを入れて、データベース名が表示されるのを待つが、しばらく経ってエラーがかえってきます。

なお、firewall は切っています。

実現したいこと

サーバもどきマシンと同様に後3台のPC（一応、クライアントマシン）でもACCESSアプリケーションを動かしたい。そのためにODBC接続を確立させたい。

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

接続できませんでした：
SQLState:'08001'
[Microsoft][ODBC Driver 18 for SQL Server]MAX_PROVS:指定されたServer/Instanceのいちを特定するときにエラーが発生しました
[xFFFFFFFF];
接続できませんでいた:
SQLState: 'HYT00'
SQL Server エラー:0
[Microsoft][ODBC Driver 18 for SQL Server][ログイン タイムアウトが時間切れになりました
接続できませんでした
SQLState '08001'
SQL Server エラー:-1
[Microsoft][ODBC 18 for SQL server][SQL Server への接続の確立中に、ネットワーク関連のエラーまたはインスタンス固有のエラーが発生しました。サーバーが見つからないか、アクセスできません。インスタンス名が正しいことと、SQ Serverがリモート接続を許可するように構成されていることを確認してく浅井。詳細については SQL Server オンライン ブックを参照して下さい。

該当のソースコード

試したこと

ODBC18 for SQL Server がダメ？なのかとおもって、普通の(?)のODBCで試してみたのですが、同じ症状です。

補足情報（FW/ツールのバージョンなど）

サーバもどきマシンの firewall は切っています。クライアントマシンのほうはそのままにしています。
テスト環境はwindows10で、問題なく接続できています。
それぞれのマシンから、pingを打つと正しく返ってきます。

エラーメッセージを見る限り、ネットワーク上で見えていないのか？と思います。ただ、インスタンス名の一覧取得は、時間がかかるのですが、取れています。
サーバもどきマシンのフォルダに共有を掛けてみて、クライアントの方でネットワークコンピュータを開くと、サーバもどきマシンは見えるのですが、自分自身は見えません。
サーバもどきマシンでネットワークコンピュータを開くと自分自身は見えますが、他のPCは見えません。これが全てのPCが見えるようになれば問題ないとは思いますが、その方法がわかっていません。

なにかヒントを頂ければと思います。

Comments

[KOZ6.0]

ODBC アドミニストレータは 32bit と 64bit のものがあります。
Access 2016 のビット数に合わせたものを使っていますか？

[退会済みユーザー]

なんの話ですか？ Access VBA?

[matukeso]

クライアントODBCアドミニストレータでエラーが出るってことですよね。
クライアントで入れた「正しいインスタンス」は具体的に何？
サーバ側でネットワーク構成マネージャを変更してからSQLサーバを再起動した？
サーバー側でSQL Server Broserサービスが開始していないとか。

[maqua0319_2022]

「正しいインスタンス」というのはコンボボックスで表示されたインスタンスで、その名前はこちらの意図したものとなっています。
ネットワーク講師マネージャーは変更のたびにサービスから再起動をしています。
サービスを見る限り、Broserサービスは開始しています。

[maqua0319_2022]

SurferOnWwwさん、Access直接ではなくて、ODBCがつながらないという話です。

[maqua0319_2022]

KOZ6.0さん
Access2016からの接続する前の話です。クライアント側にODBC for SQL server 18をインストールして、ODBC接続を追加しようとした時に接続できないという状況です。

[退会済みユーザー]

> Access直接ではなくて、ODBCがつながらないという話です。

何かのアプリケーションソフトから SQL Server に接続しようとしてそのエラーになったと想像していて、そのアプリは何ですかという質問だったのですが。的外れな質問だったらすみません。

こういう話だったのですかね。

Access を SQL Server に接続する
https://support.microsoft.com/ja-jp/office/access-%E3%82%92-sql-server-%E3%81%AB%E6%8E%A5%E7%B6%9A%E3%81%99%E3%82%8B-050d88f3-b2d6-4e76-b6f9-f3c556f139ea

[matukeso]

インスタンス名を、「相手ip, port」で指定したら？ポートはサーバでnetstatとかしてSqlserver.exeの待ってる番号をしらべてもらうとして。

[maqua0319_2022]

ご指示の通りにインスタンス名のところに192,168.XX.XX,1433 と入力したところ、待ち時間なしに次のウィンドウに移りましたが、やはり既定のデータベースを指定するところでコンボボックスの矢印をクリックすると同じ症状です。

う～～ん、サーバもどきのほうでなにかあるのかなぁ？？？

[matukeso]

sqlexpressはデフォルトインストールしたなら待ち受けポートは1433ではないですが、規定のインスタンスとしてインストールしたんですか？

[maqua0319_2022]

ポートを指定してみました。
1.構成マネージャーで IPALL のポートを1433 に指定
2.browser を停止
3.serviceで SQLserverを再起動
4.クライアント側でODBC接続を試みるも失敗
5.クライアント側にheidiSQLをインストールし、
　TCP/IP
　サーバ名を名前から IPアドレスに（192.168.XX.XX\インスタンス名)
で接続ができました。
6.ODBCアドミニストレータで
　・データソースのドライバを SQL Server を指定すると接続OK
　・データベースのドライバを ODBC Driver 18 for SQL server を指定するとエラー
ということが判明しました。

ODBC driver がまだダメです。なぜ ODBC Driver 18 for SQL server を使うかというと、通常のドライバー SQL server は日付時刻型が文字列に変換されてしまうのです。
従来のプログラムを極力修正しないという前提があるので、ODBC Driver for SQL Server を使わざるをえない状況でして。

以上　状況報告です。

[maqua0319_2022]

追加です。
ODBC Driver 17 for SQL Server, ODBC Driver 18 for SQL Server ともにダメでした。

[matukeso]

暗号化系統のチェックボックスを外してを無効にする必要があったと思うけど

[maqua0319_2022]

暗号化ですか。
知識不足で申し訳ないのですが、構成マネージャーで「強制的に暗号化」の値でしょうか！？
ここはいいえになっていました。
ちなみに「はい」に変えてから接続を試してみたら、証明書云々というエラーメッセージが出てしましました。

他に指定するところが思い浮かびません。

Answer 1

え～～、いろいろ触ってなんとなく解決できた？？というか、エラーは発生しない状況になりました。

やったこと
通常のODBC for SQL server で接続をつくるのですが、クライアントの設定画面を出して、
「ポートを動的に決定する」のチェックを外し、サーバ側で設定した1433を指定します。
これで設定を作っておいて、
ODBC Driver for SQL Server で接続を追加します。ここでインスタンス名(名称ではなく、ipアドレスを指定）とデータベース名は自動取得ではなく、直接入力します。それと「サーバ証明書を信頼する」にチェックを入れます。

これで完了して、接続テストを行うと・・・ノートラブルで完了しました。
この後、通常 SQL Serverの接続を削除しても、今のところ問題は発生していません。

想像するに、ODBC driver for SQL Server ではポート設定を指定するところがないのですが、通常のSQL Server の接続でポート指定をすることで、それが引き継がれるのではないか？と思われます。
なんかすっきりしないのですが、

・名前付きインスタンスの場合
１）サーバ側で動的ポートではなく、ポートを指定すること
２）クライアント側で一旦通常の SQL Server でのODBC接続を作成する。その際、ポートを正しく指定する（動的にしない）
３）ODBC driver for SQL Server で接続を作る。その際、インスタンス名とデータベース名は選択せず直接入力する
４）サーバの証明書を信頼するにチェックを入れておく

これで、大丈夫そうです。また別のパソコンを接続するときがあるので、もう一度検証できればと思います。
ご協力頂いた皆様、ありがとうございました。

Comments

[maqua0319_2022]

インストールされていた Access 2019が 32bit だったので、odbc32で再度作成し直しました。
お金を掛けずにというクライアントの要望があり、ODBCリンクファイルはdbo_という接頭辞がつくのでクエリを全部書き換えるのが大変なので、dbo_ホニャララのテーブルをホニャララというクエリを被せる形で対応したら、めちゃくちゃ速度が低下してました。

ともあれ、インスタンス名とポート指定、これでなんとか対応できましたということです。