Troubleshooting Connectivity #1 - SQL Server への接続

 

高橋 理香
SQL Developer Support Escalation Engineer

 

SQL Server にアクセスするアプリケーションを開発、運用していて発生する接続エラー。エラーのメッセージは様々あるものの、少々古いテクノロジーを使用していると、メッセージからは何が起こったのかがわかりにくいケースが多々あります。エラー発生時のトラブルシューティングについて、SQL Server への接続の仕組みやエラーの意味、エラーが発生した場合の対処などについて、何回かのシリーズでご紹介しようと思います。

まず 1 回目の今回は、接続時の基本的な処理内容と、事前のチェック事項についてご紹介します。

 

SQL Server への接続で行われることは?

SQL Server 付属のツールや .NET で開発したアプリケーションなどから SQL Server に接続する場合、主に次の処理が行われます。

 

  1. OS レベルのセッション確立
  2. ログイン認証
  3. データベースアクセス

 

どのステップがうまくいかなくても接続は失敗します。また、どのステップで失敗したかによって原因が異なる、つまり、対処のために確認すべきポイントが異なるため、それぞれ具体的にどのような処理を行っているのかを把握しておくことは重要です。以降では、それぞれの処理について説明します。

 

OS レベルのセッション確立

SQL Server では各種プロトコルによる接続待ち受けを行っており、クライアントからの接続時にはこれらの待ち受け先にアクセスし、OS レベルのセッションを確立します。

既定インスタンスに接続する場合はとてもシンプルです。既定インスタンスの場合、TCP/IP による接続待ち受けのための TCP ポートは、Well-Known ポートとしても登録されている 1433 のため、クライアントからは 1433 に対して Winsock の関数を使用してセッション確立を行います。名前付きパイプの場合は、SQL Server の待ち受けのためのパイプ名は \\.\pipe\sql\query で、クライアントでは \\servername\pipe\sql\query に対して Win32 API 関数を使用してアクセスします。

名前付きインスタンスの場合、SQL Server インストール後の TCP/IP の待ち受けは動的ポートの設定になっています。つまり、クライアントでは決め打ちで特定の TCP ポートに接続要求を出せません。そのため、クライアントでは SQL Server が待ち受けに使用しているポート番号を知るための処理が必要となり、サーバーではその処理に応答するための機能が必要となります。これを実現しているのが、SQL Server Browser サービスです。そのため、名前付きインスタンスへの接続では、クライアントからはまず最初に SQL Server Browser サービスにアクセスします。このとき使用されるのは UDP ポート 1434 です。SQL Server Browser サービスは、クライアントからの要求に対して、指定の SQL Server インスタンスが待ち受けに使用しているプロトコルやポート番号等の情報を返します。クライアントでは、ここで初めて、受け取った情報を使用して SQL Server の待ち受けポートへのセッション確立を行います。

なお、結構重要なことなのでここで記載しますが、SQL Server Browser サービスへの問い合わせの際には、クライアントにおいて順引き (コンピュータ名から IP Address)、逆引き (IP Address からコンピュータ名) の両方の名前解決を行っています。

 

ログイン認証

OS レベルのセッションを確立したら、次に SQL Server にログインする権限を持っているかどうかを確認するための認証処理が行われます。

Windows 認証の場合には、クライアントから渡されたユーザーの資格情報の確認が行われます。つまり、ドメイン環境においてドメインユーザーで SQL Server にアクセスする場合は、ドメイン コントローラー (Active Directory) への問い合わせもこのタイミングで行われます。

SQL Server 認証の場合には、指定のログインが SQL Server にログインとして登録されているかのチェックを行います。もしも SQL Server 稼動サーバーがドメインに参加しており、パスワード ポリシーを有効化している場合には、Windows 認証の場合と同様に、ドメイン コントローラーへの問い合わせが発生します。

 

データベースアクセス

SQL Server にログインした後には、ログインの既定のデータベース、もしくは、接続文字列等で指定されたデータベースにアクセスします。

 

SQL Server へ接続するために必要となる準備は?

さて、SQL Server への接続が完了するまでのステップがわかりました。
では、そのステップを正常に進め、接続を確立するために事前に何を準備する必要があるのでしょう?

主な事前チェック項目は以下の通りです。 

  • SQL Server サービスが起動しているか。
  • 名前付きインスタンスへの接続の場合、SQL Server Browser サービスは起動しているか。
  • SQL Server が指定のプロトコルで待ち受けを行っているか。
  • Windows ファイアウォール等、接続元と接続先の間で TCP ポートをブロックするような設定が行われていないか。
  • SQL Server にログインを作成し、接続に必要となる権限を付与しているか。
  • ドメイン環境で Windows 認証で接続する場合、Kerberos 認証のために SQL Server 用の Service Principal Name (SPN) は登録されているか。
  • 名前付きインスタンスへの接続の場合、接続元マシンで SQL Server 稼動サーバーの名前を順引き、および、逆引きで解決できるか。
  • アクセスしようとしているデータベースが存在し、正常に開ける状態か。

それぞれについてのチェック方法を簡単にご紹介します。

サービスの起動チェック (SQL Serfver, SQL Server Browser)

SQL Server の付属ツールである "SQL Server 構成マネージャー" (SQL Server Configuration Manager) でサービスの稼動状態を確認できます。接続先の SQL Server が停止状態となっていたら起動しましょう。

