So how do you connect to a remote SSIS Server? I will show examples based on SSIS 2005 SP1 bits on Windows XP SP2, Windows 2003 Server SP1 and Windows 2000 SP4. As an SSIS client for this document I will use SQLWB but the same apply for other clients.
In order to connect to a remote SSIS Server using SQLWB one need to type the name of the server and click the connect button. For this release the Browse window doesn’t support browsing for remote SSIS servers. Also, the values on the advanced page of connection dialog will have no affect on the connection.
Access Is Denied Error
If you are getting the dreaded “Connect to SSIS Service on machine <MachineName> failed: Access is denied” error when you connect, check the security settings on the server machine as follows:
Windows 2003 Server (SP1) and Windows XP (SP2)
- If the user running under non-admin account it needs to be added to Distributed COM Users group
- Run %windir%\system32\Com\comexp.msc to launch Component Services
- Expend Component Services\Computers\My Computer\DCOM Config
- Right click on MsDtsServer node and choose properties
- In MsDtsServer Properties dialog go to Security page
- Configure your settings as described bellow
- Restart SSIS Service
In the Security page we are interested in “Launch and Activation Permissions” section. Click Edit button to see “Launch Permissions” dialog.
“Launch Permissions” dialog allows you to configure SSIS server access per user/group. In the bottom of the dialog you can select:
- Local / Remote Launch permissions if you allow to a user/group to start service locally or remotely.
- Local / Remote Activation permissions if you allow to a user/group to connect to SSIS server locally or remotely.
Here are few examples of SSIS server configurations:
Enable Remote Access
By default low privileged users can only connect to SSIS Server on the local machine when the service already started. It is shown by the fact that only Local Activation checked for Machine\Users group. To grant the user permission connect to the running server remotely – check remote activation.
Control Who Can Start the Service
Normally SSIS Server can be started by a member of Administrators group either locally or remotely. It can be started either using conventional ways or by calling a method on an enabled SSIS service.
In some situations you may want to share these rights with low privileged users or revoke these rights from an administrator. It can be done by checking/un-checking Local/Remote Launch check boxes.
Windows 2000 SP4
- Run dcomcnfg.exe
- On Applications page of “Destributed COM Configuration Properties” dialog select MSDTSServer and click Properties button
- Select Security page
- Configure your settings as described bellow
- Restart SSIS Service
Security settings on Win2K are a little deferent then on Win2K3 or WinXP. There are 2 separate dialogs to configure Access Permissions and Launch Permissions. Unfortunately you can’t distinguish between remote and local access. By default on this platform any members of Machine/Users group have remote/local access and launch permissions.
Best Practice to Manage Remote Users for a SSIS Server
We recommend creating one or several windows groups (Distributed SSIS Users, etc) that you can use to control remote access using steps described above. You can also use the same groups to control access to packages stored in SSIS Store.
Unfortunately groups can’t be used when you need to add a user to Distributed COM Users group. Users should be added to the group one by one.
Local User Access to a Remote SSIS Server
If you are running under a local user account on your client machine, you can still access remote SSIS server under the following conditions:
- The machine running remote SSIS server should have an account with exactly same name and password as the one you are running under.
- The account should be configured to access SSIS server as described above.
Delegation Problems
SSIS 2005 doesn’t support scenario when a client, SSIS Server and underlying SQL server running on 3 different machines.
This scenario can emerge when you change default MsDtsSrvr.ini.xml to point to a remote SQL server (for example when you implement a shared SQL Store or want to separate SQL Server and SSIS Server for security reasons).
This post was provided by Nick Berezansky from the Integration Services team.
Thanks,
Universe.Earth.Software.Microsoft.SQLServer.IS.NickBerezansky
Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden