Visual studio 2010 Integration Services Project can't deploy to SSISDB
I have Visual studio 2010 Integration Services Project
After I build it, I try to deploy it to SQL Server 2012 and the new SSISDB but I get a very strange error message (and yes Im sysadmin on the SQL Server and CLRIntegrationEnabled is True on the server and I can run the package from inside
Visual studio). I cant figure out what privilege that is missing.
declare
@p4 bigint
exec
[SSISDB].[catalog].[deploy_project]
@folder_name=N'TripManager',@project_name=N'TripManager',@project_stream=0x504B030414000000080027A
lot of numbersHERE,@operation_id=@p4
output
select
@p4
Msg 6522,Level
16, State 1,
Proceduredeploy_project_internal,Line 0
A.NET
Framework error
occurredduring execution
of user-definedroutine
or aggregate
"deploy_project_internal":
System.ComponentModel.Win32Exception:A
required privilege
is notheld
by the
client
System.ComponentModel.Win32Exception:
at Microsoft.SqlServer.IntegrationServices.Server.ISServerProcess.StartProcess(BooleanbSuspendThread)
at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectInternal(SqlInt64deployId,
SqlInt64 versionId,SqlInt64
projectId,SqlString
projectName)
May 23rd, 2012 7:39am
Do you get the same issue if you "Deploy Project" from inside the catalog in SSMS?
Chuck
Free Windows Admin Tool Kit Click here and download it now
May 23rd, 2012 8:27am
Yes.
May 23rd, 2012 8:31am
Might try explicitly adding the ssis_admin role to your user account/group you are logging in with in SSISDB
USE [SSISDB]
GO
ALTER ROLE [ssis_admin] ADD MEMBER [YourAccount]
GO
Chuck
Free Windows Admin Tool Kit Click here and download it now
May 23rd, 2012 8:37am
Same problem both from VS and SSMS. You can see that my login got the ssis_admin role.
May 23rd, 2012 8:56am
Hmm - What are your logins permissions on the server (OS) itself? Maybe you are getting denied permissions to an assembly?
Chuck
Free Windows Admin Tool Kit Click here and download it now
May 23rd, 2012 9:08am
Hi Peter,
these are the permissions you need to execute [SSISDB].[catalog].[deploy_project]
Membership to the ssis_admin database role
Membership to the sysadmin server role
CREATE_OBJECTS permissions on the folder to deploy a new project or MODIFY permissions on the project to update a project
Cheers
Rgis
May 23rd, 2012 9:44am
Thats for all suggestions, I found the answer.
Grant Permissions to Integration Services Service
In previous versions of SQL Server, by default when you installed SQL Server all users in the Users group had access to the Integration Services service. When you install the current release of SQL Server, users do not have access to the Integration Services
service. The service is secure by default. After SQL Server is installed, the administrator must grant access to the service.
To grant access to the Integration Services service
Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
Right-click Microsoft SQL Server Integration Services 11.0, and then click
Properties.
On the Security tab, click Edit in the Launch and Activation Permissions area.
Add users and assign appropriate permissions, and then click Ok.
Repeat steps 4 - 5 for Access Permissions.
Restart SQL Server Management Studio.
Restart the Integration Services Service.
Free Windows Admin Tool Kit Click here and download it now
May 24th, 2012 4:12am
Any further help on this would be hugely appreciated!
The functionality looks so inviting, but I keep getting the same error as Peter.
I have tried all suggentions above to no avail.
Strangely, last week when I first set my Catalogs up, I was able to Deploy my Project without any trouble.
Since arriving back at work today - nothing.
May 27th, 2012 10:18pm
Same Problem here. But your solution does not work for us.. Any other ideas???
Free Windows Admin Tool Kit Click here and download it now
June 19th, 2012 10:44am
Eventually, I ran the SQL 2012 Repair against my Instance and Voila - all working fine now.
I suspect that there were some (non intrusive??) LAN Changes at the time.
June 21st, 2012 6:59pm
I ran SQL 2012 Repair and everything worked fine but after one week when I tried deploying I got the same error again.
Now, should we Repair the instance everytime before deploying the package?
.Net
Free Windows Admin Tool Kit Click here and download it now
August 10th, 2012 12:37am