The operation cannot be started by an account that uses SQL Server Authentication. SSIS Package

Question

I have a remote access in SSMS, when I try to execute an SSIS package I get, the operation can not be started by an account that uses SQL Server Authentication

How to fixed it?

Note: I can not use Windows Authentication remotely.

Answer 1

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 (as you have discovered).

As a consultant, I ran into issues where I'd use my corporate laptop plugged into a client's network. In that case, my account sdc\billinkc would not exist in the client domain. Instead, their servers would expect client\bfellows credentials to be presented. The work around is to use RunAs. Alternate reference

From my answer on deploying ispac to foreign domain...

I created a suite of batch files that launch every process I need. They take the form of

runas /netonly:client\bfellows "C:\windows\system32\cmd.exe"

I have one for a command prompt (above), Visual Studio, SSMS, PowerShell, PowerShell ISE and some other specialty apps that need to work with their domain.

Using the runas approach, I've been able to deploy packages using all of the above methods (as well as deploying directly from a Visual Studio instance being run with foreign credentials).

You'd want to use ssms.exe and the correct install path to launch management studio and then SSIS packages would run.

Approach #2

If you don't have an account in the foreign domain and the only thing they are willing to do is create a sql login, then they will also need to

• Create a Credential
• Create a Proxy
• Create a SQL Agent job that uses the proxy to run the SSIS package
• Grant the sql login the rights to run the job

This will allow you to run an SSIS package in a manner that is allowed as well as present domain credentials to the required resources.

Answer 2

For future Googlers, adding a Windows Authentication login as sysadmin and turning on "Impersonate Any Login" on the SQL Auth login (also sysadmin) as described in this StackOverflow answer allowed us to use "EXECUTE AS LOGIN = 'server\winuser'" and successfully execute the package via the SQL Auth account.

You just have to be sure you are connected to the SSISDB database when executing the commands.