I'm experiencing the dreaded Msg 4861 error message when attempting to use Bulk Insert on a 2012 SP4 SQL Server. "Cannot bulk load because the file "\PathToFileShare.d1.xxx.com\folder\file.dat" could not be opened. Operating system error code 5 (Access is denied)
I have two different domains, D1 and D2. I have three different computers, the SQL Server (in D2), the File Server (in D1), and the Application Server(in D1).
I am trying to call a stored procedure from the Application Server that uses bulk insert to get a file from the File Server and insert into the SQL Server.
Stored procedure is executed as D1\user1 from the application which produces an error. When called from the SQL Server in D2 in SSMS logged in as D1\user1, it works as expected and correct records are inserted into the table. If I try to log into SSMS as D1\user1 on any other machine in D2, execution produces an error.
- I cannot provide the SQL Server service account in D2 access to anything in D1.
- I can confirm that D1\user1 has access to the file they are trying to read from the file share.
- I have seen the information from the following links: SQL Server Docs Additional Docs
- We are NOT using kerberos authentication.
The bulk insert part of the stored procedure looks like this:
BULK INSERT BulkInsertDB.ExampleDomain.ExampleTable FROM '\\PathToFileShare.d1.xxx.com\folder\file.dat' WITH ( ROWTERMINATOR = '0x0a' );
Any help would be much appreciated.