0

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:

  1. EXEC [SSISDB].[catalog].[create_execution] with package details
  2. EXEC [SSISDB].[catalog].[set_execution_parameter_value] where I am setting package parameters
  3. 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.

Spoorthi Tejasvi
  • 11
  • 5
  • 2
    SQL Statements don't work differently because you called them from a different application. You need to add more detail here other than "I get an authentication error." – Thom A Sep 24 '21 at 12:10
  • sorry for that. I have added all the details to the question now. Also, I noticed now that when I test connection it succeeds but when I am trying to build TSQL commands for catalog SP calls it is showing as login failed. Please guide me what is that I am doing incorrectly. – Spoorthi Tejasvi Sep 24 '21 at 15:45
  • Can you add an image of the SSIS data flow implementation @SpoorthiTejasvi – Gudwlk Sep 25 '21 at 09:54
  • 1
    I found the solution through other sources. I simply changed from sql server authentication on connection to windows authentication and deployed the package to SSISDB and tested there. It worked fine. Locally I was not able to test as my windows account did not have enough permission on server. – Spoorthi Tejasvi Sep 27 '21 at 10:54

0 Answers0