Hi guys,

My Environment:

• SQL Server 2014 Standard Edition
• SQL Server Installed on a hosted environment (different domain than our company domain)
• I have to use DNV\MyUserName when RDP in order to access the hosted environment. 
• Our Company Domain is IHM\MyUserName
• Ultimate Objective is to Execute SSIS package from a .NET application (I'm currently using MS LightSwitch)

I have the SSIS package deployed to SSISDB Catalog, I can run it locally without any problem.

Here is the script:

declare @output_execution_id bigint
exec [dbo].[CopyNewDataToLegacy] @output_execution_id output 
print @output_execution_id

If I take the same script and run it from my local machine domain(IHM\MyUserName)

I get the following error

Msg 27123, Level 16, State 1, Procedure create_execution, Line 38
The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
Msg 27123, Level 16, State 1, Procedure start_execution, Line 32
The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

It's very clear to me that I can't execute the package without Windows Authentication.

1. Is there a workaround to execute SSIS package with SQL Server Authentication?
2. We have a secondary domain control in the hosted environment company but I can't see our IHM\Users from DNV Environment (I browsed Entire Directory from (SSMS >>> Logins >>> New Login >>> Click Search Button >>> Locations) I only see users under DNV\Users (which is not our domain)
3. I'm sure we are not the only company having this kind of issues, so I'm wondering how other companies who use hosting environment are working around this limitation.

I appreciate all the help.

Than

Given it is "different domain than our company domain" how did you point

exec [dbo].[CopyNewDataToLegacy] @output_execution_id output

to run on the remote machine?

it is not clear how you you reference the SQL account, and what is inside the stored procedure.

To me it looks like you cannot run the package unless the remote SQL server is linked.

this?

https://joethebusinessintelligenceguy.wordpress.com/2013/08/14/ssis-2012-using-sql-authentication-with-dont-save-sensitive-successfully/

Hi Codernater,

In order to execute SSIS packages, we can execute the [SSISDB].[catalog].[start_execution] stored procedures using a Windows Authentication Account rather than SQL Server Authentication Account, otherwise we will be at the receiving end of 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.

As per my understanding, this requirement makes sense considering SSIS executes outside the context of SQL Server and as part of an OS process, which means that a Windows account is necessary in order to execute a package. So we must use a Windows Authentication Account to execute the stored procedure.

As to your second question about adding login, please post the question in the SQL Server Security forums at https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlsecurity. It is appropriate and more experts will assist you.

Thanks,
Katherine Xiong

I tried this approach https://joethebusinessintelligenceguy.wordpress.com/2013/08/14/ssis-2012-using-sql-authentication-with-dont-save-sensitive-successfully/

It gave me the same error when I tried to change the SSISDB Catalog configuration for my project

Here is the error

===================================

Operation 'Alter' on object 'ProjectInfo[@Name='IHMSSIS']' failed during execution. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.4100.1+((SQL14_PCU_main).150420-1653)&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.CRUDImplWorker(String operationName, SfcObjectState requiredState, SfcDependencyAction depAction, SfcObjectState finalState, Object extraParam)
   at Microsoft.SqlServer.Management.IntegrationServices.ProjectInfo.Alter()
   at Microsoft.SqlServer.IntegrationServices.UITasks.PropertiesTask.Perform(ITaskExecutionContext taskExecutionContext)
   at Microsoft.SqlServer.Management.TaskForms.TaskExecutionManager.ExecuteTaskSequence(ISfcScriptCollector collector)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Sdk.Sfc.SfcTSqlExecutor.Execute(ServerConnection connection, String script, ExecutionMode mode, ExecutionTypes type)
   at Microsoft.SqlServer.Management.Sdk.Sfc.SfcTSqlExecutionEngine.Microsoft.SqlServer.Management.Sdk.Sfc.ISfcExecutionEngine.Execute(ISfcScript script)
   at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.CRUDImplWorker(String operationName, SfcObjectState requiredState, SfcDependencyAction depAction, SfcObjectState finalState, Object extraParam)

===================================

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.4100&EvtSrc=MSSQLServer&EvtID=27123&LinkId=20476

------------------------------
Server Name: .
Error Number: 27123
Severity: 16
State: 1
Procedure: set_object_parameter_value
Line Number: 47


------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Thank you

Hi Katherine,

Because our server is hosted on a hosted environment (different domain than our company domain) I can't use "our" windows authentication because one-way trust is not allowed at the hosted environment. Therefore, I can't use Windows Authentication.

If Microsoft is enforcing windows authentication use for packages, why does it offer sql server authentication in the first place as another option to use in your package

Hi guys,

My Environment:

• SQL Server 2014 Standard Edition
• SQL Server Installed on a hosted environment (different domain than our company domain)
• I have to use DNV\MyUserName when RDP in order to access the hosted environment. 
• Our Company Domain is IHM\MyUserName
• Ultimate Objective is to Run SSIS package from our domain IHM

Currently I'm getting the following error

The operation cannot be

started byan account that uses SQL Server Authentication.Start the operation withan account that uses Windows Authentication.(.Net SqlClient Data Provider)

How can I run a SSIS package without Windows Authentication?

===================================

Operation 'Alter' on object 'ProjectInfo[@Name='IHMSSIS']' failed during execution. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.4100.1+((SQL14_PCU_main).150420-1653)&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.CRUDImplWorker(String operationName, SfcObjectState requiredState, SfcDependencyAction depAction, SfcObjectState finalState, Object extraParam)
   at Microsoft.SqlServer.Management.IntegrationServices.ProjectInfo.Alter()
   at Microsoft.SqlServer.IntegrationServices.UITasks.PropertiesTask.Perform(ITaskExecutionContext taskExecutionContext)
   at Microsoft.SqlServer.Management.TaskForms.TaskExecutionManager.ExecuteTaskSequence(ISfcScriptCollector collector)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
   at Microsoft.SqlServer.Management.Sdk.Sfc.SfcTSqlExecutor.Execute(ServerConnection connection, String script, ExecutionMode mode, ExecutionTypes type)
   at Microsoft.SqlServer.Management.Sdk.Sfc.SfcTSqlExecutionEngine.Microsoft.SqlServer.Management.Sdk.Sfc.ISfcExecutionEngine.Execute(ISfcScript script)
   at Microsoft.SqlServer.Management.Sdk.Sfc.SfcInstance.CRUDImplWorker(String operationName, SfcObjectState requiredState, SfcDependencyAction depAction, SfcObjectState finalState, Object extraParam)

===================================

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.4100&EvtSrc=MSSQLServer&EvtID=27123&LinkId=20476

------------------------------
Server Name: .
Error Number: 27123
Severity: 16
State: 1
Procedure: set_object_parameter_value
Line Number: 47


------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Thank you

Hi Codernater,

If I understand correctly, you are saying the two authentications in the Log on to the server group box of OLE DB Connection Manager. Please note that the account is used to connect to the database. While the error message means that you cannot execute the package stored at SSIS Catalog with SQL Server authentication. They are actually two levels. One for connect to database in the package, another for execute the package. We can log on to the server using only a Microsoft Windows user account to execute a package stored at SSIS Catalog. This is by design.

Thanks,
Katherine Xiong

Hi Katherine,

If this is by design what's the workaround?

How can I execute an SSIS package without being remotely or physically on the server. I want to execute the package from my application.

Thanks

Like katherine said, the issue is by design. and there is no workaround. You can submit a feedback at:https://connect.microsoft.com/SQLServer/

There has to be a way!!!! I don't expect that Microsoft designed this limitation.

Help me understand something here, how is it possible that some developers are able to run ssis packages from their web applications!!!! Where the SSIS package is located on a hosted server (outside the company's network)?

Hi Codernater,

You can run SSIS packages remotely (via web or not) and there are several ways.

But due to the inability to delegate rights across disconnected networks/domains you would need to bridge the handshake by some extra layer/plumbing code.

E.g. your web app triggering something that has the rights to run the SSIS jobs.

Think of say starting an Agent job on the machine that has SSIS installed, or running a procedure like Katherine mentioned above.