以下の図では、SQL Server の名前付きインスタンス "SQLEXPRESSR2" と SQL Server Browser サービスは実行中ですが、別の名前付きインスタンス "SQLEXPRESS" は停止状態です。そのため、SQLEXPRESS インスタンスへの接続は失敗することになります。

image

 

待ち受けプロトコルのチェック

SQL Server の付属ツールである "SQL Server 構成マネージャー" (SQL Server Configuration Manager) で待ち受けプロトコルの状態を確認できます。接続に使用したいプロトコルが有効になっていなかったら、状態を "有効" に変更しましょう。(※サービスの再起動が必要です)

以下の図では、SQL Server の名前付きインスタンス "SQLEXPRESSR2" は TCP/IP のみを有効にしていることがわかります。つまり、名前付きパイプなど他のプロトコルによる接続は失敗します。

image

また、そのプロパティを見てみると、動的ポートを使用しており、現在割り当てられている TCP ポートは 64236 であることがわかります。SQL Server Browser サービスはクライアントからの接続要求に対してこの 64236 という番号を返し、クライアントではこの番号を使用して OS セッションを確立します。

image

 

Windows ファイアウォールのチェック

Windows ファイアウォールを有効にしている場合には、クライアントからの SQL Server や SQL Server Browser への通信がブロックされないよう、それぞれのプログラム、もしくは、それぞれが使用するポートを登録する必要があります。Windows ファイアウォールの設定は、コントロール パネルの [Windows ファイアウォール]、もしくは、管理ツールの [セキュリティが強化された Windows ファイアウォール] で確認、変更できます。

以下の図は Windows 7 の Windows ファイアウォールの設定です。SQL Server と SQL Server Browser のプログラムに関して Windows ファイアウォールを介した通信を許可するよう設定しています。

image image

 

ログイン登録状態のチェック

SQL Server にログインするためには、SQL Server 認証用のログインが登録されているか、もしくは、Windows 認証用のユーザーアカウントもしくはグループが SQL Server にログインとして登録されている必要があります。アクセスに使用するログインが登録されているかどうかについては、Management Studio で確認できます。

以下の図は、Management Studio 上のオブジェクト エクスプローラーを使用してログイン一覧と、そのうちの SQLUser01 のプロパティを表示したものです。SQLUser01 は SQL Server 認証のログインであることがわかります。

image image

 

データベースの状態のチェック

SQL Server にログインできても、データベースが使用可能な状態でなければ接続処理はエラーで失敗します。データベースの状態も Management Studio で確認できます。

以下の図は、Management Studio 上のオブジェクト エクスプローラーを使用してデータベース一覧を表示しています。DBMTtest というデータベースはデータベース ミラーリングを構成するデータベースで、現在は復旧中の状態ですのでアクセスできません。また、xactdb というデータベースはオフラインですので、こちらも接続できませんね。他のデータベースを指定したら接続できるけれども、特定のデータベースを指定すると接続できない場合には、こちらをチェックして、データベース管理者に相談しましょう。(データベース管理者の方は、データベースの復旧作業等を実施ください。)

image

 

名前解決可否のチェック

名前付きインスタンスに接続する場合、まずは SQL Server Browser サービスへ問い合わせを行いますが、この時、接続元環境において、接続先サーバー名の名前解決を行います。もしも接続先として IP アドレスを指定している場合には、その IP アドレスに対応するホスト名を取得しようとしますので、もし、Hosts を使用しておらず、DNS で逆引きができず、NetBIOS 名も得られないとなると、SQL Server Browser サービスに問い合わせができません。

名前解決に問題がないかについては、まずは以下をチェックしましょう。

 

SPN 登録状態のチェック

SQL Server はサービス起動時にそのサービス起動アカウントによって Active Directory に SPN を登録しようとします。もしも登録の権限がない場合には、SPN は登録されません。この動作の説明や、SPN の形式などについては、以下に説明があります。

サービス プリンシパル名の登録

起動時に SPN を登録できたかどうかは、SQL Server のエラーログで確認できます。以下は、SPN 登録に成功した例です。

2011-11-28 16:56:16.02 サーバー The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/Server01.domain01.com:SQLEXPRESSR2 ] for the SQL Server service. 2011-11-28 16:56:16.02 サーバー The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/Server01.domain01.com:64236 ] for the SQL Server service.

以下は、SPN 登録に失敗した例です。

2011-11-28 19:36:23.17 サーバー The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

起動時に SPN 登録に失敗した場合でも、登録する権限のあるユーザーによって手動で SPN を登録することができます。SPN の登録状態を確認するには、Setspn.exe ツールを使用します。以下は、特定のコンピュータアカウントで登録されている SPN の一覧を取得した例です。SQL Server の SPN としては 2 つの登録があります。

C:\Windows\system32>setspn –L server01 次の項目に登録されている CN=SERVER01,OU=Workstations,OU=Machines,DC=domain01,DC=com: MSSQLSvc/SERVER01.domain01.com:64236 MSSQLSvc/SERVER01.domain01.com:SQLEXPRESSR2 TERMSRV/SERVER01.domain01.com TERMSRV/SERVER01 RestrictedKrbHost/SERVER01 HOST/SERVER01 RestrictedKrbHost/SERVER01.domain01.com HOST/SERVER01.domain01.com

 

今回はまずはここまでとします。少なくとも上記を確認し、問題があった場合にそれを改善できれば、何かしらの方法で接続することはできるようになります。

次回は、実際のエラーメッセージを例に、何が起きているのかを判断する方法について説明できればと思っています。