question

ajaymahato-0224 avatar image
0 Votes"
ajaymahato-0224 asked ZoeHui-MSFT answered

Executing an SSIS Task from a SQL Authentication Login

I am trying to execute a SSIS package deployed in the SSISDB. while using windows authentication the package executes without any problem but when I try executing it with a SQL server authentication user I get the following error.
"The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication."

Here is my code to execute to package.

   declare @parameterValue nvarchar(200)
   set @parameterValue='D:\DriverImpact\'
       declare @output_execution_id bigint
  declare @execution_id bigint

exec ssisdb.catalog.create_execution
@folder_name = 'DriverImpact'
,@project_name = 'Integration Services Project2'
,@package_name = 'Package1.dtsx'
,@execution_id = @execution_id output

EXEC [SSISDB].[catalog].[set_execution_parameter_value]
@object_type= 20
,@execution_id = @execution_id
, @parameter_name = N'MainFolder'
, @parameter_value = @parameterValue;

exec ssisdb.catalog.start_execution @execution_id
set @output_execution_id = @execution_id
select @output_execution_id as 'output'

sql-server-transact-sqlsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @ajaymahato-0224,

You cannot use a SQL Account to run an SSIS package in the Integration Services Catalog. There is logic within the CLR methods that are used to run the SSIS packages that reject non-windows authentication.

If you don't want to use windows authentication, you may refer below for details.

https://dba.stackexchange.com/questions/198914/the-operation-cannot-be-started-by-an-account-that-uses-sql-server-authenticatio

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

The error is clear. What is your question?

The simplest way to avoid this issue is to use a SQL Agent job run the SSIS package. Agent runs as Windows auth.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.