Kerberos認証を用いてファイル共有上のデータを基にBulk Insertするとアクセス拒否で処理が失敗する


 

横井 羽衣子 (よこい ういこ)
SQL Server Developer Support Engineer

皆さん、ご機嫌よう。今日は共有フォルダ上のファイルをソースとして Bulk Insert を実行した際、アクセス拒否エラーが発生する現象についてご紹介します。

エラー :
メッセージ 4861、レベル 16、状態 1、行 1
ファイル "<CSV ファイル名>" を開けなかったので、一括読み込みできません。オペレーティング システム エラー コード 5(アクセスが拒否されました。)。

image

発生条件 :
・Active Directory 環境であること
・Windows 認証 を使うこと
・Bulk Insert のソースは SQL Server 構成マシンとは別のマシン上に配置されていること
※ SQL Server 認証ユーザーで実行される場合は、問題は発生しません。
※ Always ON / シングル構成に限らず発生します。

image

図 : File Server ”sql178cl” 上のフォルダ “ShareforAlwaysOn1” 内の CSV ファイル “t1.csv” をソースにして Bulk Insert を実行する際は、SQL Server がファイルサーバーにアクセスすることになる

原因

ファイルサーバー上のファイルをソースとして Bulk Insert を行う場合、SQL Server が元々のアクセス元であるクライアントの資格情報を利用してファイル サーバーにアクセスします。ここで SQL Server がクライアントの資格情報を利用するためには、 SQL Server のサービスを動作させているアカウント (SQL Server サービスの起動アカウント) が委任において信頼される必要があります。委任において信頼されるよう、SQL Server サービスを設定しない状況においてファイルサーバー上のソースにアクセスしようとすると、結果として上記のようにアクセス拒否エラーのためファイルを読み込むことができず、bulk insert が失敗します。

対処方法

対処方法は、Kerberos 認証を行うよう構成し、かつ、委任設定を行うことになります。

"委任" とは、ネットワーク中のリソースにアクセスするため、ユーザー アカウントまたはコンピュータ アカウントの代理として動作することをサービスに許可することを指します。"委任に対して信頼されている" サービスは、ユーザーの代理として他のネットワーク サービスを使用できるようになります。
つまり、対処方法は委任設定を実施することですが、以下の二つの方法があります。

1. 無制限委任
       対象 : SQL Server 実行ユーザー
2. 制限付き委任
対象 : SQL Server 実行ユーザー、SQL Server 稼働コンピューター アカウント (AlwaysON など構成マシンが複数存在する場合は、全稼働コンピューターアカウント対象)

無制限委任の場合は、特定のサービスを指定せず委任を構成します。対象は SQL Server サービス起動アカウントとなります。
この設定を実施することで、クライアントからファイル サーバーに対するチケットを取得するために必要なアクセス元ユーザーの Kerberos のチケット保障チケット TGT (Ticket-granting Ticket) 自体が提供されるようになります。SQL Server は、その TGT を利用してファイル サーバーに対するチケットを取得しますが、ここでは SQL Server 起動アカウントで閉じた認証処理となりますので、SQL Server 稼働コンピューターに対する委任設定は、指定されたサービスの委任時においてのみ必要です。

一方制約付きの委任 (指定されたサービスに対しての委任だけを許可) の場合は、ファイル サーバーへのアクセス時には特殊な動きになります。
具体的には、 OS の動作として、 SQL Server 実行ユーザーだけでなく、システム アカウントによる認証処理要求も行われます。この動作のために、委任設定を SQL Server 実行ユーザーに対しておこなうだけでなく、 SQL Server 稼働コンピューター アカウントに対しても行う必要があります。指定されたサービスの委任設定時には、ファイル サーバーのチケットを特殊な方法で取得する必要があります。具体的には SQL Server は、ファイル サーバーへのアクセス時に自身のチケットをドメイン コントローラーに提供し、そのチケットに含まれるキー情報を元にファイル サーバーにアクセスするためのチケットを暗号化してもらう必要があります。このときファイル サーバーへのアクセスのためには SQL Server 起動アカウントに加えて、 SQL Server のシステム アカウントも利用されます。その両アカウントが、ファイル サーバーに対してのチケット情報を利用する必要があるため、 SQL Server のシステム アカウントについても委任設定が行われている必要があります。
そのため、SQL Server 稼働コンピューター アカウントに対しても指定されたサービスに対する委任だけを許可する際は、委任設定が必要です。

