I am running Microsoft SQL Server 2012 SP on a Windows Server 2008 R2 Standard SP1 box. The SQL Server service is running as a simple windows domain user (nothing special, no admin rights, etc.)
I am having some issues with using Bulk Insert when the data file is on a network share when using Windows Authentication. What is known is that the SQL Server service account has access to the network resource, which is shown by logging into SQL Server with a SQL account and doing the Bulk Insert. I also have rights to the files on the share, as shown by the fact that I put the files there.
Now, when connecting to SQL Server with Windows Authentication and running the Bulk Insert I get the following error (emphasis mine):
Msg 4861, Level 16, State 1, Line 2 Cannot bulk load because the file
"\\[server]\[share]\[filename]" could not be opened. Operating system error code 5(Access is denied.).
I found this snip at BULK INSERT (Transact-SQL)\Security Account Delegation (Impersonation), which says, in part:
To resolve this error [4861], use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.
Finally, after much searching, I found this TechNet article, How to Configure the Server to be Trusted for Delegation, and we tried the unconstrained delegation and I rebooted the SQL server, but it still does not work.
What the heck am I missing???
Best Answer
OK, here is the answer to my particular problem. While the commenters above were very helpful and had some good suggestions, like manually adding the SPN, there was one final part missing. You know, one of those that has to be completed on a dark stormy third Thursday of the month, only when the moon is waxing half way...
After a few days after this posting, I basically posted the same question on the Microsoft SQL forums to get a fresh prospective. Long story short, I had to have my network admin run adsiedit.msc and have him edit the service's domain account user. The
userAccountControlattribute, which is a number that represents a bitmap fields had to be updated. I had to take a single existing number, 0x10200 (NORMAL_ACCOUNT | DONT_EXPIRE_PASSWORD) and OR in 0x80000 (TRUSTED_FOR_DELEGATION) and OR in 0x1000000 (TRUSTED_TO_AUTH_FOR_DELEGATION) for a grand total of 0x1090200!Now, isn't that user friendly! I honestly don't know why that when we setup the account in the GUI for delegation that it did not set these bits too, but it didn't.
I now have Bulk Insert running correctly for a trusted connection (Windows Authentication) and impersonating correctly on two out of three of my SQL Servers. Not sure why it is not one the one, but that is for a different day...