Issues with Bulk Insert from Remote server

1

I have 2 servers: App server and SQL Server.
App server is trying to initiate a bulk insert into SQL Server, using SQLCMD.

If I initiate the SQLCMD on the SQL server, no problem.
If I initiate the SQLCMD on the app server, I get access is denied on the file.

I have set up SPN on my SQL Server account, which is a domain account.
Both servers are configured to allow delegation.

I want to kick Kerberos in the crotch.

2

To clarify: It is the same UNC path being used in both scripts.

3

Sounds like a double hop issue as described in the 1st answer here: http://dba.stackexchange.com/questions/44524/bulk-insert-through-network

4

Thanks Tara,

I read just about all of the articles I could find on troubleshooting the double-hop issue, and I finally solved the problem thanks to some help from a really smart co-worker.

I verified:

  1. SQL Server Machine and App Server were both trusted for delegation
  2. SQL Server was running as a domain account that was also trusted for delegation
  3. There was an SPN for the domain account referencing the SQL Server machine

The kicker ended up being the way that the domain was set up, it would not accept the short name for the server, I had to use the FQDN instead (as that is how the SPN was set up....usually I don't have this problem though).

Once I changed the SQLCMD -S parameter to use the FQDN (servername.subdomain.domain.local) it worked like a charm. The big pain in the ass was that the Kerberos Config manager was giving me no problems when I checked it using the short name, so I was going down a lot of rabbit holes.

In any case, it is resolved now and thanks so much for responding here!

Joe

1 Like