Solved

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

Posted on 2013-06-13
11
1,122 Views
Last Modified: 2016-02-11
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.
0
Comment
Question by:j0s3ph
  • 6
  • 4
11 Comments
 
LVL 16

Expert Comment

by:DcpKing
Comment Utility
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
 

Author Comment

by:j0s3ph
Comment Utility
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
 
LVL 8

Expert Comment

by:didnthaveaname
Comment Utility
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
 

Author Comment

by:j0s3ph
Comment Utility
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
 
LVL 8

Expert Comment

by:didnthaveaname
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:j0s3ph
Comment Utility
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
 
LVL 8

Expert Comment

by:didnthaveaname
Comment Utility
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
 

Author Comment

by:j0s3ph
Comment Utility
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
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 500 total points
Comment Utility
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
 

Author Comment

by:j0s3ph
Comment Utility
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
 

Author Closing Comment

by:j0s3ph
Comment Utility
This seems to be the way to do this if you are above a 2003 Domain Functional level.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now