ユーザーに特定のサービスに対する委任時の信頼を付与する
https://msdn.microsoft.com/ja-jp/library/cc757194(v=ws.10).aspx

なお、この動作は Kerberos 認証自体ではなくファイル共有にアクセスする場合に利用される OS のリダイレクタというコンポーネントと Windows の認証プロセスの実装により生じている動作になります。

参考 :
Constrained Delegation For CIFS fails with ACCESS_DENIED.
http://support.microsoft.com/kb/2602377/en-us

対処方法の流れ

手順をまとめますと以下の通りです。

※ 注意
なお、それぞれ委任構成後は、SQL Server 稼働マシンを再起動する必要があります。再起動しない場合、SQL Server 稼働マシンの Kerberos チケットがキャッシュされているため、有効期限(キャッシュされたタイミングと委任構成作業のタイミングによっては、10 時間程度かかる場合もあります)までチケットが反映されず、問題が継続することがありますので、必ず反映のため SQL Server 稼働マシンを再起動してください。

■ 1. 無制限の委任構成方法

1. ドメインコントローラーにドメインの管理者でログインします。
2. "Active Directory ユーザーとコンピュータ" を起動します。
3. コンソール ツリーで "Users" を選択します。
4. SQL Server サービスの起動アカウントを右クリックし、"プロパティ" を表示します。(下記図)
5. "委任" タブを開き、"任意のサービスへの委任でこのコンピューターを信頼する (Kerberos のみ)" を選択します。(下記図)
6. [OK] を押し下げしてプロパティ ダイアログを閉じ、SQL Server 稼動マシンを再起動します。(※ 設定を反映するには再起動が必要です)

image

図 : SQL Server の起動アカウント = Administrator の場合

■ 2. 制限つき委任構成方法

シングル構成で制限付きの委任を構成する方法
1. ドメインコントローラーにドメインの管理者でログインします。
2. "Active Directory ユーザーとコンピュータ" を起動します。
3. コンソール ツリーで "Users" を選択します。
4. SQL Server サービスの起動アカウントを右クリックし、"プロパティ" を表示します。
5. "委任" タブを開き、"指定されたサービスへの委任でのみこのユーザーを信頼する"、"任意の認証プロトコルを使う" を選択します。(下記図A)
6. "追加" を押下し「サービスの追加」画面を表示します。
7. "ユーザーまたはコンピューター" を押下し「ユーザー または コンピューターの選択」画面を表示します。
8. "選択するオブジェクト名を入力してください" にファイル シェアのあるコンピュータ名を入力し、"名前の確認"、"OK" の順に押下します。
9. "サービスの種類" が "CIFS" のものから、ファイル シェアのあるコンピュータ名のものを選択し、"OK" を押下します。(下記図B)
10. ファイル シェアのあるコンピュータを右クリックし、"プロパティ" を表示します。
11. "委任" タブを開き、"指定されたサービスへの委任でのみこのユーザーを信頼する"、"任意の認証プロトコルを使" を選択します。(下記図A)
12. "追加" を押下し「サービスの追加」画面を表示します。
13. "ユーザーまたはコンピューター" を押下し「ユーザー または コンピューターの選択」画面を表示します。
14. "選択するオブジェクト名を入力してください" にファイル シェアのあるコンピュータ名を入力し、"名前の確認"、"OK" の順に押下します。
15. "サービスの種類" が "CIFS" のものから、ファイル シェアのあるコンピュータ名のものを選択し、"OK" を押下します。
SQL Server の起動アカウントに加え、SQL Server の稼働マシンアカウントに対しても CIFS の委任の設定を行います。(下記図C)
16. SQL Server 稼動マシンを再起動します。(※ 設定を反映するには再起動が必要です)

