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

Question

Ok. I do see that there have been some good amount of similar questions that have been already asked. Form surface it may look like I shouldn't be asking this question because a lot of people have already tried to answer this question but I will explain my scenario is different:

First of all my set up:

I have two servers behind load balancer

Server A: running a windows service as a domain Service_account Server B: Running a windows service as a domain service_account

and then this windows service eventually calls the SQL Server for executing s stored procedure which of course tries to do Bulk insert from a file sitting on \network\location. Now, I don't know how important his information is but this network location is actually a folder shared as a network share on Server B so basically \network\location = Server B: C/something.

Now, I have added everyone as a user on this folder and set it to full control to make sure there are no permission issues for anyone trying to access the folder.

Now, I do receive this error :Cannot bulk load because the file “" could not be opened. Operating system error code 5(Access is denied.).

ONLY sometimes. The entire problem here is that the error is not consistently re-producible. It only happens once a week. I have tried bypassing load balancer and making requests directly from server and I have not found any issues. Also, I have also observed that requests made before and after this failing request are all successful. Which means this is in a true sense a sporadic error.

After browsing through SO, I have found two major reasons that have traditionally caused this error are:

1. When the user running ther service truly does not have access to this folder. Which is not the case because literally everyone has access to this folder.

2. I came across some threads where people are pointing to potential Kerberos Double Hop as a potential issue. As explained here: https://learn.microsoft.com/en-us/previous-versions/msp-n-p/ff649317(v=pandp.10)?redirectedfrom=MSDN

It could be Kerberos and I am digging further to see if it the issue or not. But in meanwhile, say Kerberos double hop is the culprit, what explains the sporadic nature of this error? If kerberos is dropping the credentials then wouldn't is be dropping it all the times instead of ONLY selected few times?

Or also, say if none of these two are the real reason behind this error. The real problmem is not the error itself but the very sporadic nature of the error. Could it be something in the network which could be causing the hiccup in the connection?

Answer 1

ensure that SQL Server can see the share. One good way I've found to test this is to use xp_cmdshell to do a DIR on the share. e.g.

exec sp_configure 'show advanced' , 1
RECONFIGURE
exec sp_configure 'xp_cmdshell' , 1
RECONFIGURE
exec xp_cmdshell 'dir \\host.domain.local\sharename'

If it is sporadic, it could be because DNS/network name resolution is an issue? make sure you use the FQDN. If it is Kerberos related then you can rule out as an issue by using the IP address of the server hosting the share, i.e. exec xp_cmdshell 'dir \\10.10.10.x\sharename' When you use the IP, the authentication has to fallback to NTLM. Depending on security settings of servers, you may need to change some GPOs to get this to work.

Answer 2

Another reason may by CONCURRENCY.

When excel file is already open by another user in network, and during then when we open the file, we get message like "File will be in Read only mode" (don't remember correctly).

Similarly, when we read excel file through application like C# etc. and if there are many user using same file simultaneously then we will get same error.

Our application translate such error to "Access is denied..." . So sometimes it work fine and sometimes it throw error.

So you can try to reproduce in CONCURRENT environment.

Do the change in your excel as describe in this link.

Answer 3

Id be curious to see what using xp_dirtree says. I would use it for the root folder and set the variables so that it goes "deep enough" to find your folder.

 EXEC master.sys.xp_dirtree ‘filePath1’,0,1;

Answer 4

Thank you so much for taking time for responses and I really appreciate your responses.

It just took some time to get some more information on this issue. So as it turned out, it is kerberos-double-hop issue.

We did dig into the machine logs and we found following logs on the machine which exactly co-incide with the random errors that we are facing. The log looks like following:

The delegated TGT for the user (username@domainname) has expired. A renewal was attempted and failed with error 0xc0000001. The server logon session (0x9:8748fa4a) has stopped delegating the user's credential. For future unconstrained delegation to succeed, the user needs to authenticate again to the server.

The username for which we saw the the error is the user name of the service account which runs web service and which eventually calls the failing stored procedure.

So this establishes the it to be a kerberos-double-hop issue. We are still not sure why it only happens sometimes-randomly.But it still is kerberos-doublehop