My name is Archana CM from Microsoft SQL Developer Support team, we support SQL Connectivity issue along with data access technologies and SSIS.
We had a scenario where one of my customer was facing an issue while deploying SSIS Project in SQL Server 2012. In this blog today I am discussing on what was the issue while deploying the SSIS project and how we could resolve the issue.
While Deploying the SSIS Project to SSIS 2012, my customer was having a trouble and he could see the below error message, he had tried to deploy on different machines but still the issue was same. The main problem was, my customer was not using the default SSIS Service account "NT SERVICE\MsDtsServer110" to run SSIS service but rather he was using one of the domain accounts.
Error Message while deploying the SSIS project was
A .NET Framework error occurred during execution of user-defined routine or aggregate
System.ComponentModel.Win32Exception: A required privilege is not held by the client
at Microsoft. SqlServer. IntegrationServices.Server.ISServerProcess.StartProcess(Soolean
at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectlnternal(SqlInt64 deployld,
SqIInt64 versionld, SqlInt64 projectld, SqlString projectName)
. (Microsoft SQL Server, Error: 6522)
From the above error message it’s very clear that issue is because of some permissions, and hence first suggestion was to add the user account under DCom.
1. Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
2. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
3. Right-click Microsoft SQL Server Integration Services 11.0, and then clickProperties.
4. On the Security tab, click Edit in the Launch and Activation Permissions area.
5. Add users and assign appropriate permissions, and then click Ok.
6. Repeat steps 4 - 5 for Access Permissions.
7. Restart SQL Server Management Studio.
8. Restart the Integration Services Service.
But in this case, these steps din’t help us to resolve the issue and more permission was required, we had to further dig deep to resolve it.
By default, for SSIS All rights are granted to the per-service SID for both default instance and named instance: NT SERVICE\MsDtsServer110. Integration Services does not have a separate process for a named instance.
Permission granted by SQL Server Setup to NT SERVICE\MsDtsServer110 are
Log on as a service (SeServiceLogonRight)
Permission to write to application event log.
Bypass traverse checking (SeChangeNotifyPrivilege)
Impersonate a client after authentication (SeImpersonatePrivilege)
In our case, my customer dint want to use the default account (NT SERVICE\MsDtsServer110) but he wanted to use the domain account as started earlier.
So here are the permissions we gave to the domain account under User Rights Assignment on SQL server machine.
Run-> Secpol.msc -> User Rights Assignment: Add the domain user to the below listed security privileges:
• Log on as a service (SeServiceLogonRight) : http://technet.microsoft.com/en-us/library/cc739424(v=ws.10).aspx
• Replace a process-level token (SeAssignPrimaryTokenPrivilege) : http://technet.microsoft.com/en-us/library/cc784623(v=ws.10).aspx
• Bypass traverse checking (SeChangeNotifyPrivilege) : http://technet.microsoft.com/en-us/library/cc739389(v=ws.10).aspx
• Adjust memory quotas for a process (SeIncreaseQuotaPrivilege) : http://technet.microsoft.com/en-us/library/cc736528(v=ws.10).aspx
• Permission to start SQL Writer
• Permission to read the Event Log service
• Permission to read the Remote Procedure Call service
Also we captured the process monitor trace (Download available at http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx ) , we could see that "isdeploymentwizard.exe" was having permission issue in below registry Keys. After adding the domain account under all the below registry key, we were able to resolve the issue completely.
Desired Access: Query Value, Set Value
Desired Access: Read/Write
Desired Access: All Access
The result of process monitor may defer based on the account with which SSIS service need to be executed and serve machines, run the tool on individual machine to provide the permission to the registry keys for the account with which SSIS service need to be executed.
Some of the Reference Links
Integration Services (SSIS) Projects
Deploy Projects to Integration Services Server
Comparison of Deployment of Projects and Packages
Author : Archana(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft
Sometimes, you dont need to setup anything and it works. But for this case, I have to go through all these steps by myself even we can submit a ticket to DBA team--How they can know all of these. If this is by-design, I have to say it sucks.
I'm still struck, thanks for this "nice feature".
Two days and still doesn't work.