Search code examples
sqlsql-serverssisssis-2012

SSIS Package What credential SSIS is running?


I have package to access an excel file .xls. This file is located on network path.
The packaged is called from store with pass use32bitruntime = true. The store is called from C# code.
If the file is local file => the package run successfully.
If the file is network path => the package run failed.

Checking for network path:
I can access network path and even open file manually is successful.

So my question is what actually credential SSIS package running?
If it runs under my account that logg-in to SQL and call the store, it should be access the file. But it's not. It turns out another credential is substituted my own.
After search a quite long time, I can't find the answer.

Any help on this is very appreciated.

This is a store to execute SSIS

ALTER PROCEDURE [dbo].[execute_ssis_package]
 @folder_name varchar(100) 
 ,@project_name varchar(100)
 ,@package_name varchar(300)
 --,@RunAccount varchar(300) OUTPUT
 ,@output_execution_id BIGINT OUTPUT
AS
BEGIN

    SET NOCOUNT ON;

 DECLARE @execution_id BIGINT
 EXEC ssisdb.catalog.create_execution @folder_name,
           @project_name,
           @package_name,
           @use32bitruntime = True,
           @reference_id = Null,
           @execution_id = @execution_id OUTPUT

 Select @execution_id

 DECLARE @var0 smallint = 1
 EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
      @execution_id,  
      @object_type=50, 
      @parameter_name=N'LOGGING_LEVEL', 
      @parameter_value=@var0

 EXEC ssisdb.catalog.start_execution @execution_id
 SET  @output_execution_id = @execution_id

Solution

  • The credential that runs the SSIS package:

    If run from SQL Agent, it will run as Agent user running the Windows service.

    If run from SQL Agent via proxy credentials, it runs under that credentials.

    If run from Windows scheduler via dtexec, it runs under the user as defined in the scheduler.

    In your case, you are using a stored proc, who calls that?