SSIS cannot open datafile when executed from remote host

Hi-

I have seen several problems posted where an SSIS package writes a file successfully when executed manually, but fails when executed via SQL Agent job..I have the opposite problem. I'll try to lay it out succinctly:

SSIS writes to a file on a shared folder, specified as \\HostA\Share, for example. I created the share and gave full control to Everyone (out of frustration).

I'm working from HostA via RDP, connected to the DB on DBHost via SSMS.

If I kick off a SQL Agent job that executes the package, it works fine. (SQL job is running under SQL Server Agent Service Account)

If I execute the job interactively (logged in to SSMS with Windows Auth), it fails with "Error: Cannot open the datafile "\\HostA\Share\filename.ext""

We did find that if I RDP directly to the DBHost, I am able to execute manually.

Also, if I try executing an xp_command shell command to write a file to the share, it works. (When RDP's into HostA with an SSMS connection to DBHost under my windows domain auth, as above.)

The problem is the same when I RDP to any remote host.

IT and SQL Admins are scratching their heads over this, any suggestions welcome.

thanks

-m

August 19th, 2015 3:52pm

Hi again-

I thought I had searched the forums effectively, but several closely related issues popped up once I posted, my apologies.

I'm reading through them now, there may be an issue with the 'double hop', but not sure yet.

thx

Free Windows Admin Tool Kit Click here and download it now
August 19th, 2015 4:12pm

This is a "double hop" issue.

Please see:

https://www.mssqltips.com/sqlservertip/2312/understanding-when-sql-server-kerberos-delegation-is-needed/

August 19th, 2015 4:19pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics