CODEWITHSUNDEEP
reporting-servicesssis-2012
Julius Alexander
Julius Alexander

Reputation: 501

The operation cannot be started by an account that uses SQL Server Authentication error When running ssis from stored proc in SSRS

I am getting 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

I have a ssrs report which uses a dataset derived from a stored procedure. The stored procedure first creates an execution and executes a ssis package before drawing the data. In visual studio I can preview the report fine. The data source uses integrated security (though I wish I could pass a sql server account). When I upload the report to SSRS it gives me the error about using an account with windows auth. I checked the datasource on the server and it is set to use windows auth. I'm not sure what I am doing wrong or how to fix this.

my sql server is 2012 r2 (ssrs is 2008)

Upvotes: 4

Views: 23380

Answers (6)

hedayat sqz
hedayat sqz

Reputation: 3

enter image description here

Change the owner to administrator. Here are the steps I took to solve this issue:

Right-Click on the SSISDB database and select properties

Click on Files under the Select a page

Under the Owner, but just below the Database Name on the right-hand pane, select [pc name]/Administrator as the owner.

Upvotes: 0

Karel-Jan Misseghers
Karel-Jan Misseghers

Reputation: 807

The issue is that you cannot execute SSIS packages which are stored on your SSISDB Catalog using a SQL Server account.

This is why changing the user which is attempting to start the SSIS package to a 'Windows Authentication user' works.

A similar issue can be found here;

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bbd2b556-8f68-4e04-94d1-754127886361/unable-to-execute-ssis-package-from-sql-server-login?forum=sqlintegrationservices

Upvotes: 6

Jon
Jon

Reputation: 23

schedule the SSIS package as a job with windows authentication, then call the job itself using

USE msdb;

GO

EXEC dbo.sp_start_job N'NAME OF JOB'; GO

works for me

Upvotes: 1

Viktor Lushchyk
Viktor Lushchyk

Reputation: 11

Integrated Security = SSPI;

Ignores UserId and Passwords and uses Windows Authentification :D

Upvotes: 1

farnia emami
farnia emami

Reputation: 1

Trusted_Connection = False;
Password = ***;
User ID = ***;
Data Source = localhost;
Initial Catalog = master;
Integrated Security = SSPI;

This connection string worked for me!

Upvotes: 0

Julius Alexander
Julius Alexander

Reputation: 501

Apparently putting a windows login in the data source worked, couldn't get it to go in visual studio but updating the datasource on ssrs did the trick.

Upvotes: 0

Related Questions