BULK INSERT runs fine in window, but fails inside sql job

Question

I have two server (in the same network), Server A, is the web server, with IIS and all, Server B is the second server, the DB (MS SQL Server 2012), both Windows 2012 R2.

In the Server A, files are uploaded to this folder, uploaded-bills. That I have shared with \\web-server-01\uploaded-bills\.

I have given FULL ACCESS (in the security tab) to EVERYONE in that folder!

Now, this bulk insert runs in the SQL Server in Server B

INSERT INTO BillDetails (
    ItemID
    ,Qty
    ,Amount
    )
SELECT ItemID
    ,Qty
    ,Amount
FROM OPENROWSET(BULK '\\web-server-01\uploaded-bills\itemised-bill.csv', FIRSTROW = 2, FORMATFILE = 'D:\itemised-bill-format-file.fmt') AS BulkLoadFile;

Which runs perfectly fine. But if I create a job, to ran at a particular schedule, it always fails. When it is triggered automatically (due to time cycle) or I ran the job manually. When I view the history, it shows me this error:

The job failed.  The Job was invoked by Schedule 16 (InsertItemisedBillsEveryFiveMinutes).  The last step to run was step 1 (BulkInsert).,00:00:00,0,0,,,,0
05/29/2017 15:30:00,InvoiceUploadRawInsertJob,Error,1,SERVER-B,BillsUploadRawJob,BulkInsert,,Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot bulk load because the file "\\web-server-01\uploaded-bills\itemised-bill.csv" could not be opened. Operating system error code 5(Access is denied.). [SQLSTATE 42000] (Error 4861).

As I mentioned, I have given everyone the whole access, even then the sql sever agent can't seem to access it. Also, I can access this folder fine in the windows explorer without any issues, and even the query executes fine when you run this query in a new query window. But for the love of Microsoft I can't figure out why the job is failing, and what should I do to fix that. Can anyone help me figure out what am I doing wrong?

Update Update after comment and Remus's answer.

Yes Server A and Server B are in domain, in the same domain.

The SQL Server service (MSSQLSERVER) is running under a windows account name Expo (in the log on tab in the service configuration, in services.msc) amd that Expo account is available domain, so I can log in in Server A as well as Server B using that account's credentials.

The SQL Server Agent is running under (in the log on tab in the service configuration, in services.msc) NT Service\SQLSERVERAGENT.

As for Everyone, I grant it Read\Write in the file sharing dialog, under Sharing tab, and in the Security tab, I've added everyone with Full Control checkbox checked. (But I am guessing the Everyone on Server A could be different from the Everyone on Server B, I could be wrong though).

Please let me know if I could provide any more information to help sort out this.

Update 2
I've created the credentials and proxy, but in the job step dialog, Job Step Properties, the Run as option is disabled because the Type I have selected is Transact-SQL script (T-SQL) which is actually it's type. So, how do I specify the proxy account for this?

Answer 1

When you run that query interactively then SQL Server impersonates you when accessing the share. Normally this would fall into double-hop Kerberos Constrained Delegation and fail unless set up appropriately. It works because probably one of the hops is short-circuited (eg. you log on from a session on Server A or Server B).

When the query is run as a job the query impersonates the SQL Server agent service, and it all depends on how the SQL Server Agent is configured to run and connect to SQL Server. Different answers apply depending on whether the connection is made using a SQL Server login (eg. sa), a domain account, a virtual account, a local built-in account (eg. LocalService) and so on and so forth.

We here cannot guess what the problem is. To document it, please provide the details of the accounts involved. Are Server A/B in a domain? In the same domain? Is SQL Server running as a domain account, as a virtual account, as a local account, as local service, or what? Is SQL Server Agent running as who?

When you granted access to Everyone, did you grant access to the share or did you grant access to the folder?

Ideally you should set up an audit policy and then simply consult the event log, which will tell you exactly which account attempted the access. See Auditing File Access on File Servers

Finally, you may be interested in reading up about SQL Server Agent proxies.