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