BULK INSERT, Kerberos, and Delegation

Pesky Kerberos and BULK INSERT

The T-SQL commands, BULK INSERT and OPENROWSET(...), offer great ways to import data from a file into a table in SQL Server. However, getting BULK INSERT Kerberos security setup correctly can be an exercise in frustration. This post shows what you need to setup, and gives a coherent example of exactly how to import rows that have been exported from SQL Server with the BCP utility.

bulk insert kerberos

      Kerberos the Lovely!

The Test Rig

First things first, we need to setup a simple test-bed in SQL Server. We’ll do this in the tempdb database, but you probably want to make sure you run this code in a non-production, development system:

The code above creates a single table with slightly over 1,000 rows.

Next we need to create a folder where we’re going to put the bcp files we’re going to use as part of the test. You’ll need to know the name of the Active Directory Service Account used to start SQL Server. Replace <service_account_name> with the name of that account, and <server_name> with the name of your SQL Server instance in the code below. Then execute the following commands in a command-prompt:

Lines 1 and 2 drop-and-recreate the folder. Lines 3 and 4 ensures the folder is accessible only to your Active Directory account (i.e. SQL Server does not have access). Microsoft’s icacls utility provides a concise command-line method of granting the necessary rights to your user account, then revoking inheritance from the parent directory. Lines 5 and 6 create a format file and export the data from dbo.BulkImportTest into the C:\bulk_insert_test folder. If everything works correctly, you won’t see any error messages. You may see “Directory Not Found” as a result of the first command line, if this is the first time you’ve ran these commands.

Running the Test

Switch back to SQL Server Management Studio, and execute the following commands to see if we can read from the bcp file we just created:

If you already have security configured correctly, with Kerberos authentication to the SQL Server via Service Principal Names, and the SQL Server Service Account has been granted impersonation delegation rights, you should see a single line returned, as in:

If you don’t have Kerberos Delegation working correctly, you’ll see an error similar to the one in the next step.

But before we get there, let’s remove access to the folder for our own Windows account, and grant the SQL Server Service account access instead.

Now, if you run the SELECT ... OPENROWSET code, you should see an error.

Msg 4861, Level 16, State 1, Line 41
Cannot bulk load because the file "C:\bulk_insert_test\BulkImportTest.bcp" could not be opened. Operating system error code 5(Access is denied.).

The Take-Away

If you don’t see an error after removing access to your user account, that means SQL Server is using the SQL Server Service Account to access filesystem resources. In other words, impersonation is not enabled for the SQL Server Service Account. In order to get that working you need to perform the following actions:

  1. Enable Kerberos authentication by creating the necessary Service Principal Names. I have instructions here for manually creating them, without requiring a reboot of the SQL Server instance.

  2. Ensure the Service Account is “trusted for delegation” in Active Directory. This step can only be accomplished once the Kerberos Service Principal Names have been created for the instance, and the instance is accepting Kerberos authentication. Use Microsoft’s “Active Directory Users and Computers” Management Console application to configure the service account for delegation. Locate the service account in Active Directory, right-click the account, click “Properties” on the drop-down menu, then look for a “Delegation” tab.

    The Delegation Tab

        The Elusive Delegation Tab

    Choose “Trust this user for delegation to any service (Kerberos only)”. If the Delegation tab is not visible, either (a) the account does not have Kerberos SPNs setup correctly, or your Active Directory domain is configured for the Windows 2000 Functional Level. Microsoft has more details about Kerberos and Delegation here.

Once you get Kerberos Delegation working, test BULK INSERT using the code below. It first resets the security access control lists on the bulk_insert_test directory, then imports the previously exported bcp file. You should see no errors if everything is working correctly.

Output should look similar to:

I hope this post helps – if you have any comments or questions, please let me know!

Check out the rest of our series on SQL Server Troubleshooting.

Microsoft’s Documentation for OPENROWSET