question

scottHanebutt-4376 avatar image
0 Votes"
scottHanebutt-4376 asked Monalv-msft edited

Permissions Issue Through a Job

SQL Server 2017

I have created an SSIS package that creates a text file on the "Y" drive which is mapped to another server. When I run the package through the design interface it correctly creates the file. However when I have a SQL job run the package I get the following error.

Executed as user: DOMAIN\DomainAdmin. Microsoft (R) SQL Server Execute Package Utility Version 14.0.1000.169 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 9:28:43 PM Error: 2021-03-18 21:28:44.93 Code: 0xC020200E Source: Data Flow Task 1 Flat File Destination [2] Description: Cannot open the datafile "Y:\FILE5945031821.txt". End Error Error: 2021-03-18 21:28:44.93 Code: 0xC004701A Source: Data Flow Task 1 SSIS.Pipeline Description: Flat File Destination failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:28:43 PM Finished: 9:28:44 PM Elapsed: 1.063 seconds. The package execution failed. The step failed.

When I run it successfully I am logged on as DOMAIN\DomainAdmin. That account is a domain admin. This appears to be a permission issue however the package is being ran under the same credentials both ways. Any help would be appreciated.

sql-server-generalsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I just updated the package to have the file created on a local drive and it works correctly through the job. Clearly this is a permissions issue. Any help would be great.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

on the "Y" drive which is mapped to another server

Mapped = so it's a NAS share mapped as drive in your profile; SQL Server service account don't have a profile and even, it's not yours and so drive Y don't exists for SQL Server engine.
Use an UNC path instead and ensure that SQL Server service account do have access permissions on the NAS share.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @scottHanebutt-4376 ,

1.Please check if the DOMAIN\DomainAdmin has full permission to access to the file "Y:\FILE5945031821.txt".

2.May I know if you can create a text file on the "Y" drive manually?

3.Please Create a SQL Server Agent Proxy to use the user in design interface to execute ssis package.
A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL Server Agent subsystem, and then assign that proxy to the job step.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.