debugsqlMinor
The operation cannot be started by an account that uses SQL Server Authentication. SSIS Package
Viewed 0 times
cannotthestartedpackagesqlssisaccountusesoperationauthentication
Problem
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.
How to fixed it?
Note: I can not use Windows Authentication remotely.
Solution
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
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
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.
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.
Code Snippets
runas /netonly:client\bfellows "C:\windows\system32\cmd.exe"Context
StackExchange Database Administrators Q#198914, answer score: 9
Revisions (0)
No revisions yet.