SQL Server service permissions on network share depending on IntegratedSecurity=true/false
The problem is due to how security is handled for bulk operations and the default limitations of Impersonation.
When connecting with a SQL Server Login, there is no external Windows SID / security context, so SQL Server uses the security context of the service account when needing to reach outside of SQL Server (i.e. interact with the OS, network, etc.). So, if the SQL Server service account is the Local System account, then that is the account used for external access. Since your file is on the network, it would have to be that &quot;Local System&quot; has access to that network share. This is possible by giving the server itself access (by specifying &lt;domain_name&gt;\&lt;computer_name&gt;$ for the NTFS permissions). In this case, the SQL Server process is not using Impersonation: the external access is being made in the security context of the process itself.
When connecting to SQL Server using a Windows Login (whether AD account or local account on the server), there is an SID to use, and SQL Server will use that SID (Security ID that points to the Windows / AD account) for external access. This is what happens when connecting with &quot;Integrated Security&quot; / &quot;Trusted_Connection&quot; = true. Now, using the SID of the account logging into SQL Server means that SQL Server is using Impersonation to make it's network request appear to be coming from that other SID instead of from &quot;Local System&quot; (remember, the service account owns the sqlservr.exe process, and it is that process, not the SID logging into SQL Server, that is making the network request). Impersonation, by default, is quarantined to the machine in which the process was started (similar to how using Impersonation via EXECUTE AS user is, by default, quarantined to the Database). So, the BULK INSERT / OPENROWSET(BULK... commands should be able to see files locally on the server that SQL Server is running on, but network shares are not local, and the impersonated security context, by default, does not extend that far. What you are seeing here should be the same thing as the infamous &quot;double-hop&quot; issue.
So, you can do one of the following:

give permission on that network share to EVERYONE

continue logging in via the SQL Server Login and not worry about using Integrated Security / Trusted Connection.

Enable &quot;Delegation&quot;, though I am not sure if that will work on a Local System account (you mentioned SQL Server Agent running as &quot;Local System&quot;, so this is what would be connecting to SQL Server). It might require setting up an AD account and enabling that account for &quot;Delegation&quot; so that it can go beyond the local server. Either way, &quot;Delegation&quot; is how one configures AD to allow an account (maybe even an account / service combination) permission to go beyond the default quarantine.
P.S. @StrayCatDBA mentioned that using the Network Service account (i.e. NT AUTHORITY\NETWORK SERVICE allows for Delegation. Also, please see the &quot;Configure Windows Service Accounts and Permissions&quot; link at the bottom as that documentation goes over the various options you have.


Some info here:

Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (the Security considerations section; link should go directly there)
Kerberos Constrained Delegation Overview
How to Configure the Server to be Trusted for Delegation
How to Implement Kerberos Constrained Delegation with SQL Server 2008
Configure Windows Service Accounts and Permissions