0

I have a SSIS package that I want to run in SQL Server Agent T-SQL job using Stored Procedure since I have a permission issue described in my other post:

Using Proxy to give SQL Server Agent windows credential to access to azure db not working (still not solved)

I created a test case described as below:

1.SSIS package to run: Insert a row into an Azure database

2.Script to execute the package:

/* Create the execution object */

DECLARE @execution_id BIGINT

EXEC [SSISDB].[catalog].[create_execution] 

    @package_name = N'Package.dtsx'

    , @project_name = N'test'

    , @folder_name = N'test'

    , @use32bitruntime = False

    , @reference_id = NULL

    , @execution_id = @execution_id OUTPUT



/* System parameters */

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 

    @execution_id

    , @object_type = 50                     -- System parameter

    , @parameter_name = N'SYNCHRONIZED'

    , @parameter_value = 1



/* Execute the package */

EXEC [SSISDB].[catalog].[start_execution] @execution_id



/* Check package status, and fail script if the package failed

IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id)

RAISERROR('The package failed. Check the SSIS catalog logs for more information', 16, 1) 

PS: There is no problem with this code. It was successfully executed when run it in a query.

3.Create a job

Owner: DOMAIN\USER_NAME

Step (only one step):

Type: T-SQL


3.1 Database: master

    Advanced --> run as user: blank

    Error message:

        "Executed as user: DOMAIN\LAPTOP-4I64GKE3$. The package failed. Check the SSIS catalog logs   
         for more information [SQLSTATE 42000] (Error 50000).  The step failed"

3.2 Database: master

    Advanced --> run as user: dbo

    Error message:

        "Executed as user: dbo. The server principal "sa" is not able to access the database 
         "SSISDB" under the current security context. [SQLSTATE 08004] (Error 916).  The step 
         failed."

3.3 Database: SSISDB

    Advanced --> run as user: blank

    Error message:

        "Executed as user: DOMAIN\LAPTOP-4I64GKE3$. The package failed. Check the SSIS catalog logs 
         for more information [SQLSTATE 42000] (Error 50000).  The step failed"

3.4 Database: SSISDB

    Advanced --> run as user: dbo

    Error message:

        "Executed as user: dbo. The operation cannot be started by an account that uses SQL Server 
         Authentication. 
         Start the operation with an account that uses Integrated Authentication. 
         [SQLSTATE 42000] (Error 27123)  The operation cannot be started by an account that uses SQL 
         Server Authentication.
         Start the operation with an account that uses Integrated Authentication. 
         [SQLSTATE 42000] (Error 27123)  The operation cannot be started by an account that uses SQL 
         Server Authentication. 
         Start the operation with an account that uses Integrated Authentication. 
         [SQLSTATE 42000] (Error 27123).  The step failed."

End of test case.

It seems that only 3.4 approach gets a little bit closer to my goal, but not sure what to do next.

Should I try any other combinations or should I create a user has windows authentication?

Any idea helps

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • Can you connect to the Azure sql database using a sql login? If so, that would work around the run as problem. – Mark Wojciechowicz Apr 17 '20 at 20:55
  • yeah... I agree with you, but on our case, my client is not the owner of the azure db either. It is shared and managed by other department of their company.. – Shannon Lin Apr 17 '20 at 21:32

0 Answers0