What permissions needed to connect to SQL Server Integration Services

Question

I need to allow a consultant to connect to SSIS on a SQL Server 2008 box without making him a local administrator. If I add him to the local administrators group, he can connect to SSIS just fine, but it seems that I can't grant him enough permissions through SQL Server to give him these rights without being a local admin.

I've added him to every role on the server, every database role in MSDB shy of DBO, and he's still not able to connect. I don't see any SSIS-related Windows groups on the server - Is membership in the Local Administrators group really required to connect to the SSIS instance on a SQL Server? It seems like there is somewhere I should be able to grant "SSIS Admin" rights to a user (even if it's a Windows account and not a SQL account), but I can't find that place.

UPDATE: I've found an MSDN article (See the section titled "Eliminating the 'Access if Denied' Error") that describes how to resolve problem, but even after following the steps, I'm still not able to connect. Just wanted to add it to the discussion

Solution 1

When exactly does he get the "Access is Denied Error"? When trying to connect to SSIS using SSMS (SQL Server Management Studio) and specifying "SSIS" in the connection dialog? Or after this when trying to execute a package or something?

I guess it is the first situation and might be able to dig something up when I am back in the office tomorrow. I would let you know of course. Until then: where would your packages be stored if he could connect to the SSIS server: on the file system or in MSDB? If on the file system in the default location (under SQL Servers' root) or somewhere else? I think if you are not storing them in MSDB there are no SQL Server permissions involved here...

I was always able to work around this issue using the information provided in the article you mention.

Edit: Too bad; I cannot find anything "special" that we did but the steps mentioned in that MSDN article that you followed already.

• In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

• In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups.

• Restart the Integration Services service.

Connecting by using a Local Account

If you are working in a local Windows account on a client computer, you can connect to the Integration Services service on a remote computer only if a local account that has the same name and password and the appropriate rights exists on the remote computer.

Solution 2

I know it´s an old question, but in my case I solved the problem following the MSDN Article that you mention. But in the first try, I didn´t add users to the local group Distributed COM, that was the key. This article is more clear in my opinion.

http://www.sqlservercentral.com/blogs/robert_davis/2008/08/13/How-do-I-grant-access-permissions-for-SSIS-to-users/

Comments

[eugened]

He'll be deploying and managing packages on a remote SQL Server while he's here. SSIS on his workstation seems to work just fine, since he's a local administrator. If this was a single-instance server, I'd even consider making him a local admin, but since it's our sql cluster, that's not an option.

[eugened]

The packages are stored in the MSDB, but he can't even get that far - attempting to connect to SSIS using Management Studio gives him an access denied error. Adding him to the local administrators group on the server (which is what we ended up doing) resolved the problem, but that's obviously not ideal. Thanks for following up on this older question, and I appreciate any additional feedback you have to add.

[scherand]

I just searched my documentation and could not find any "special trick" we had to perform to get that working. If I remember correctly we did not restart the SSIS service once which led us to wild speculations why things are not working on that particular server. But in the end the solution was simple (restart the service). Sorry about that.