CODEWITHSUNDEEP
asp.netsql-serversql-server-2008-r2asp.net-2.0
Blackstar
Blackstar

Reputation: 161

Operating system error code 5(Access is denied.) when bulk insert

I have an ASP.NET application on Server A and a SQL Server on Server B. When running the appliction it calls a stored procedure. This procedure try to bulk insert from a file from a shared folder on Server A, but the following error occured:

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

The strange is that when I execute the bulk insert from the sql management studio directly, it runs without any exeptions.

Another observation: if I change the shared folder address to a local folder where the sql server runs (Server B) without shareing, no access denied error...

I tried to set bulkadmin permission to all the users, including the technical users: sql_serviceusr who runs the sql service, another technical user who runs the application and make the call to sql server and to the user who logged in to the application. All users are domain accounts and all servers using Kerberos authentication.

Server A: Windows Server 2008R2

Server B: Windows Server 2008R2, SQL Server 2008R2

Appreciate any help or guidance.

Upvotes: 1

Views: 6107

Answers (3)

Chris
Chris

Reputation: 1601

You could create a connect account and use SQL authentication.

In my experience, getting our network folks to properly set up Kerberos is like pulling teeth. We had it working for one glorious week once and then it suddenly stopped working. It just wasn't worth the hassle after that.

This might not be the exact answer you're looking for, but could be an interim solution for you.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294407

You are doing a double hop, so you need to enable Kerberos delegation.

Upvotes: 1

ronlut
ronlut

Reputation: 592

By your description of the error, I assume the problem is in sharing the folder.
When sharing a folder, you should give access to the user who will be accessing the folder from remote computer in two places in folder's properties: under the Share tab and under Security tab.
Did you give access in both of them?

Upvotes: 0

Related Questions