通常はWindows 認証を使用していますが、SQL Server 認証を使用した経験がなかったのでテストし内容をメモします。
仕様を知るって大事ですね。
msdn ライブラリの説明では分からなかった・・・
今回の内容は下記msdn ライブラリにしっかり説明されています。BULK INSERT または OPENROWSET(BULK...) を使用した一括データのインポート(SQL Server)しかし、私の場合、当初はライブラリを読んだだけでは半分も理解できませんでしたが、実機でテストしていくと徐々に内容が分かりました。
下記引用文は、今回の中核をなす大切な文章です。
セキュリティの注意点
ユーザーが SQL Server ログインを使用する場合は、SQL Server プロセス アカウントのセキュリティ プロファイルが使用されます。 SQL Server 認証を使用したログインは、データベース エンジン以外では認証できません。 そのため、SQL Server 認証を使用したログインによって BULK INSERT コマンドが開始されると、SQL Server プロセス アカウント (SQL Server データベース エンジン サービスで使用されるアカウント) のセキュリティ コンテキストを使用してデータへの接続が行われます。 ソース データを正しく読み取るには、SQL Server データベース エンジンで使用されるアカウントに対して、ソース データへのアクセス権を付与する必要があります。これに対して、SQL Server ユーザーが Windows 認証を使用してログインした場合、そのユーザーは SQL Server プロセスのセキュリティ プロファイルに関係なく、そのユーザー アカウントでアクセス可能なファイルのみを読み取ることができます。たとえば、SQL Server のインスタンスに Windows 認証を使用してログインしたユーザーを考えます。 ユーザーが BULK INSERT または OPENROWSET を使用してデータ ファイルから SQL Server テーブルにデータをインポートするには、アカウントにデータ ファイルの読み取りアクセス許可が与えられていなければなりません。 データ ファイルへのアクセスで、SQL Server プロセスにそのファイルへのアクセス許可がなくても、ユーザーはそのファイルからテーブルにデータをインポートできます。 SQL Server プロセスにファイル アクセス許可を与える必要はありません。
SQL Server および Microsoft Windows では、認証されている Windows ユーザーの資格情報を転送することで、SQL Server インスタンスから別の SQL Server インスタンスへ接続できるように構成することが可能です。 この設定を、"権限借用" または "権限委譲" といいます。 BULK INSERT または OPENROWSET を使用する場合は、SQL Server バージョンによってユーザーの権限借用のセキュリティがどのように処理されるかを理解しておくことが重要です。 ユーザーの権限を借用することで、SQL Server プロセスまたはユーザーのいずれかが使用しているコンピューターとは異なるコンピューターにデータ ファイルを常駐させることができます。 たとえば、Computer_A 上のユーザーが Computer_B 上のデータ ファイルにアクセスでき、資格情報の委任が適切に設定されている場合、このユーザーは、Computer_C 上で実行されている SQL Server のインスタンスに接続して、Computer_B 上のデータ ファイルにアクセスし、そのファイルから Computer_C 上のテーブルにデータを一括インポートできます。
BULK INSERT を実行するための設定
SQL Server 認証を使用する場合は諸々の設定が必要です。設定をせずにBULK INSERT ステートメントを実行すると
ファイル "\\ServerName\xxx\xxxx.csv" を開けなかったので、一括読み込みできません。オペレーティング システム エラー コード 5(アクセスが拒否されました。)。
一括読み込みステートメントを使用する権限がありません。のエラーが表示されました。
原因は、データ ソースへのアクセス許可がないためです。
設定方法は下記手順となります。
- サーバ上にCSVファイルを保存できるようにする
例）\\ServerName\ShareFolder$\xxx.csv
※$は隠し共有フォルダを意味してます。 - CSVファイルを保存するフォルダに対し、アクセス許可を追加
追加するユーザは、私の環境では[NETWORK SERVICE]でした。
許可項目は初期値のまま。
設定は、SQL Serverをインストールしたサーバ上で行いました。
- 該当のテーブルに対し、[挿入]許可を付与
SSMS（SQL Server Management Studio）上で設定します。
- [セキュリティ]→[サーバー ロール]→[bulkadmin]のロール メンバーにSQL Server 認証を使用しログインするユーザを追加
フォルダのアクセス許可に追加するユーザ
CSVファイルを保存したフォルダにアクセス許可を追加すると前述しました。追加するユーザは、[SQL Server 構成 マネージャ]→[SQL Serverのサービス]の一覧にあるSQL Serverのログオン ユーザになります。
私の環境では、ログオン ユーザは[NT AUTHORITY\NETWORK SERVICE]、実際にアクセス許可に追加するユーザ名は[NETWORK SERVICE]です。
BULK INSERT ステートメントのサンプル
サンプルは以前の記事をご覧ください。SQL Serverで大量レコードをインサートする方法
余談：Truncate Tableを実行する場合
BULK INSERT ステートメントを実行する前にテーブルを空にしたい場合があると思います。DELETE ステートメントではなく、Truncate Tableを実行すると[高速]かつ[リソースの低消費]となります。
実際に実行する場合、データベース ロールでリソースへの制限を行っている場合は設定が必要です。
[データベース ロールのプロパティ]→[セキュリティ保護可能なリソース]→[明示的]リストの[変更]の[許可]にチェックを行う事で、VBA等からTruncate Tableを実行する事が出来ます。
