Project Description
This code will perform the required steps to make SSIS servers accessible to users who are not administrators on the SSIS machine (see http://support.microsoft.com/default.aspx/kb/940232). This is generally done for developers. There are several manual configuration steps and they can be tedious, especially on multiple machines.


When attempting to connect to a SQL 2005 Integration Services machine with a user account that is not an administrator, you receive the following message:

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

For the configuration steps involved to remedy this error, please see http://support.microsoft.com/default.aspx/kb/940232.

If you want to automate these steps, look at this project.

I posted the SSISAccess project on CodePlex to allow others to leverage code I wrote to automate these steps. This code will perform the required steps to make SSIS servers accessible to users who are not administrators on the SSIS machine. This is generally done for developers. There are several manual configuration steps and they can be tedious, especially on multiple machines.

This is a console application. It is intended to be run from the command line. It is not a GUI.

Here are the scenarios:

SSISAccess server contoso\joe
The above will tell you (information only, no changes made) if the user contoso\joe will be able to access the SSIS server. This must be run on an SSIS server. It maps to steps 2,3 and 4 of KB 940232. If you have a group of people (rather than a single user), it is valid to use it instead of a single user.

SSISAccess server contoso\joe grant
The above will actually perform the modifications necessary to give joe access. Not just informational.

SSISAccess client
The above will tell you (information only, no changes made) if the client computer can access SSIS. Must be run on client computer. It maps to steps 1 and 2 of KB 940232.

SSISAccess client grant
The above will actually perform the modification necessary to configure the client computer.

Caveat: If you use an AD group in a server scenario, SSISAccess will add the local "Distributed COM Users" to the MsDTSServer COM access rights instead of the AD group you named. The reason is that I found adding an AD group directly in the ACLs for COM didn't resolve correctly. However, local groups, local users and AD users will. I worked around the AD group resolution problem by adding the local "Distributed COM Users" when an AD group is detected on the command line. The local "Distributed COM Users" will already have your AD group in it since SSISAccess will have already added it in a previous step.

Last edited Mar 4, 2008 at 11:22 PM by timomta, version 4