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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
USE tempdb; GO IF OBJECT_ID(N'dbo.BulkImportTest', N'U') IS NOT NULL DROP TABLE dbo.BulkImportTest; CREATE TABLE dbo.BulkImportTest ( RowID int NOT NULL CONSTRAINT BulkImportTest_PK PRIMARY KEY CLUSTERED IDENTITY(1,1) , RowData nvarchar(128) NOT NULL ) ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE); INSERT INTO dbo.BulkImportTest (RowData) SELECT REPLICATE(CHAR(((ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) % 26) + 65), 100) FROM sys.syscolumns sc1 GO |
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:
|
1 2 3 4 5 6 |
rmdir /s /q C:\bulk_insert_test mkdir C:\bulk_insert_test icacls.exe C:\bulk_insert_test\. /grant:r <user_account>:(OI)(CI)(F) icacls.exe C:\bulk_insert_test\. /inheritance:r bcp dbo.BulkImportTest format nul -S <server_name> -n -T -d tempdb -x -f C:\bulk_insert_test\BulkImportTest.fmt bcp dbo.BulkImportTest out C:\bulk_insert_test\BulkImportTest.bcp -S <server_name> -T -d tempdb -f C:\bulk_insert_test\BulkImportTest.fmt |
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:
|
1 2 3 4 5 |
SELECT TOP(1) * FROM OPENROWSET( BULK 'C:\bulk_insert_test\BulkImportTest.bcp' , FORMATFILE = 'C:\bulk_insert_test\BulkImportTest.fmt' ) c; |
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:
|
1 2 3 4 5 |
╔═══════╦═════════════════════════════════════╗ ║ RowID ║ RowData ║ ╠═══════╬═════════════════════════════════════╣ ║ 1 ║ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA... ║ ╚═══════╩═════════════════════════════════════╝ |
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.
|
1 2 |
icacls.exe C:\bulk_insert_test\. /remove <user_account> icacls.exe C:\bulk_insert_test\. /grant:r <service_account>:(OI)(CI)(F) |
Now, if you run the SELECT ... OPENROWSET code, you should see an error.
|
1 2 3 4 5 |
SELECT TOP(1) * FROM OPENROWSET( BULK 'C:\bulk_insert_test\BulkImportTest.bcp' , FORMATFILE = 'C:\bulk_insert_test\BulkImportTest.fmt' ) c; |
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:
- 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.
-
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 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.
|
1 2 |
icacls.exe C:\bulk_insert_test\. /grant:r <user_account>:(OI)(CI)(F) icacls.exe C:\bulk_insert_test\. /inheritance:r |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DELETE FROM dbo.BulkImportTest; BULK INSERT dbo.BulkImportTest FROM 'C:\bulk_insert_test\BulkImportTest.bcp' WITH ( CHECK_CONSTRAINTS , DATAFILETYPE = 'native' , FORMATFILE = 'C:\bulk_insert_test\BulkImportTest.fmt' , KEEPIDENTITY , KEEPNULLS , ORDER (RowID ASC) , ROWS_PER_BATCH = 1000 , TABLOCK ); SELECT * FROM dbo.BulkImportTest; |
Output should look similar to:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
╔═══════╦═════════════════════════════════════╗ ║ RowID ║ RowData ║ ╠═══════╬═════════════════════════════════════╣ ║ 1 ║ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA... ║ ║ 2 ║ BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB... ║ ║ 3 ║ CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC... ║ ║ 4 ║ DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD... ║ ║ 5 ║ EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE... ║ ║ 6 ║ FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF... ║ ║ 7 ║ GGGGGGGGGGGGGGGGGGGGGGGGGGGGGGGG... ║ ║ 8 ║ HHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHH... ║ ║ 9 ║ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII... ║ ║ 10 ║ JJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJJ... ║ ║ 11 ║ KKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK... ║ ║ 12 ║ LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL... ║ ║ 13 ║ MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM... ║ ║ 14 ║ NNNNNNNNNNNNNNNNNNNNNNNNNNNNNNNN... ║ ║ 15 ║ OOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO... ║ ║ 16 ║ PPPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP... ║ ╚═══════╩═════════════════════════════════════╝ |
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
Hannah,
I think you left out something important to mention in this article, namely the need to NOT configure the ‘Back-End” SQL Server instance (the SQl Server being linked to as a Linked Server and from which data is being requested via the “Front-End” SQL Server instance) to use Dynamic TCP/IP Ports. The use of Dynamic Ports defeats the ability to assure correct passage through the firewall for delegation requests, unless really poor firewall configuration practices are used (which would have to be categorized under “highly NOT recommended” ideas).
Also, there are added complexities to this entire story if Trusted relationships between different domains or forests is involved in the jump between servers as well. In short, there are many possible sources of tripping hazards for even a proper Kerberos configuration to fail.
One other question: How can we “lock down” and secure the Kerberos configuration, as the built-in choice tor allowing “everything” to authenticate creates holes for subtle abuses of the network by black-hat folk seeking authentication for nefarious purposes. Documentation suggests that we should specify FQDNs(?) in the configuration list, but that didn’t want to work for me (unless I was using a kerberos SPN where a FQDN was required – or vice versa). What is the “right way” to lock down the Kerberos authentications to curb most potential for abuse?
Also, I’d found another article that suggested using the ASDI Edit application to permit “Read Service Process Name” and “Write Service Process Name” security access on the SQL Server’s Service Account at the Domain: Controller as a means of avoiding use of Admin-level permissions for the SQL Service login account (permissions that are apparently a part of the mix when Admin rights are granted).
Regarding dynamic ports; you’re correct. I never use dynamic ports for a variety of reasons, however I strongly advise against opening any SQL Server through a firewall. Many of my blog posts are geared towards getting something up-and-running, and do not cover every possible eventuality. As you undoubtedly know, there are a lot of edge-cases in SQL Server 👀
The “typical” guidance is to setup an SPN for both the NetBIOS machine name, and the FQDN, which allows clients to connect with the minimum amount of trouble. If you don’t create the NetBIOS SPN, you’ll need to ensure that all clients are pointing at the applicable FQDN, in order to connect via Kerberos. Note that with NTLM authentication to SQL Server, clients can still connect using the NetBIOS name, just not via a double-hop mechanism. Having said all that, I don’t know everything there is to know about Kerberos and network security in general, so don’t take my advice as “gold” without doing your own due diligence.
ADSI Edit is a great application that provides access to the large number of fine-grained permissions possible in Active Directory. If you are truly concerned about Kerberos security, and SQL Server security in general, there are a great many articles and white-papers available from Microsoft for that purpose.