SQL Server 2017に換えたら、ログインできなくなった(備忘録)

弊社のサーバが古くなり、ISO的にも問題があるので新しいサーバに交換しました。で、開発用のデータベースサーバもSQL Server 2017にアップグレード。すると、以下のエラーが出てログインできなくなりました。

対象のプリンシパル名が間違っています。SSPIコンテキストを生成できません。(Microsoft SQL Server)

こういう訳の分からんエラーが出た時は、そのままの文で検索すれば大体解決方法が載っているんですが、今回は的確な解決策が見当たりません。なので、備忘録としてブログアップしておきます。

このエラーの原因は、Kerberos認証がうまくいってないってことです。ということは、SQL認証では問題ない筈と考えてSQL認証をすると、うまく行けました。
ところで、今までのサーバはどうだったかというと、やはりKerberos認証ができていない事が分かりました。ではどうやって今までは使えてたかというと、Kerberos認証が失敗すると、NTLM認証でログインするようです。Kerberos認証か、NTLM認証かを確認するには、masterデータベースのクエリで以下を実行すると分かります。

 select auth_scheme from sys.dm_exec_connections where session_id=@@spid;

今回このエラーが出たという事は、SQL Server 2017ではNTLM認証を認めなくなっているって事かと考えました。なので、Kerberos認証できるようにすれば解決できるので、何故Kerberos認証できないのかを調べました。

つまるところ、デフォルトでは絶対にKerberos認証ができません。なんじゃそらって感じですね。
原因は、Kerberos認証をするための、SQL ServerのSPN(サービスプリンシパル名)の登録がされていないとのことです。では何故登録されてないかというと、SQL Serverのサービスが起動するときに、自動的に登録されるようになっているんですが、サービスのアカウントにSPNを登録する権限が無いからです。これまたどういう事かというと、SPNはActive Directoryに登録されるんですが、SQL Serverのデフォルトのアカウントは、

 NT Service\MSSQLSERVER

という、ローカルの仮想アカウントが設定されています。ローカルのアカウントでActive Directoryへの書き込みができる訳ありません。ここが、デフォルトでは絶対にKerberos認証ができないという理由です。

解決策は、主に2つ
(1)サービスアカウントをSPNに登録できる権限のアカウントに変更する
   試しに、Active DirectoryのAdministratorを設定したところ、解決できました。
   ※「サービス」から「SQL Service」を探し、右クリックでプロパティを開き、「ログオン」のアカウントを変更します。
(2)SPNを手動で登録する
 SETSPNというコマンドを実行します。但し、上記仮想アカウントはローカルなので、アカウント名のところをサーバ名に変更する必要があります。例えば、サーバのFQDNが「sql01.hoge.local」だとすると、以下コマンドにします。

 setspn –s MSSQLSvc/sql01.hoge.local:1433 sql01

※ご使用の環境により異なりますので(特に1443のポート番号)、setspnで検索して、情報を得てください。

これで、一安心です!

2018-01-24 | Posted in 入江(社長), 備忘録Comments Closed 

関連記事