SQL Server Bulk Insert Access Issue

Question

I am trying to perform a bulk insert onto SQL Server:

BULK INSERT SampleData FROM '<UNC_Path>'
WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\n' )

This works running against my local database, but when I try to run against our dev server, I am getting the following error:

"Cannot bulk load because the file "..." could not be opened. Operating system error code 5(Access is denied.)

Both my account, and the SQL Service account has access to the path. My network admin is working with delegates to get this to work, but is running out of ideas. Has anyone here seen this issue before?

Answer 1

Quote from Remus Rusanu:

Your client authenticates with SQL Server, then SQL Server impersonates the client and tries to access the UNC path. That is Delegation and is implicitly forbidden. You must take the explicit steps to enable constrained delegation for the SQL Server service account. See this article explaining the details: http://msdn.microsoft.com/en-us/library/ms998355.aspx The article is shows constrained delegation for an ASP service accessing the back end database, but in your case it would be the SQL Server in the middle accessing the back end UNC share.

As an alternative, you can connect to Sql Server using Sql Authentication. That would cause Sql Server to access shares under its own account.

And are you sure the bulk insert is executed under the Sql Server account? If it's a batch job, it's probably executed under the Sql Server Agent account. By default, that's system\NetworkService. Change it to a domain user with access to the share.