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.
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.
BULK INSERT always impersonates Windows logins for file access. But your configuration is a double-hop, so Kerberos would be required for impersonation. So, as stated, you are stuck.
Common workaround is to create a Credential and a SQL Agent Proxy, and kick off the load from a cmdexec or powershell SQL Agent job step. Agent will use the stored credentials to logon locally and run the job, so it's no longer a double-hop.
A TSQL job step won't work as the credentials aren't used to LogonUser in that case, Agent just connects as the Agent Service Account and does SQL Server impersonation of the job owner.
You can also access the file from the application server and either copy the file to the database server, or drive the bulk load from the client, eg with BCP or SqlBulkCopy.
External links referenced by this document: