Running Bulk Insert w/a Domain User over the network fails

We have historically run a bulk insert command from our SQL 2008 R2 server with a local sql account from a txt file over the network.  We are switching to using a domain user account that we have verified can reach the server over the network with proper rights \\server\share\text.txt.  We have a 2008 domain functional level.  I have heard we need to delegate permissions for the server (we did it through properties of ad) but I think we are stuck on how to delegate permissions to the domain user account?  We dont' have a "Delegation" tab under the user.  Can someone guide us how to fix this?  We have spent a lot of hours trying to figure it out.  See DB Admins remarks below. Thanks so much

These people have SSMS (don’t ask, it’s a very long story) and are executing a stored proc that runs the BULK INSERT

Code excerpt:
IF (@FileName LIKE '%_IN.txt')
    BEGIN
        DECLARE @SQL varchar(1000) = 'BULK INSERT #RawInputData FROM ''' + @InputPath + @FileName + '''WITH (CHECK_CONSTRAINTS)'
        EXEC(@SQL)
    END

Typically these people have been logging in with a standard SQL login that belongs to a database role that has execute permissions on the stored prod.  What I’ve done is put each of these folks’ AD logins into an AD group.  Added that group as a login and a user in the database and placed it in the same database role that has the exec permissions on the stored prod.

Problem is, when you run the stored proc under the standard sql login it works fine.  When they run it logged in as themselves I get the following error:
Cannot bulk load because the file "\\fileserver\sqlexetest\Sav28_IN.txt" could not be opened. Operating system error code 5(Access is denied.).

The first error I rec’d had to do with the AD group not having they Administer Bulk Operations system permission.  Once that was granted I started running into the Access is denied problem.

BOL states “To resolve this error, 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.”
I’ve been working with our system admin on the privelges at the server, share and file level and STILL can’t get this sucker to work.

Are you familiar with this?  Is there an answer or am I beating my head against a wall.

Also, I tried to use EXECUTE AS but can’t get that to work either.
j0s3phAsked:
Who is Participating?
 
didnthaveanameConnect With a Mentor Commented:
Man...  This is not at all easy to find information on (I completely understand your frustration on this).  I think I may have found the SPN diamond in the rough, so to speak: http://msdn.microsoft.com/en-us/library/gg723715.aspx

Edit: the really odd thing is, I can only find information regarding this for functional forests at 2003.
0
 
DcpKingCommented:
Have you considered running an SSIS job and using Exec Process to run the BCP after copying the data to a more convenient location?
0
 
j0s3phAuthor Commented:
That would require a change in the code and the way the data is imported.  Not an option at this time.  We want to get it to work as is but using an person's AD account.  The basic problem is that when a person logs into SSMS as the SQL users (ifacs_cs) and runs the exec <stored proc> from a query window, it works fine.  If they login with their Windows credentials we get the Access denied error even though the Windows accounts have the exact same permissions as the SQL login.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
didnthaveanameCommented:
Can you make sure that the AD group has permissions on the \\fileserver\sqlexetest share?  I feel like there are token differences with how file system access is handled when it's a SQL Server login vs. Windows login.
0
 
j0s3phAuthor Commented:
I setup a new share to rule out file permission issues that has Everyone-FullControll. If that domain user runs the same file in the "Bulk Insert" on the local drive of the SQL server instead of the network path it works.  That is why I setup the "Everyone" share to rule out the file permissions issue.
0
 
didnthaveanameCommented:
That's weird...  I've never seen the OpSys Error code 5: Access denied be anything but OS level permissions.  I did come across this guy: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2012/03/22/10082977.aspx.  Appears to be addressing your exact scenario.  It does appear to be from the perspective of SQL 2005, but I imagine it would hold true in 2008 since 2005 is when they changed how the security context for access to external files is handled (and it does appear to involve token/impersonation shenanigans).
0
 
j0s3phAuthor Commented:
My problem is that I don't have the option under the user account to select "Account is trusted for delegation".  THis is the part where were getting triped up.
0
 
didnthaveanameCommented:
I dunno how i missed your statement in the original post about not seeing that option, sorry!  I don't believe you get a delegation tab until you register the SPN's mentioned in that article for the sql server service account.  Have you already done that part? Also, what's the functional forest level?
0
 
j0s3phAuthor Commented:
That's what we're not quite sure how to register the spn's for the user account so we can give it delegation.  Could you give instructions on how to do this?  I believe this is where we are hung up.  The forest and domain levls are 2008.
0
 
j0s3phAuthor Commented:
That is exactly the problem I ran into.  Let me look this over and run it by the DBA and we'll see if we can put this to rest.  The crazy part is if the file runs local with a domain user, it works great.
0
 
j0s3phAuthor Commented:
This seems to be the way to do this if you are above a 2003 Domain Functional level.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.