5

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.

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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