Solved

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

Posted on 2013-06-13
11
1,164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 39247264
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
ID: 39247995
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
ID: 39248135
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:j0s3ph
ID: 39248252
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
ID: 39248285
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
 

Author Comment

by:j0s3ph
ID: 39250819
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
ID: 39250839
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
ID: 39257140
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
ID: 39257695
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
ID: 39257771
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
ID: 39281347
This seems to be the way to do this if you are above a 2003 Domain Functional level.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can we attach PDF to table 2 46
SQL Lag Function DateDiff 2 26
When are cursors useful? 8 60
Populating a table from inside a trigger 2 15
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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