Our Organisation has set up Azure DevOps and as part of a proposed CI/CD Pipeline we would like to deploy (via release pipeline) our SSIS and SQL changes from Dev To Test and subsequently onto Prod.
We currently have an Agent Pool installed and running on Test and Production clusters. This is available to us in the release pipeline.
The documentation regarding SSIS Deployment informs us that we must deploy using windows authentication.
Which is what we’re doing:
However, deployment is not working:
We can deploy SSIS packages from VS and SSMS. So, my credentials are fine.
We can connect with a specific SQL login but this gives an error that confirms the above table:
Digging at little deeper, we added a WHOAMI cmd process to the pipeline and this produces nt authority\network service. This surprised us as we expected to get the name of person running the process.
Given the details specified here (What permissions are needed for the Azure DevOps Deployment Group Agent?) ,we suspect this is the account running the Agent Pool. This question, along with the subsequent answer/links raises the salient security points about applying nt authority\network service to the agent pools/deployment groups.
We suspect that if we gave db_owner permission to nt authority\network service on the SSISDB DB then it might work. However, this is possibly as step too far in terms of security resilience.
My question is, can you (or has anybody be able to) deploy a ISPAC/DACPAC from DevOps as part of release pipeline utilising windows authentication, without giving db_owner permissions to nt authority\network service on the destination SQL instance?
Happy to field question and mods please feel free to amend accordingly.
We look forward to your responses.
Quick Edit. Error Messages in plain text:
05/12/2023 08:21:43 Failed to connect to the SQL Server 'REDACTED\SQLSERVER2016,52192': Failed to connect to server 'REDACTED\SQLSERVER2016,52192. 05/12/2023 08:21:43 Failed to connect to server 'REDACTED\SQLSERVER2016,52192. Failed to connect to the SQL Server 'REDACTED\SQLSERVER2016,52192': Failed to connect to server 'REDACTED\SQLSERVER2016,52192. Failed to connect to server 'REDACTED\SQLSERVER2016,52192.
05/05/2023 14:35:42 Deploy failed: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication. 05/05/2023 14:35:42 Deploy failed! Deploy failed: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication. Deploy failed!
