Deploy SSIS package requires Windows Authentication?

Question

I developed and tested my package locally and now I wish to deploy to an Azure VM Installed SQL instance. The deployment fails:

TITLE: SQL Server Integration Services

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Integrated Authentication. (Microsoft SQL Server, Error: 27123)

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

---

BUTTONS:

OK

This is the first time I've ever come across something the SA user can't do on a SQL Server instance! Can someone shed logic on this fact and explain? I've tried running VS as the admin for the VM. This fails.

I'm not a sysadmin and do not have both machines in the same domain but control both of them.

Is it true that SSIS packages can only be deployed between machines in the same domain? Why does the deployment wizard ask me if I want to use SQL Server authentication if deploying requires integrated?

Is it time to move to data factory? What is the future of SSIS anyway!

Answer 1

Correct. Deploying projects to the SSISDB requires a windows account.

SQL Server accounts don't work as the CLR methods in the SSISDB use impersonation and such.

RunAs will allow you to deploy packages to SQL Server instances that aren't on your domain

Can't speak to the deployment wizard giving you that choice.

ADF is a different mindset than SSIS (ELT vs ETL). It's also a different costing model so it's probably too broad a question to address on SO.