SSIS File System Task Access Denied

Question

SSIS File System Task Access Denied

jdh.eon.blue

SSC Veteran

Points: 226
More actions
January 18, 2018 at 3:28 pm

#334619

I have an SSIS package that writes to a csv output, saves the file locally on the server, then copies the files to a UNC share.
The package is deployed into the SSISDB Catalog.
Everything works great on my local dev machine (execute via ssms), as well as when i RDP into the server and run (via ssms), files are created and copied as expected.
However, when i run the package from my local machine, via ssms, connected to the server, the File System Task fails:
[SSIS Task]:Error: An error occurred with the following error message: "Access to the path '\\[redacted]\[redacted]\DATA\Import\[redacted].csv' is denied.".
I've made sure the SQLSERVERAGENT MSSQLSERVER are housed in a managed service account and has full control on the network path. I there an easy way to figure out what account is trying to copy the file from local to share? Why does this work when i execute the package RDP'd on the server, and not when i run it locally?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 1

Is the SSIS service account has rights on that path? Check the security log on the server, that will give you more information.

Salil-386686 Old Hand Points: 341 More actions · Answer 2

Are you able to connect to the UNC path using the managed service account from your local machine?

Phil Parkin SSC Guru Points: 246962 More actions · Answer 3

Evgeny Garaev - Thursday, January 18, 2018 6:56 PM
Is the SSIS service account has rights on that path? Check the security log on the server, that will give you more information.

This is unlikely to be relevant. The SSIS service has no impact on runtime execution of SSIS packages, as far as I know.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246962 More actions · Answer 4

jdh.eon.blue - Thursday, January 18, 2018 3:28 PM
Is there an easy way to figure out what account is trying to copy the file from local to share?

Yes, check the All Executions report:

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99130 More actions · Answer 5

I'm thinking, Phil, this is likely due to our good old friend Mr "Double Hop". If the OP is having issues running it using SSMS, but can do so fine in SSDT, then the credentials should be the same (if they're logged in as the same domain user); thus Kerboros isn't allowing the second hop to retain the credentials, and thus is using anonymous authentication.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246962 More actions · Answer 6

Thom A - Friday, January 19, 2018 9:03 AM
I'm thinking, Phil, this is likely due to our good old friend Mr "Double Hop". If the OP is having issues running it using SSMS, but can do so fine in SSDT, then the credentials should be the same (if they're logged in as the same domain user); thus Kerboros isn't allowing the second hop to retain the credentials, and thus is using anonymous authentication.

Hmm, quite possibly. Definitely seen that problem before.
For the OP: if you set this up as a SQL Agent job on the server, does that run OK? If yes, that's probably your easiest workaround, unless you have a Kerberos expert handy.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jdh.eon.blue SSC Veteran Points: 226 More actions · Answer 7

Phil Parkin - Friday, January 19, 2018 8:28 AM
jdh.eon.blue - Thursday, January 18, 2018 3:28 PM
Is there an easy way to figure out what account is trying to copy the file from local to share?
Yes, check the All Executions report:

Thanks! So, i ran the package via SSMS on my local machine, the package executed on a test sql server instance. Caller is reported as my domain user account [domain]\my.user. The share the file is being copied to, i can access via my local machine, and when i RDP into the sql server. So, again, i am confused as to why this doesn't work.

jdh.eon.blue SSC Veteran Points: 226 More actions · Answer 8

Thom A - Friday, January 19, 2018 9:03 AM
I'm thinking, Phil, this is likely due to our good old friend Mr "Double Hop". If the OP is having issues running it using SSMS, but can do so fine in SSDT, then the credentials should be the same (if they're logged in as the same domain user); thus Kerboros isn't allowing the second hop to retain the credentials, and thus is using anonymous authentication.

Can you point me in the direction of how to confirm Kerboros is not pulling over the credentials? I just responded to Phil saying within the ssis execution report, the caller is noted as my domain account.

Phil Parkin SSC Guru Points: 246962 More actions · Answer 9

jdh.eon.blue - Friday, January 19, 2018 10:37 AM
Thom A - Friday, January 19, 2018 9:03 AM
I'm thinking, Phil, this is likely due to our good old friend Mr "Double Hop". If the OP is having issues running it using SSMS, but can do so fine in SSDT, then the credentials should be the same (if they're logged in as the same domain user); thus Kerboros isn't allowing the second hop to retain the credentials, and thus is using anonymous authentication.
Can you point me in the direction of how to confirm Kerboros is not pulling over the credentials? I just responded to Phil saying within the ssis execution report, the caller is noted as my domain account.

I don't know how to confirm it ... I'm just familiar with the symptoms. While the caller is your account, this is based on 'hop 1' (from your local machine to the server). 'Hop 2' (from the server to the file) is likely where your problems lie, because the system has not passed through your domain user credentials.
You might find some enlightenment here, if you are keen to learn more.
If you do find out how to confirm that Kerberos is your problem (or not), please post back with details of what you did.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

lamtu007 Newbie Points: 2 More actions · Answer 10

Hi,

Suggest you to check the attributes of the file (which you are trying to delete). If the file attributes is ReadOnly, you should use the File system task to change the attribute (ReadOnly=False) before try to delete the file.