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

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

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

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

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

図 : 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. https://support.microsoft.com/kb/2602377/en-us

対処方法の流れ

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

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

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

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

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

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

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

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

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

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. コンソール ツリーの "Computers" から SQL Server の稼働マシンのコンピュータ名を右クリックし、"プロパティ" を表示します。
11. "委任" タブを開き、"指定されたサービスへの委任でのみこのユーザーを信頼する"、"任意の認証プロトコルを使う" を選択します。
12. "追加" を押下し「サービスの追加」画面を表示します。
13. "ユーザーまたはコンピューター" を押下し「ユーザー または コンピューターの選択」画面を表示します。
14. "選択するオブジェクト名を入力してください" にファイル シェアのあるコンピュータ名を入力し、"名前の確認"、"OK" の順に押下します。
15. "サービスの種類" が "CIFS" のものから、ファイル シェアのあるコンピュータ名のものを選択し、"OK" を押下します。
※ SQL Server の起動アカウントに加え、SQL Server 稼働マシンアカウントに対しても CIFS の委任の設定を行います。下記の例では、SQL178SQL1 と SQL178SQL2 が対象となります。 (下記図)
16. [OK] を押し下げしてプロパティ ダイアログを閉じ、SQL Server 各稼動サーバーを再起動します。(※ 設定を反映するには再起動が必要です)

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

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