I am trying to execute the SSIS package using TSQL commands (as a part of asynchronous execution testing) using Execute SQL task but I am getting error with related to server authentication and connection not being made.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "Declare @execution_id bigint EXEC [SSISDB].[catalo..." failed with the following error: "The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Integrated Authentication.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. The TSQL commands I used in Execute SQL task with connection made to db by creating OLEDB connection with sql server authentication:
- EXEC [SSISDB].[catalog].[create_execution] with package details
- EXEC [SSISDB].[catalog].[set_execution_parameter_value] where I am setting package parameters
- EXEC [SSISDB].[catalog].[start_execution] @execution_id starting the execution of package
Does this happen because I am trying to call SSISDB catalog stored procedures locally ie. from outside SSMS ? I also checked the users role on SSISDB and it has access to it. The TSQL commands work fine when executed from SSMS. Any leads would be appreciated. Thanks in advance.
Updated(Found the solution) : 2021-09-27
I found the solution through other sources. I simply changed from sql server authentication for db connections to windows authentication and deployed the package to SSISDB and tested there. It worked fine. Locally I was not able to test with windows authentication,as my windows account did not have enough permission to db server.