sql-serverazure-devopsssiscontinuous-deploymenton-premises-instances

DevOps Deploy SISS with a Deployment group. Permissions

I am trying to use DevOps to Deploy SSIS on to an On-Prem SQL Server instance. There is a Deployment Group and the username on that I cannot change. The agent running is the serverName#, this I cannot change. I have created the artifact and with the use of PowerShell I have updated the server, so now it is ready to deploy.

However I am coming up with the error.
----------------------------------Task Inputs---------------------------------
Source path                       : C:\azagent\..\_work\..\.\_ProjectName_SSIS
\drop\ProjectName.ispac
Destination type                  : SSISDB
Destination server                : SERVERNAME
Destination path                  : /SSISDB/FOLDER/ProjectName
Project Password                  : 
Authentication type               : Windows Authentication
Connection string suffix          : Data Source=SERVERNAME;Initial Catalog=SSISDB;Integrated Security=True;
Overwrite existing projects or SSISDeploymentManifest files of same name : yes
Continue deployment when error occurs                                    : no
------------------------------ispac to deploy---------------------------------
C:\azagent\..\_work\..\.\_ProjectName_SSIS\drop\ProjectName.ispac

Connect to 'ServerName' successfully. [error] Failed to create the catalog folder 'FOLDER': Operation 'Create' on object 'CatalogFolder[@Name='FOLDER']' failed during execution.

Deploy failed

This failed deployment is using Authentication Type: Windows Authentication. I'm guessing from the error, this is permissions for SSISDB, as stated the agent is SERVER#, which is not part of the AAD logins, and does not have permissions. I say guessing as this works fine deploying databases on the same server.

It I use Authentication Type: SQL Server Authentication. The error is

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

However here I do get the acknowledgement

The project 'ProjectName' already exists in the catalog folder 'FOLDER'. Will overwrite it.

I have also tried Authentication Type: Active Directory - Password. I get the very odd error.

Failed to connect to the SQL Server 'ServerName': Cannot use 'Authentication' with 'Integrated Security'.

I have not used Active Directory - Integrated, as the error message to the last puts me off. This doesn't have any requirements to fill out. However there is another box, this has the Connection string suffix.

I am using this to, this is populated with

Data Source=ServerName;Initial Catalog=SSISDB;Integrated Security=True;

Has anybody else come across this dilemma? If so, any help would be gratefully accepted.

Kind regards.

Solution

  • Since you are deploying with a deployment group(local agent) on On-Prem SQL Server instance, as per the task doc, you should use Windows Authentication.

    enter image description here

    As per the error with Windows Atheization in SSIS Deploy task below, the server is connected successfully.

    Connect to 'ServerName' successfully. [error] Failed to create the catalog folder 'FOLDER': Operation 'Create' on object 'CatalogFolder[@Name='FOLDER']' failed during execution.

    Please add a powershell task, with whoami to check which account the agent is running.

    enter image description here

    Make sure the account is a windows authentication type, the account should have permission to the SQL server. If it's the correct account, reconfigure the deployment group agent with correct account(eg：machine administrator account).

    enter image description here

    To narrow down the issue, directly go to the deployment group machine ,use SSMS to connect to the SQL database via the account above, try to create the catalog for a check:

    enter image description here

    To create the catalog, make sure:

    1.During the SQL server installation process, on the Feature Selection page, make sure to select Integration Services under Shared Features, so that you are able to create the catalog SSISDB.

    2.If any permission error reported on folder creation, add the window authentication account related permission. try to give the windows account sysadmin role on sql server.

    If you are able to create catalog folder on SSMS, the permission should be fine now.

    Rerun the pipeline task, my result below:

    enter image description here