CODEWITHSUNDEEP
sqlsql-serverdeploymentssisservice-accounts
Phaneendar
Phaneendar

Reputation: 71

SSIS Deployment Error while deploying packages into SQL Server

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.

Upvotes: 6

Views: 8926

Answers (4)

Nahid Monfared
Nahid Monfared

Reputation: 1

One of the root cause behind this issue is the presence of NT SERVICE\MSSQLSERVER and NT SERVICE\SQLSERVERAGENT under Local Policies\User Rights Assignments \ Replace a process level Token

As it is said above running SQL server repair will register these accounts properly.

Upvotes: 0

Todd T
Todd T

Reputation: 335

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.

ETL Package Deployment Error Message

Realy appreciate this site and it's contributors!

Upvotes: 0

James K.
James K.

Reputation: 11

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.

Upvotes: 1

Phaneendar
Phaneendar

Reputation: 71

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

Upvotes: 1

Related Questions