none
launching a dtsx package from a stored procedure RRS feed

  • Question

  • hi all,

    I have this stored procedure:

    BEGIN TRY
    
    	DECLARE @execution_id bigint
    	DECLARE @refID int
    	
    	EXEC [SSISDB].[catalog].[create_execution] 
    		@package_name=N'myTextFileImport.dtsx',
    		@folder_name=N'myFolder',
    		@project_name=N'TextFileImport',
      		@use32bitruntime=False,
    		@reference_id=5,
    		@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
    
    END TRY

    When I run this in the SS Management Studio being logged with the domain account and Windows Authentication, the stored procedure runs OK and the package loads a text file to a table in a database.

    However, when I call the same stored procedure from a web page through a dedicated database user I get this error message:

    System.Data.SqlClient.SqlException (0x80131904): Error executing: Error loading: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

    I have not seen an error message saying I cannot use a certain authentication before …

    Any ideas what is happening here?

    Thanks,

    elsvieta 

    Friday, September 20, 2019 12:21 PM

All replies

  • Hi elsvieta,

    You need a proxy.

    One easy way I can think of is to create an Agent job that has this package run using a proxy and call this job from within your application.


    Arthur

    MyBlog


    Twitter

    Friday, September 20, 2019 2:46 PM
    Moderator
  • thanks Arthur,

    but this won't do. I tried the Agent job solution but I am not comfortable with the roles I have to assign to my web user. That's why I am trying to dump everything into a stored procedure. Admittedly, I still have to grant execute permissions for the procs but somehow I feel better about this.

    Regards,

    elsvieta

    Saturday, September 21, 2019 12:44 AM
  • Hi elsvieta,

    We could create a new account in SQL server that uses Windows Authentication to start the operation.

    Please refer to Creating a user and granting permissions.

    Best Regards,

    Mona




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 23, 2019 7:32 AM