Azure Devops SSIS Deploy, cannot connect to on prem server

[theherbert-oneiroworks]

I have an SSIS project that I want to use Azure DevOps' CI/CD. Using SSIS build for the build pipeline, it works.

I have created an agent, installed and configured in the desired on-prem server. I create an SSIS Deploy for the release pipeline, using the agent. But I am getting an error, that it cannot connect to the server.

Log snippet:

2023-01-26T02:23:43.1916043Z ------------------------------ispac to deploy---------------------------------
2023-01-26T02:23:43.1964038Z C:\azagent\_work\r1\a\[xxx]\[xxx]\[xxx].ispac
2023-01-26T02:23:43.1972341Z ------------------------------------------------------------------------------
2023-01-26T02:23:43.2015150Z 
2023-01-26T02:23:43.3927735Z ##[error]01/26/2023 02:23:43 Failed to connect to the SQL Server '[xxx]': Failed to connect to server [xxx].
2023-01-26T02:23:43.3940302Z ##[error]01/26/2023 02:23:43 Failed to connect to server [xxx].
2023-01-26T02:23:43.4000635Z -----------------------------------Errors-------------------------------------
2023-01-26T02:23:43.4090460Z ##[error]Failed to connect to the SQL Server '[xxx]': Failed to connect to server [xxx].
2023-01-26T02:23:43.4097170Z 
2023-01-26T02:23:43.4126075Z ##[error]Failed to connect to server [xxx].
2023-01-26T02:23:43.4130942Z 
2023-01-26T02:23:43.4846671Z ##[section]Finishing: Deploy SSIS

I can see the ispac file in the server & I can run it manually, but it seems it can't when via Azure Devops.

Appreciate any help... please.

[#2]

@theherbert-oneiroworks You need to check what permissions the Azure DevOps self-hosted agent service has and what account is executing the service.  That account will need to have permissions to log into your SQL Server.

I'm guessing you're running the agent service using the NETWORK SERVICE credentials which probably won't work.

[#3]

@theherbert-oneiroworks 

Pretty match with permission issue, please check from there including role of accounts