Share via

Bulk Insert and Kerberos

  • Article

I recently worked on two Bulk Insert cases that dealt with Kerberos. My favorite past time! In both cases, the customers were hitting the following error:

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "<file name>" could not be opened. Operating system error code 5(Access is denied.).

This issue came down to Kerberos Delegation. In one case they were wanting to use Full Trust delegation, but there was some confusion on the CIFS principal. We don't need to add a CIFS Principal. You should just be able to enable Delegation for the SQL Service Account and it should work.

On the Constrained Delegation side of things, it turned out that we had to also enable Constrained Delegation on the Machine account of SQL Server as well as the SQL Service Account. This was due to how SMB2 works and will not always have the context of the user and instead be in the context of the System Account.

For more details about both, keep reading…

Techie Details

In the example I'm going to walk through, I'm just using a simple text file (ALongTimeAgo.txt) that contains the following:

Darth,Vader,TheDarth@galacticempire.com
Luke,Skywalker,FarmBoy@rebelsRus.com
Han,Solo,KesselRunner@rentasmuggler.com

Nothing fancy. The Bulk Insert is just going to try and load this data into a table I defined to hold a FirstName, LastName and Email.

clip_image001

Here is a look at what the environment looks like.

clip_image002

The File is sitting on a different server than what SQL Server is running on. Therein lies the problem. If we look at Process Monitor, which is a free SysInternals tool, we can see the Access Denied. This was run on the SQL Server, because that is where the CreateFile API call is made.

clip_image003

We can see that the request is trying to impersonate me instead of using the service account to access the file. We can look at dm_exec_connections to see that I'm connected to SQL using Kerberos.

clip_image004

The issue here is really about delegation. The SQL Server needs to be trusted to delegate my credential to another server/service. We see issues like this crop up because, typically, SQL Server is the back end server and the last stop on the journey of a connection/credential. So, in most cases, SQL Server will not be trusted for delegation. It is usually the Web Server or Application Server that is trusted for delegation because they want to get to SQL Server.

If I look at the delegation settings for my SQL Server's service account (BATTLESTAR\sqlservice), I see the following:

clip_image005

I have two options here. The first option is "Trust this user for delegation to any service (Kerberos Only)" which I refer to as "Full Trust". The other option, "Trust this user for delegation to specified services only", is Constrained Delegation and is more secure because you are explicitly allowing delegation for certain services and not a blanket pass.

Let's give the Full Trust option a try to see what happens. I'll need to restart the SQL Service after the change is made to clear any cache from an LSA perspective. LSA will cache failures.

clip_image006

I've had mixed results with restarting the SQL Service vs restarting the whole box (see this blog post), you may get away with just restarting the service, but you may need to reboot the box. After I restarted, I see the following:

clip_image007

That took care of one issue I was looking at. But, I was presented with another one that indicated they wanted to do Constrained Delegation. Initially their setup was not correct. When we go to use Constrained Delegation, we have to be specific about what service we specify.

Because we are hitting a file server, we are interested in the CIFS service. One thing I've seen people do is go to create the CIFS SPN because when they go to look at the Machine Account for the file server, they don't see it.

clip_image008

However, CIFS is covered by the HOST entries. Similar to HTTP. We do not need to add a CIFS SPN. However, with the Constrained Delegation, we do need to add the CIFS Service there. It should show up because the HOST entry is present on the Machine Account.

clip_image009

clip_image010

I found that I had to pick "Use any authentication protocol". I actually didn't expect that, but that is what I found through my testing.

So, with that set, I reboot the SQL Server again and give it a try.

clip_image011

We know Full Trust worked! So, why didn't Constrained Delegation work? I had enabled Kerberos Event logging earlier to catch items. So, when I look at the System Event Log on the SQL Server, I see the following:

Log Name: System
Source: Microsoft-Windows-Security-Kerberos
Date: 9/7/2012 1:49:41 PM
Event ID: 3
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: CaptThrace.battlestar.local
Description:
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 18:49:41.0000 9/7/2012 Z
Error Code: 0xd KDC_ERR_BADOPTION
Extended Error: 0xc00000bb KLIN(0)
Client Realm:
Client Name:
Server Realm: BATTLESTAR.LOCAL
Server Name: captthrace$@BATTLESTAR.LOCAL <-- This should not be the Machine Account Context
Target Name: captthrace$@BATTLESTAR.LOCAL@BATTLESTAR.LOCAL
Error Text:
File: 9
Line: f09
Error Data is in record data.

NOTE: Be aware that with Kerberos Event Logging, that failures may be caches and you may not see anything. You may have to recycle the Service or reboot the box to actually see the failure.

It is showing the Machine name, not the Service Account or my User account. That is not what I would have expected. Because we are seeing the Machine Account in this respect, that would explain why it failed, because I haven't setup any Delegation settings for the Machine Account. Only the SQL Service Account. Let's see what happens when I set the Delegation settings on the Machine Account.

clip_image012

And reboot again.

clip_image013

It works! So, what happened?

The real issue here is due to the use of SMB2 and the redirector that I used. Due to the code path that we end up coming down for Constrained Delegation within LSASS, we do not have the context of the user. Instead, we have the context of the System Account. This is why we saw captthrace$ in the Kerb Event Log entry when it wasn't expected.

SMB2 is more asynchronous to maximize performance and causes you to run into this issue with Constrained Delegation. You could actually hit this with SMB1 as well, but it isn't likely as most requests will come from a thread that has the context of the user.

So, your options to get this working if you went down the path of Constrained Delegation are the following:

  1. Enable Constrained Delegation for the Machine account (this would be the Machine account that the SMB redirector worker threads run from - in our case the SQL Server machine account)
  2. Disable SMB2 - this is not recommended as you could introduce performance issues
  3. Use Full Trust instead of Constrained Delegation - This is also not recommended as it is a less secure option.
  4. Use SQL Authentication instead of Windows Authentication

 

***** UPDATE – 9/17/2012 *****

Note:  If you are running a SQL Cluster, and want to configure Constrained Delegation, you have to do it on each physical node machine account in the cluster.  Doing this on only the virtual node account will not allow this to work properly.

 

 

Adam W. Saxton | Microsoft Escalation Services
https://twitter.com/awsaxton

Comments

  • Anonymous
    September 10, 2012
    Hi Adam, Great post. I indeed had similar issue before and your explanation is very clear. Thanks

  • Anonymous
    November 27, 2012
    Great detailed testing & explanation! Can I assume, that the same applies to SMB 3.0 (2.2) under Windows Server 2012? Cheers, Andreas

  • Anonymous
    February 13, 2014
    Thank you for this article describing the additional step for constrained delegation on the machine account.  I found a nice write up originally here (thesqldude.com/.../how-to-sql-server-bulk-insert-with-constrained-delegation-access-is-denied).  In the comments it lead me here. It's helped fix our issue with OPENROW BULK insert of jpeg files from our Server 2012 machine.

  • Anonymous
    January 22, 2016
    Great, your dokument saved my weekend :-). thanks a lot.

  • Anonymous
    January 14, 2019
    This was relatively useful. It looks like it has to do with the network redirector for SMB calls. I an thinking that the local connection from the SQL Instance to the Network redirector is making an NTLM call which is breaking the Kerberos authentication chain, hence the need for protocol transition. Feel free to let me know if my thinking is off the mark.I do have a little of a different question on this... Is anyone doing this to a file server on a Domain Based DFS Namespace. Do you need to configure constrained delegations into CIFS on the root servers and the domain controllers as well as the destination file server?