What are the challenges you face when working across database platforms? Take the survey
Options

SSIS Deployment Conundrum

Problem:  Cannot deploy SSIS packages to AZURE VM/SQL Server Instance (or execute them via SSDT/VS) from a local laptop using a SQL Login. FYI:  Project Deployment Model is used.

Background

We moved our development SQL Server instance to an AZURE VM, and would like to avoid giving every developer RDP permissions access to the server.

It seems that SSIS Projects can be only be deployed via a WINDOWS AD account while logged onto a server using an ISPAC file, or opening an SSIS Project on the server itself and deploying from there.

See error specifics below but my question is this: is there a work-around so that we can connect to AZURE SQL SERVER instance using SQL LOGIN’s and deploy/run package from our development laptop, or must every developer have RDP/Windows AD access to the new development server?

When we try to deploy SSIS Projects to the Integration Services Catalog from SSDT or VS, using a SQL Login we receive the following error message:

------------------------------

TITLE: SQL Server Integration Services

------------------------------

Operation 'Create' on object 'CatalogFolder[@Name='DNA_ODS']' failed during execution. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.17243.0+((SSMS_Rel).180502-2324)&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

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

------------------------------

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Integrated Authentication.

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 SQL Server&ProdVer=14.00.3281&EvtSrc=MSSQLServer&EvtID=27123&LinkId=20476

------------------------------

Also tried to change connection type to Windows Authentication (in my case have more than adequate permissions) and received the following error message:

------------------------------

TITLE: SQL Server Integration Services

------------------------------

Failed to connect to server 172.21.2.5. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

ADDITIONAL INFORMATION:

The target principal name is incorrect.  Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

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

------------------------------

Thanks, in advance for any suggestions, insights, etc. 
Tagged:

Answers

Sign In or Register to comment.