How do I give readonly access to connect to SSIS

Question

How do I give readonly access to connect to SSIS

robinhc

SSCommitted

Points: 1617
More actions
December 21, 2005 at 3:51 pm

#90263

I want my SSIS developers to be able to connect to SSIS from their workstations and monitor the success of package runs. Because of change control procedures, I am NOT allowed to let them make changes on the production SSIS server. I can connect without problems as I am a local administrator on the server. How can I configure limited access to SSIS?
Robin HC

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

JKSQL SSCrazy Eights Points: 8323 More actions · Answer 1

I was thinking about this and why would you want to limit read only rights in SSIS. the connection to the DBengine would limit the ability to do anything. That would be controlled using NT Authentication. so if they had read writes they would not be able to exec anything on the server

robinhc SSCommitted Points: 1617 More actions · Answer 2

I want the developer of the package to be able to connect to the integration server from his desktop and monitor the processing of the package. The package feeds a new data warehouse and completes in 1.5 to 3 hours. Although I want him to be able to monitor the package execution, I do not want him to start the package execution. I need to do that. I set up RDP so that he could log onto the server and he can run the management studio and connect to the integration services just fine. When he tries to connect remotely from his workstation, we get the error message “cannot connect to {servername}. Additional information: Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) Connect to SSIS Service on machine {servername} failed: Access is denied”

JKSQL SSCrazy Eights Points: 8323 More actions · Answer 3

now that is complicated. Is the integration server the default or is it a named instance? 2005 is tricky with that. For instance my machine is RC222 but the Server named instance is JKSQL. So it is RC222\JKSQL. 2005 only shows RC222 you need to specify the name. If that is not the problem why not just make the SSIS package a job and let him monitor the job? Use tables or e-mail for errors if they occur. Beside those recomendations I am out of ideas why access would be denied. Sorry

rasoulov Right there with Babe Points: 765 More actions · Answer 4

I am having this same problem. I as a dba do not have admin rights on the box. I have a default instance of sql and I have been granted permissions to the dtsserver Remote Access. I still get the same error but when I am granted admin rights on the box it works. Is there any work around? Our network team does not allow admin rights on the boxes.

Reecha Mishra SSC Enthusiast Points: 177 More actions · Answer 5

Hi,

Follow these steps and i am sure you will get rid of this erro message. I tried this on several boxes.

You need to give certain Rights at DCOm Config to get teh remote access to users with non-sysadmin account.

Access Is Denied Error

<O> </O>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:

<O> </O>Windows 2003 Server (SP1) and Windows XP (SP2)<O></O>

<O>- 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

<O>In the Security page we are interested in “Launch and Activation Permissions” section. Click Edit button to see “Launch Permissions” dialog.

<O>- “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.

BudaCli Hall of Fame Points: 3383 More actions · Answer 6

hi,

I have tried what Reecha Mishra has advised as per above with no joy, is there anything that I am missing.

I am running Win server 2008 R2 with SQL server 2008 SP1

Ta

What you don't know won't hurt you but what you know will make you plan to know better

Duppie SSChasing Mays Points: 613 More actions · Answer 7

Hey Duda,

It's a few months ago that you posted, but if you didn't fix it than I have the solution.

The post of Reecha Mishra didn't work for me either, but I had to do something extra. From configure your settings as described below, also give a user/group rights in the access permissions section. If you do that, the solution of Reecha Mishra works fine.

Good luck,

Michaël