Execute SSIS package with proxy account from command line

Question

I need to run SSIS2016 package from older SSIS (another server) because of extending legacy system. The package runs fine from SQL Agent, but I am unable to run it using tsql or DTEXEC. The problem is that package is unable to access network even when run in elevated cmd (under sysadmin)

1. I am able to access network drive from account I am running package (admin)
2. I know that package runs fine when it is run from Sql agent under same account.
3. The package runs fine when file is saved to local temp folder (both DTEXEC and TSQL)

4. The package fails to save file to network when run using execute is SSMS, TSQL or DTEXEC.

   DTEXEC /ISSERVER "\SSISDB\XXXX\Exports\package.dtsx" /SERVER "." /Par
   "$Package::FilePath(String)";"C:\TEMP"
   

From all what I have read on internet, it seems that the only way to run SSIS2016 package is by using SQL Agent and jobs.

I am surprised by the fact that DTEXEC ignores the security context of account which execute the command line. Is there a way to force it to use proxy account?

I find it hard to believe, that only way to run package with permission to network is to use Sql agent and jobs.

Thank you. p.s. Here is good article about running packages, but it completely omits network access problem.

EDIT 2019-06-10

I applied MSSQL2016 CU7 and restarted the server. After this,

• Iam able to save file to network using DTEXEC command ran on local server (logged as sysadmin)
• Iam able to save file to network using sp ran from local server (logged as sysadmin)
• I cant save file to network drive when logged as sysadmin on different machine. The error is

Flat File Destination failed the pre-execute phase and returned error code 0xC020200E.

Cannot open the datafile ..

there is also warning "Access is denied" in data flow which should save the file.

Source code of stored procedure

DECLARE @execution_id BIGINT;
EXEC [SSISDB].[catalog].[create_execution]
    @package_name = N'package.dtsx',
    @execution_id = @execution_id OUTPUT,
    @folder_name = N'XXXX',
    @project_name = N'Exports',
    @use32bitruntime = False,
    @reference_id = NULL;
SELECT @execution_id;
DECLARE @var0 SQL_VARIANT = N'\\NETWORK\PATH\Export\test_files';
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id,
    @object_type = 30,
    @parameter_name = N'FilePath',
    @parameter_value = @var0;
DECLARE @var1 SMALLINT = 1;
EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id,
    @object_type = 50,
    @parameter_name = N'LOGGING_LEVEL',
    @parameter_value = @var1;
EXEC [SSISDB].[catalog].[start_execution]
    @execution_id;

I also added System::UserName to log on start of package and I see correct account. I assume, that problem might be in double hop as @Piotr mentions. Iam digging the fileserver logs to see.

EDIT2 2019-06-10

Ok. After much dancing around I can narrow problem down to delegation executing user rights to CIFS.

What i did

• Setup a share on dev computer
• Create package with scriptask which tries to create test file and get security context of calling user WindowsIdentity.GetCurrent().Name
• Then I ran stored procedure with code above in scenario that always ended by access denied.

Until I enabled anonymous access (using this article), the package was not able to save file.

AFAIK we have enabled only MSSQLSvc kerberos delegation.

Closure:

The problem is caused by behaviour of SSIS catalog, when called from TSQL, which gets users security context, but in order to work this properly, when accessing netwrok shares, you need to setup Kerberos delegation for CIFS also.

I was not able to do this in our environment, so I reverted back to xp_cmdshell.

Thanks to all for support.