image

図A : SQL Server の起動アカウント (上記例では、Administrator) に対して CIFS サービスを委任に追加した例

image

図B : サービスの追加で、ファイル配置サーバー(ファイルサーバ、この例では SQL178CL というマシン)の CIFS サービスを委任に追加した例

image

図C : SQL Server の稼働コンピューター(上記例では SQL178SQL1) に対して、CIFS サービスを委任に追加した例

※ 補足 : Windows 認証で Kerberos 認証ではなく NTLM 認証になるパターン
SQL Server は起動時に SPN を自動的に登録しており、これによって Windows 認証での接続では Kerberos 認証が使用されるようになります。もしも SQL Server のサービス起動アカウントに SPN を登録する権限がない場合には SPN は登録されないため、NTLM 認証が使用されます。NTLM 認証では委任の設定を行ってもそれを使用できませんので、このような場合には手動での SPN 登録を実施ください。詳細については以下のトピックをご覧ください。

Kerberos 接続用のサービス プリンシパル名の登録
https://msdn.microsoft.com/ja-jp/library/ms191153.aspx

AlwaysOn 構成で制限付きの委任を構成する方法
0. 以下についてあらかじめ構成を確認しておきます。
-a. AlwaysOn AG 環境を構成するすべての SQL Server の起動アカウントを同一にする。
-b. AlwaysOn AG リスナーの名前の SPN を手動登録しておく。(SetSPN -s MSSQLSrv/リスナー名のFQDN:ポート番号)
1. ドメインコントローラーにドメインの管理者でログインします。
2. "Active Directory ユーザーとコンピュータ" を起動します。
3. コンソール ツリーで "Users" を選択します。
4. SQL Server サービスの起動アカウントを右クリックし、"プロパティ" を表示します。
5. "委任" タブを開き、"指定されたサービスへの委任でのみこのユーザーを信頼する"、"任意の認証プロトコルを使う" を選択します。
6. "追加" を押下し「サービスの追加」画面を表示します。
7. "ユーザーまたはコンピューター" を押下し「ユーザー または コンピューターの選択」画面を表示します。
8. "選択するオブジェクト名を入力してください" にファイル シェアのあるコンピュータ名を入力し、"名前の確認"、"OK" の順に押下します。
9. "サービスの種類" が "CIFS" のものから、ファイル シェアのあるコンピュータ名のものを選択し、"OK" を押下します。
10. ファイル シェアのあるコンピュータを右クリックし、"プロパティ" を表示します。
11. "委任" タブを開き、"指定されたサービスへの委任でのみこのユーザーを信頼する"、"任意の認証プロトコルを使う" を選択します。
12. "追加" を押下し「サービスの追加」画面を表示します。
13. "ユーザーまたはコンピューター" を押下し「ユーザー または コンピューターの選択」画面を表示します。
14. "選択するオブジェクト名を入力してください" にファイル シェアのあるコンピュータ名を入力し、"名前の確認"、"OK" の順に押下します。
15. "サービスの種類" が "CIFS" のものから、ファイル シェアのあるコンピュータ名のものを選択し、"OK" を押下します。
※ SQL Server の起動アカウントに加え、SQL Server の稼働マシンアカウントに対しても CIFS の委任の設定を行います。前述の構成例の場合は、SQLServ1 と SQLServ2 が対象となります。 (下記図)
16. [OK] を押し下げしてプロパティ ダイアログを閉じ、SQL Server 各稼動サーバーを再起動します。(※ 設定を反映するには再起動が必要です)

imageimage

図 : SQL Server AlwaysOn の構成環境 2 台それぞれのコンピュータアカウント(上記例では SQL178SQL1SQL178SQL2)に対し、ファイルサーバの CIFS サービスの委任を設定する

委任が設定されることにより、ネットワーク越しのファイル リソースにアクセスできるようになります。

Comments (0)

Skip to main content