Connecting to a Remote Integration Services Server

When attempting to connect to a SQL 2005, 2008 or 2012 Integration Services instance with a user account that is not an windows administrator on the machine running the SSIS services, you receive the following message:

"Cannot connect to SSIS Server
Additional information: Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
Connect to SSIS Service on machine "SSIS Server" failed: Access is denied."

OR:

When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an "Access is denied" error message. You can avoid this error message by ensuring that users have the required DCOM permissions.

...

Necessarily rights for remote users on Windows Server 2003, 2008 or Windows XP

To connect to a Remote Integration Services Server hosted on Windows Server 2003, 2008 or Windows XP the users must be added to the following groups:

1. Distributed COM Users:

If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

2. Component Services ? COM Security

Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

Expand the Component Services node in the left pane of the console. Expand the Computers node, expand and Right click on My Computer node, and then choose Properties.

Add the user to ?Edit default? on both ?Access permissions? and ?Launch and Activation Permissions? from "COM Security" tab.

3. Component Services ? DCOM Config ? MsDtsServer

Expand the Computers node, expand My Computer, and then click the DCOM Config node.

Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

Right-click on MsDtsServer,  MsDtsServer100 or Microsoft SQL Server Integration Services 11.0 (depending on the SQL Server / operating system version) and select Properties.

In the Properties dialog box, select the Security tab.

Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

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.

Click OK to close the dialog box.

Under Access Permissions and Configuration permisions, repeat steps above to assign the appropriate permissions to the appropriate users and groups. The easiest is to add the local DCOM Distributed Users group or the user name that should connect to SSIS.

Close the MMC snap-in.

4. Give rights to that specific user to the SQL Server:

Grand db_owner (SQL Server 2005) or db_ssisadmin (SQL Server 2008 and up) rights to the user that should connect to SSIS on msdb database.

5. Restart the Integration Services service.

For more information please visit:

http://msdn.microsoft.com/cen-us/library/aa337083.aspx

http://support.microsoft.com/kb/940232/ro

Possible errors

Erorr 1

?The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2008 Books Online.

Login failed for user 'server_name\user_name'. (MsDtsSrvr)?

Solution

Grand db_owner rights to the user that should connect to SSIS on msdb database.

Erorr 2:

"Cannot connect to SERVER_NAME
Failed to retreive data for this request. 
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (Microsoft.SqlServer.ManagedDTS)"

Solution

Restart the Integration Services service.

Erorr 3

The following error can appear when trying to import an SSIS package to the remote server:

Access to Integration Services package ?PACKAGE_NAME? is denied (Microsft SQL Server Native Client 10.0)

Solution

Grand db_ssisadmin rights to the user that should connect to SSIS on msdb database (SQL Server 2008 and up).