7

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

enter image description here

enter image description here

How to fixed it?

Note: I can not use Windows Authentication remotely.

2
  • On that session in SSMS, you will need to login using Windows Authentication. You can read more in the documentation.
    – Rigerta
    Feb 27, 2018 at 14:30
  • I can not use Windows Authentication remotely. Feb 27, 2018 at 14:35

2 Answers 2

9

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.

1
  • Can you elaborate on Approach#2.
    – H20rider
    Feb 9, 2022 at 19:21
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.