Bulk Insert and Kerberos
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…
In the example I'm going to walk through, I'm just using a simple text file (ALongTimeAgo.txt) that contains the following:
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.
Here is a look at what the environment looks like.
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.
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.
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:
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.
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:
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.
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.
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.
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
Date: 9/7/2012 1:49:41 PM
Event ID: 3
Task Category: None
A Kerberos Error Message was received:
on logon session
Server Time: 18:49:41.0000 9/7/2012 Z
Error Code: 0xd KDC_ERR_BADOPTION
Extended Error: 0xc00000bb KLIN(0)
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 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.
And reboot again.
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:
- 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)
- Disable SMB2 - this is not recommended as you could introduce performance issues
- Use Full Trust instead of Constrained Delegation - This is also not recommended as it is a less secure option.
- 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