What permissions are required to connect to SQL Server Integration Services

Question

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

I added it to every role on the server, every database role in the shy DBO's MSDB, and it still could not connect. I do not see any Windows groups associated with SSIS on the server. Is membership in the local administrators group really necessary to connect to an SSIS instance on SQL Server? It seems that somewhere I should be able to grant "SSIS Admin" administrator rights to the user (even if it is a Windows account and not an SQL account), but I can not find this place.

UPDATE . I found an MSDN article (see the section "Troubleshooting" Access if Denied 'Error ") that describes how to resolve the problem, but even after completing the steps, I still cannot connect. I just wanted to add it to the discussion

Answer 1

When exactly does he receive the "Access is Denied Error" message? When you try to connect to SSIS using SSMS (SQL Server Management Studio) and specify "SSIS" in the connection dialog? Or after that when trying to execute a package or something else?

I guess this is the first situation and may be able to snatch something when I get back to the office tomorrow. Of course I would let you know. Until then: where will your packages be saved if it can connect to the SSIS server: in the file system or in MSDB? If the file system is in the default location (under the SQL Server root server) or somewhere else? I think that if you do not store them in MSDB, there are no SQL Server permissions here ...

I could always solve this problem using the information provided in the mentioned article.

Edit: Too bad; I can’t find anything “special” that we did, but the steps mentioned in this MSDN article you already followed.

• In the Run Permission dialog box, add or remove users and assign appropriate permissions to the corresponding users and groups. the available permissions are local Launch, remote start, local Activation and remote activation. Launch rights to grant rights or refuse permission to start and stop the service; Activation rights grant or deny permission to connect to the service .

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

• Restart service integration services.

Connect using a local account

If you are working on a local Windows account on a client computer, you can connect to the Integration Services services on a remote computer only if the local account with the same name and password and the corresponding rights on the remote computer .

Answer 2

I know this is an old question, but in my case I solved the problem after the MSDN article you mentioned. But on the first attempt, I did not add users to the local Distributed COM group, it was the key. This article is clearer in my opinion.

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

Answer 3

Does it run SSIS packages locally or remotely?

If he works locally on his workstation, he needs only the usual SQL privileges (i.e. select / insert / whatever) in the corresponding databases and tables that he accesses, since this is just a normal SQL connection.

Or does he deploy packages to run remotely?

Answer 4

I know this is an old question, but I ran into this problem before and was successful just using a different version of SQL Management Studio. I think my problem could be that the server was in 2012, and I tried to use 2014 to connect to SSIS.