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
IF (@FileName LIKE '%_IN.txt')
DECLARE @SQL varchar(1000) = 'BULK INSERT #RawInputData FROM ''' + @InputPath + @FileName + '''WITH (CHECK_CONSTRAINTS)'
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.