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.