SSIS Deployment Error while deploying packages into SQL Server

Question

I was Trying to deploy SSIS package from Visual studio 2019 into MS SQL Server 2016, I have been facing deployment error as shown below:

"“A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal": System.ComponentModel.Win32Exception: A required privilege is not held by the client.”

After few hours of debugging found that the error is actually from SQL Server. SQL Server is unable to run the stored procedure from SSISDB "[catalog].[check_schema_version] ".

Hence we realized that there are certain privileges for the current service account on which SQL Server is running. Hence we have created a new local admin account and provided all privileges.

• As per Microsoft suggestion, we have added the SQl server service account & SQL server integration service account in the below Configs : o Edited DCOM config properties and provided granted the Local Launch and Local Activation permissions for the below component services

 Microsoft SQL Server Integration Services 11.0, Microsoft SQL Server Integration Services 12.0 and Microsoft SQL Server Integration Services 13.0

o Further we extended permissions for both the service accounts as below :

   Log on as a service .
   Permission to write to application event log.
   Impersonate a client after authentication.
   Adjust memory quotas for a process

Below are the two group policies yet to be added :

   Bypass traverse checking 
   Replace a process-level token

I would like to know if this resolves the issue and please suggest anything that we are missing here to resolve the issue.

Aedile

If you create a "hello world" level of an SSIS project and similar level of package, deploy it to the server, does it work? This can help eliminate whether it's specific to the project you've deployed or something more general. Are there other SSIS projects deployed and running on this server? Does this new project that is failing to deploy specifically target SQL Server 2016? The default would be to target 2019. – Disharmonious

Billink, thanks checking this , What i was trying to deploy is a simple SSIS package. There no other projects deployed on the server. this is the first time we are trying to deploy an SSIS package on to the SQL Server. The SSDT tools are matching to the SQL Server installed. And as explained, there is the same while running the check schema version stored procedure in the sql server – Aedile

Answer 1

After adding the New service account to the listed group policies, the deployment got succeeded.

Aedile

in windows server 2016 I am not able to edit the policy "Replace a process-level token", how can I fix it? – Disinterest

Answer 2

I ran into this problem after a new Security Policy was put in place that broke WinRM (disabled "Allow remote server management through WinRM"). Even after rolling back the change I could not deploy SSIS packages. I finally fixed the problem by running the "Repair" SQL option from the installation disk and then restarting the server. After that deployments worked as normal.

Tamelatameless

Useful answer on the matter, but i suggest you to add more context. Especially the error message you got initially from SSIS deployment with a broken WinRM. You should also precise if the policy and its rollback bring only the change for WinRM (and only that). – Boardinghouse

Answer 3

At my company we experienced the following error attempting to deploy an SSIS Package to SQL 2019 running on Windows Server 2019. We tried deploying with Visual Studio 2019 and SSMS but received the same error. Following the prior post, we ran the SQL Repair Installation Option and it fixed the issue.

Realy appreciate this site and it's contributors!

Snowshoe