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

Answer 1

Is he running the SSIS packages locally or remotely?

If he's running locally on his workstation, he should only need normal SQL priveledges (i.e. select / insert / whatever) on the relevant databases and tables he's accessing, as it's just a normal SQL connection.

Or is he deploying packages to be executed remotely?

Answer 2

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.