Monday 9 December 2013

Unable to connect SSIS from remote

Access Denied while connecting SSIS from remote.

Error:
------------------------------------------------
Cannot connect to TESTSERVER1.
------------------------------------------------
Connecting to the Integration Services service on the computer "TESTSERVER1" failed with the following error: "Access is denied".
This error occurs when the computer has not been configured to allow remote connections through DCOM, or the user does not have permission to access the SQL Server Integration Services service through DCOM.
------------------------------
-----------------

To fix this error and connect the SSIS from remote, try below steps.

Add the user account to the Distributed COM Users group. To do this, follow these steps:
  1. Click Start, click Run, type lusrmgr.msc, and then click OK.
  2. In the Local Users and Groups dialog box, click Groups, and then double-click Distributed COM Users.
  3. In the Distributed COM Users Properties dialog box, click Add.
  4. In the Select Users dialog box, type the user name under Enter the object names to select, and then click OK two times. 
  Grant the appropriate permissions for the MsDtsServer DCOM application. To do this, follow these steps:
  1. Click Start, click Run, type dcomcnfg, and then click OK.
  2. In the Component Services dialog box, expand Component Services, expand Computers, and then expand My Computer.
  3. Expand DCOM Config, right-click the MsDtsServer object, and then click Properties.
  4. In the MsDtsServer (SQL Server 2005) or MsDtsServer100 (SQL server 2008) Properties dialog box, click the Security tab.
  5. Under Launch and Activation Permissions, click Customize, and then click Edit.
  6. In the Launch Permission dialog box, click Add.
  7. In the Select Users or Groups dialog box, type the user name under Enter the object names to select, and then click OK.
  8. In the Launch Permission dialog box, click the user name under Group or user names.
  9. Under Permissions for UserName, click to select the Allow check box for the following permissions:
    • Local Launch
    • Remote Launch
    • Local Activation
    • Remote Activation
    Note UserName is a placeholder for the user name that you clicked in step h.
  10. Click OK.
  11. In the MsDtsServer (SQL Server 2005) or MsDtsServer100 (SQL server 2008) Properties dialog box, click Customize under Access Permissions, and then click Edit.
  12. In the Access Permission dialog box, click Add.
  13. In the Select Users or Groups dialog box, type the user name under Enter the object names to select, and then click OK.
  14. In the Access Permission dialog box, click the user name under Group or user names.
  15. Under Permissions for UserName, click to select the Allow check box for the following permissions:
    • Local Access
    • Remote Access
  16. Click OK two times.
 Restart the SQL Server Integration Services service.