CODEWITHSUNDEEP
sql-server
Sean
Sean

Reputation: 235

SQL Server Bulk Insert Access Issue

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?

Upvotes: 5

Views: 4571

Answers (1)

Andomar
Andomar

Reputation: 238246

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.

Upvotes: 4

Related Questions