none
Troubleshooting Connectivity #4 - 接続エラーの調査方法 (前編) RRS feed

  • 質問

  • 高橋 理香
    SQL Developer Support Escalation Engineer

    こんにちは。

    SQL Server への接続シリーズも今回で 4 回目となりました。だいぶ時間空いてますけど、その点はご容赦ください。。。

    それでも、少しでも、読んでくださっている皆さんのお役に立っているといいなと思う今日この頃です。

    Troubleshooting Connectivity #1 - SQL Server への接続
    Troubleshooting Connectivity #2 - エラー情報からわかる失敗原因
    Troubleshooting Connectivity #3 - 予期しない接続切断

    さて今回は、これまでの Troubleshooting ではなんとかならなかった、もしくは、きちんと裏を取ってから対処を行いたい、という場合にどんな情報を採取したらよいかについてご紹介したいと思います。また、実際のログなどを例に調査ポイントを説明します。

    1. SQL Server への接続失敗についてまず確認すること


    基本的には一般的なトラブルシューティングの場合と同様に 5W1H で考えてみましょう。

    ログ採取は時には負荷を伴う場合もありますので、そういった場合には、これらの情報を頼りに、採取すべき情報の最小化を検討することになります。

    WHAT (発生している現象) :

      • どのようなエラーが発生するか。もしくは、何を見て接続の問題と判断したか。

      WHERE (現象発生環境の範囲) :

      • ツールやアプリケーションをどこで実行しているか。(SQL Server が動作しているサーバー上か、それとも、リモートのクライアントか。)
      • クライアントも複数存在する場合にはそれら全部かそれとも一部のみか。
      • SQL Server のインスタンスやサーバーが複数ある場合にはそれらのすべてに対してかそれとも一部のみか。

      WHEN (再現性) :

      • 問題のツールやアプリケーションを使用する場合には常にエラーとなるか。それとも時々発生するか。
      • 使い始めた以降で成功していたことはあるか。今まで問題なかったとしたら、いつから問題が出るようになったか。

      WHO (現象発生ユーザー状況) :

      • 接続しようとしているユーザーによる違いはあるか。(接続試行するアプリケーションを実行しているユーザーや、SQL Server への認証に使用するユーザー)

      HOW (具体的な操作内容) :

      • どのような処理を行った場合に接続エラーが発生するか。(エラーが発生したツール、アプリケーションにおける操作内容等。また、一方で、接続が成功するツールやアプリケーションはあるか。SQL Server 付属の Management Studio、Visual Studio で開発した System.Data.SqlClient を使用するアプリケーション、など)
      • 開発アプリケーションの場合には、アプリケーションのどのような処理でエラーになるか。(SqlConnection.Open の処理でエラーとなる、など)

      また、上記に加えて、補足となるような情報もあるはずですので、その内容も押さえます。

      EXTEND (補足情報) :

      • エラー発生前に実施した作業内容
      • エラー発生後に試した作業内容

      例えば上記に沿って接続失敗の情報を収集した例は以下の通りです。

      ※例1

      発生している現象
      以下のエラーが発生して SQL Server に接続できない。
      ----------
      System.Data.SqlClient.SqlException: SQL Server への接続を確立しているときにネットワーク関連またはインスタンス固有のエラーが発生しました。サーバーが見つからないかアクセスできません。インスタンス名が正しいこと、および SQL Server がリモート接続を許可するように構成されていることを確認してください。 (provider: 名前付きパイプ プロバイダ, error: 40 - SQL Server への接続を開けませんでした)
      ----------
      このエラーはデバッグ実行で画面上に表示された。

      現象発生環境の範囲
      新規に用意した Windows 7 のマシンにアプリケーションを配置して実行した。
      SQL Server は 2008 R2 で別の Windows Server 2008 R2 上で動作している。
      以前に導入済みの他のクライアントについては正常動作している。
      SQL Server はこの 1 つの既定インスタンスしかインストールしていない。

      再現性
      新規のマシンでは一度も成功したことはない。

      現象発生ユーザー状況
      ユーザーによる違いはない。

      具体的な操作内容
      Visual Studio 2008 C# で開発した Windows フォーム アプリケーション。
      SQL Server へのアクセスには System.Data.SqlClient を使用している。
      SqlConnection.Open の個所でエラーとなる。


      補足情報
      SQL Server 構成マネージャーから、共有メモリプロトコルと TCP/IP の待ち受けが有効となっていることは確認済み。
      また、TCP/IP のポート番号は 1500 と表示されていた。
      Windows ファイアウォールは有効だが、 TCP ポート 1500 を例外として許可するよう設定されている。
      SQL Server Browser サービスは開始している。
      クライアントからサーバーへの telnet での接続も可能であることは確認した。

      実は上記の情報があれば、すでに原因と対処はお伝えできるレベルなのですが、�����降では、これを情報採取しながら調査することを考えてみましょう。

      2. SQL Server への接続失敗についての調査で参考となる情報にはどのようなものがあるか


      2-1. 確実にいただきたい情報

      接続エラーについての調査では、様々なログやトレースを使用することができます。

      どのようなパターンにおいてもまずご用意いただきたいのが、以下の情報です。

      • アプリケーション情報
        • エラーとなったツールの名前、アプリケーション開発で使用したテクノロジーがわかるようなソース ファイル等
        • 指定した接続先サーバー名、ユーザー名等の接続情報 (接続文字列) を含むスクリーンショットやファイル
      • 発生したエラー情報 (エラー番号、メッセージ、スタックトレース等) を含むスクリーンショットやログ ファイル
      • SQL Server エラーログ
        [SQL Troubleshooting] 第1回 : Tips - SQL Server エラーログとイベント ログを採取する (SQL 2000 ~ 2008 R2)

      特に、先のブログや先の WHAT でも記載しましたが、何はなくともエラー情報の確認 が必要です。時々、「エラーは表示されたけど忘れた」といったことを伺うこともありますが、本当に、本当に、なんとかして取得をお願いします。画面のスクリーンショットを取ってメールに貼り付けるなどの方法でも十分です。

      2-2. 再現性がある場合に、切り分けのために実施いただきたい接続テスト

      引き続き接続が失敗している状況 (再現性のある状況) では、以下のような検証を行った結果も問題個所を特定するための切り分け情報として参考となります。

      • ping 実行結果
        • コマンドは以下の3つ。
          ping 接続先IPアドレス
          ping 接続先ホスト名
          ping -a 接続先IP アドレス
      • telnet 実行結果
        • コマンドは以下の2つ。
          telnet 接続先IPアドレス 待ち受けポート番号
          telnet 接続先ホスト名 待ち受けポート番号
      • いずれかの付属ツールを使用した Windows 認証/SQL Server 認証での接続テスト結果
        • sqlcmd ユーティリティでの接続テスト結果
          SQL Server への接続、クエリの実行等で使用されるコマンド ライン ユーティリティです。SQL Server や付属のツールがインストールされている環境で使用可能です。このユーティリティを使用して、プロトコルを明示的に指定した接続、認証方法を変更しての接続のテスト等が可能です。ツールの詳細については、
          sqlcmd ユーティリティ のページをご覧ください。

          コマンドプロンプトからの実行例は以下の通りです。

          - プロトコル指定なし、Windows 認証で接続する

          sqlcmd –S 接続先SQLServer名 –E –m-1

          - プロトコル指定なし、SQL Server 認証で接続する

          sqlcmd –S 接続先SQLServer名 –U ログイン名 -P パスワード –m-1

          - TCP/IP、ポート 2500 に対して Windows 認証で接続する

          sqlcmd –S tcp:接続先SQLServer名,2500 –E –m-1

          - 名前付きパイプ、SQL Server 認証で接続する

          sqlcmd –S np:接続先SQLServer名 –U ログイン名 -P パスワード –m-1

        • ODBC アドミニストレーターでの接続テスト結果
          ODBC アドミニストレーターを使って、sqlcmd ユーティリティの場合と同様に、接続先 SQL Server 名にプロトコルやポート番号を指定した場合や、認証方法を変更した場合に接続が可能かのテストが可能です。
          以下は ODBC アドミニストレーターでの接続手順です。(ODBC ドライバとして SQL Server Native Client 10.0 を使用した場合の例です。)

          1) odbcad32.exe を起動します。(WOW64 の場合には、%Windir%\SysWOW64\odbcad32.exe)
          2) [追加] ボタンをクリックすると、ODBC ドライバ選択の画面が表示されるので、使用したい ODBC ドライバを選択して [完了] します。(図では SQL Server Native Client 10.0 を選択しています。)
          image

          3) [名前] の欄には任意の名前を指定し、[サーバー] には接続先 SQL Server 名を指定して、[次へ] をクリックします。
          image

          4) 認証方法を選択して、[次へ] をクリックします。"SQL Server に接続して追加の構成オプションの既定設定を取得する" のチェックが入っている場合、ここで SQL Server へ接続します。
          image

          接続が成功すると、次のような画面が表示されます。失敗すると、エラーメッセージが表示されます。
          image

        • UDL ファイルを使用した接続テスト結果
          UDL ファイル (データ リンク ファイル) を使用すると、OLE DB Provider を使って、sqlcmd ユーティリティの場合と同様に、接続先 SQL Server 名にプロトコルやポート番号を指定した場合や、認証方法を変更した場合に接続が可能かのテストが可能です。
          以下は UDL ファイルによる接続テスト手順です。(OLE DB Provider として SQL Server Native Client 10.0 を使用した場合の例です。)

          1) デスクトップ上の空いているところで右クリックし、[新規作成] - [テキスト ドキュメント] を選択します。
          image

          2) ファイル名を拡張子 .udl のついた任意の名前に変更し、右クリックで [プロパティ] を選択します。
          image

          3) [プロバイダー] ������で接続に使用する OLE DB プロバイダーを選択し、[接続] タブを表示します。
          image image

          4) サーバー名に接続先 SQL Server 名を入力し、ログオンのための情報を選択、入力した後、データベースの選択はせずに [接続テスト] をクリックします。成功すると、次のメッセージが表示されます。失敗すると、エラーメッセージが表示されます。
          image

      2-3. 接続エラーについて調査する際に採取できるトレースやログ

      上記以外では、以下のような情報が調査する上で参考となります。

      • 接続関連のレジストリ情報

      プロトコルの優先順位、別名の設定などを確認するのに役立ちます。
      この情報は、エラー発生環境と、正常に接続可能な環境があればその両方で採取します。

        • 参照レジストリは以下の通りです。
          32 bit 環境
          [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer]
          64bit 環境
          [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer]
          および
          [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer]
      • 接続元と接続先の両端で採取したネットワーク トレース

      クライアントから送信した接続要求が SQL Serer まで到達しているかどうかなど、通信経緯を把握するのに役立ちます。
      採取方法については、他チームですが、ブログにて紹介しているのでそちらをご覧ください。

      ご存じですか、Microsoft Network Monitor はコマンドラインでも操作できます
      • 接続元で採取した BID トレース

      データアクセス コンポーネント (ADO.NET、ODBC、OLE DB 等) のデバッグログを取得するためトレース機能です。ネットワークトレースでは把握できないアプリケーション側での処理経緯を把握するのに役立ちます。採取方法等については、以下のブログにて紹介しているのでそちらをご覧ください。

      HowTo: BID トレース - データアクセス アプリケーションのトレースを採取する
      • 接続元で採取した Process Monitor トレース

      ファイルやレジストリへのアクセスをトレースするためのツールです。これにより、レジストリに格納されている情報をどのように読み取っているかなどを把握することができます。ツールの入手、ならびに、使用方法等については Process Monitor のページでご覧ください。

      • 接続先で採取したパフォーマンス ログ

      接続の問題は、接続元、ネットワーク、接続先等、通信経路上の各所における負荷に起因して発生することが多いので、パフォーマンス監視も有効です。これは平常時に採取しておき、エラー時との比較をすることに意味があります。

      [SQL Troubleshooting] 第2回 : Tips -パフォーマンス ログの採取方法 (Windows Server 2003 ~ Windows Server 2008 R2)
      • SQL Server で採取したサーバー トレース

      SQL Server へのクエリ実行経緯を把握する必要がある場合に役立ちます。

      SQL トレーススクリプトの作成、実行 (SQL Server 2005, 2008, 2008 R2)
      • イベントログ (サーバー側のシステム、セキュリティ、アプリケーション)

      OS やサービスの再起動の状況、ハードウェアに関連したエラー等、対象マシン全体の状態を把握するために確認します。

      [SQL Troubleshooting] 第1回 : Tips - SQL Server エラーログとイベント ログを採取する (SQL 2000 ~ 2008 R2)

      • システム情報

      サービスの状態、起動アカウント、NIC のドライバー等の情報を確認します。
      採取のためのコマンドは以下の通りです。出力には少々時間がかかります。

      Windows Server 2003/Windows XP の場合:

        winmsd /report <システム情報出力パス>

      Windows Vista、Windows 7、Windows Server 2008、Windows Server 2008 R2 の場合:

        msinfo32 /report <システム情報出力ファイルパス>

      • 接続元でのポート利用状況確認 (netstat) 結果

      TCP ポート枯渇による接続失敗等の可能性がないかを確認するのに役立ちます。コマンドは以下の通りです。

      netstat –ano

      20秒間隔で実行した結果をファイルに書き出す場合には以下の通り。

      netstat –ano 20 > c:\temp\netstat.log

    2019年4月8日 4:08
    所有者