SSIS Deployment Conundrum
Michael_R_Harvey
Posts: 1 New member
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
------------------------------
Answers
We've moved this to a more appropriate forum for more appropriate context and visibility to the community.
This doesn't look related to any of our tools, but I have seen the message under "ADDITIONAL INFORMATION" before and this page https://documentation.red-gate.com/sm/troubleshooting/error-messages/sql-monitor-connection-error-cannot-generate-sspi-context in the SQL Monitor documentation may help.
If it turns out you are having an issue with one of our tools please do let us know!
Kind regards,
Alex
Have you visited our Help Center?