Connect to a Remote Integration Services Server (SSIS Service)
Applies To: SQL Server 2016
This topic discusses the Integration Services service, a Windows service for managing Integration Services packages. SQL Server 2012 supports the service for backward compatibility with earlier releases of Integration Services. Starting in SQL Server 2012, you can manage objects such as packages on the Integration Services server.
Connecting to an instance of Integration Services on a remote server, from SQL Server Management Studio or another management application, requires a specific set of rights on the server for the users of the application.
To connect directly to an instance of the legacy Integration Services Service, you have to use the version of SQL Server Management Studio (SSMS) aligned with the version of SQL Server on which the Integration Services Service is running. For example, to connect to the legacy Integration Services Service running on an instance of SQL Server 2016, you have to use the version of SSMS released for SQL Server 2016. Download SQL Server Management Studio (SSMS).
To manage packages that are stored on a remote server, you do not have to connect to the instance of the Integration Services service on that remote server. Instead, edit the configuration file for the Integration Services service so that SQL Server Management Studio displays the packages that are stored on the remote server. For more information, see Configuring the Integration Services Service (SSIS Service).
Open SQL Server Management Studio.
Select File, Connect Object Explorer to display the Connect to Server dialog box.
Select Integration Services in the Server type list.
Type the name of a SQL Server Integration Services server in the Server name text box.
The Integration Services service is not instance-specific. You connect to the service by using the name of the computer on which the Integration Services service is running.
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.
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.
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 My Computer, and then click the DCOM Config node.
Select the DCOM Config node, and then select SQL Server Integration Services 11.0 in the list of applications that can be configured.
Right-click on SQL Server Integration Services 11.0 and select Properties.
In the SQL Server Integration Services 11.0 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, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.
Close the MMC snap-in.
Restart the Integration Services service.
Run dcomcnfg.exe at the command prompt.
On the Applications page of the Distributed COM Configuration Properties dialog box, select SQL Server Integration Services 11.0 and then click Properties.
Select the Security page.
Use the two separate dialog boxes to configure Access Permissions and Launch Permissions. You cannot distinguish between remote and local access - Access permissions include local and remote access, and Launch permissions include local and remote launch.
Close the dialog boxes and dcomcnfg.exe.
Restart the Integration Services service.
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.
By default the SQL Server Integration Services service does not support the delegation of credentials, or what is sometimes referred to as a double hop. In this scenario, you are working on a client computer, the Integration Services service is running on a second computer, and SQL Server is running on a third computer. First, SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which the Integration Services service is running. Then, however, the Integration Services service cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.
You can enable delegation of credentials by granting the Trust this user for delegation to any service (Kerberos Only) right to the SQL Server service account, which launches the Integration Services service (ISServerExec.exe) as a child process. Before you grant this right, consider whether it meets the security requirements of your organization.
For more info, see Getting Cross Domain Kerberos and Delegation working with SSIS Package.