SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

ネットワーク共有上のファイルを Windows 認証で BULK INSERT する際の考慮点

leave a comment

ネットワーク共有上のファイルを Windows 認証を使用して次のようなステートメントで BULK INSERT を実行しようとします。

BULK INSERT NATION
FROM 
'\\xxxxxxx\Share\NATION.csv'
WITH
(
	FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a'
)

 

このような実行をした場合、共有フォルダーに対して適切なアクセス権が付与されていても「アクセスが拒否されました。」のエラーが発生し、インポート対象となるファイルにアクセスができないケースがあります。

このような場合、どのような点に注意して動作を確認していけばよいのかをまとめておきたいと思います。

Windows 認証で BULK INSERT を実行する場合の考慮点

SQL Server 認証を使用している / SQL Server 上で直接 BULK INSERT を実行している場合は問題はないのですが、BULK INSERT のステートメントを実行する環境がリモート環境であり Windows 認証を使用している場合は、セキュリティアカウントの委任についての考慮が必要となります。

これについては BULK INSERT のドキュメントに記載されています。

Windows 認証を使用して UNC パスでアクセスする場合には委任の設定が実施されていないと、共有ディレクトリに対してのアクセスでエラーが発生するケースがあります。

これについては、次のような投稿でも同様の質問が上がっています。

MS の公開情報としては次のような記事で情報が公開されています。

Bulk Insert and Kerberos が調査方法についても解説されており、この情報が一番まとまっているのではないでしょうか。

本事象が発生した場合、SQL Server 上で Process Monitor を実行して、共有ディレクトリに対してのアクセス状況を確認することになります。

Windows 認証を使用して Bulk Insert を実行した場合、SQL Server のサービスアカウントのユーザーから、Windws 認証で接続をしたユーザーに偽装して処理が実行されることになります。

そのため、Process Monitor で CSV ファイルへのアクセスを確認すると Impersonating として、Windows 認証で接続したユーザー名が表示された状態となります。

image

Impersonating に表示されているユーザーが、共有フォルダーに対して適切なアクセス権が付与されていても、今回のケースでは「ACCESS DENIED」となってしまい、ファイルに対してアクセスすることができません。

この状態で、Kerberos イベントログ を SQL Server で取得すると、次のようなエラーがシステムのイベントログに出力されます。

  LogonSession  
  ClientTime  
  ServerTime 12:38:47.0000 10/22/2024 Z 
  ErrorCode 0xd 
  ErrorMessage KDC_ERR_BADOPTION 
  ExtendedError 0xc0000225 KLIN(0) 
  ClientRealm  
  ClientName  
  ServerRealm EXAMPLE.COM 
  ServerName cifs/WAC-01.example.com 
  TargetName cifs/WAC-01.example.com@EXAMPLE.COM 
  ErrorText  
  File onecore\ds\security\protocols\kerberos\client2\kerbtick.cxx 
  Line 130a 
   3015A103020103A20E040C250200C00000000003000000 

 

このような事象は SQL Server 上で該当のユーザーで Windows 認証を使用してクエリを実行した場合は発生しないのですが、公開情報に記載されている次のような構成 (クエリを実行する環境と、SQL Server が別になっている) で実行した場合に、本事象が発生するケースがあります。

 

今回のケースであれば、すべての環境 (クライアント / SQL Server / ファイルサーバー) が同一のドメインに参加していますので、AD の Active Directory ユーザーとコンピューターから、SQL Server のコンピューターアカウントのプロパティを開いて、ファイルサーバーに対しての CIFS の委任の設定を行い、SQL Server のコンピューターを再起動します。

image

この状態で BULK INSERT をファイルサーバーのコンピューター名 or FQDN で実行してみると、エラーが発生せずに実行ができるケースがあります。

今回のケースであれば、次のような情報を確認して整理しておくことが重要になるのではないでしょうか。

  • 共有フォルダーのアクセス権
  • AD 上での委任の設定
  • Windows 認証 / SQL Server 認証のどちらを使用しているか
  • BULK INSERT のクエリを実行する環境は SQL Server 上か / リモートサーバーのどちらとなるか
  • BULK INSERT で指定したファイルサーバーの情報 (IP アドレスではなく、コンピューター名 / FQDN で指定)
Share

Written by Masayuki.Ozawa

10月 22nd, 2024 at 11:24 pm

Posted in SQL Server

Tagged with

Leave a Reply