3

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???

Improve this question
14
  • How are you performing the bulk insert?
    – Hannah Vernon ♦
    Commented Nov 24, 2015 at 17:41
  • Can you give sql service account access to the fileshare ? That will fix the problem. --or-- you can copy the files locally and then run the BULK INSERT.
    – Kin Shah
    Commented Nov 24, 2015 at 17:41
  • 1
    Did you set up the SPN for the SQL Server?
    – Solomon Rutzky
    Commented Nov 24, 2015 at 17:56
  • 1
    Maybe look here: Register a Service Principal Name for Kerberos Connections.
    – Solomon Rutzky
    Commented Nov 24, 2015 at 20:32
  • 1
    @srutzky. Thanks for your help. I finally got the final piece of the puzzle. You can see my posted answer if you are interested.
    – Jim
    Commented Dec 4, 2015 at 22:19

1 Answer 1

Reset to default
1

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 userAccountControl attribute, 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...

Improve this answer
4
  • 1
    Glad you got it figured out. Seems like I was on the right track with thinking that the delegation wasn't set up correctly / fully. Though I had no idea that there were two aspects to delegation: the base delegation + "to_auth". Yes, there should be an easier way. There has to be. Maybe there is a GUI screen that has these options but is worded differently so it is not intuitively obvious?
    – Solomon Rutzky
    Commented Dec 4, 2015 at 22:23
  • In future: Troubleshooting Kerberos Errors
    – Remus Rusanu
    Commented Dec 4, 2015 at 22:58
  • Also, worth reading: How Windows Server 2012 Eases the Pain of Kerberos Constrained Delegation
    – Remus Rusanu
    Commented Dec 4, 2015 at 22:59
  • @RemusRusanu Thanks for the documents. I will look at them later and see if anything looks like it may still help diagnose my one server's issue. Unfortunately, it appears that the first one, Troubleshooting Kerberos Errors, is very dated (for Server 2003). Also, since I never got a Kerberos error directly from SQL, most of that document is of little help to me in this case. I also noticed in there that the docs for SETSPN are lacking. For example, I have since learned that I should be using SETSPN -S and not -A to make sure I have no dups. Back then SETSPN didn't have a -S... Thanks though...
    – Jim
    Commented Dec 6, 2015 at 13:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.