Thursday 13 March 2014

SQL Integration Services Access

Once upon a time, a long time ago, in SQL 2008, all users had access to connect to Integration Services in Management Studio. Naturally, SQL 2012 does it differently, and you have to explicitly grant access via Component Services
  1. Run Dcomcnfg.exe. This lets you edit the registry.
  2. In the Component Services dialog, expand
    Component Services --> Computers --> My Computer --> DCOM Config .
  3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
  4. On the Security tab, click Edit in the "Launch and Activation Permissions" area.


  5. Add users and assign appropriate permissions, and then click OK. If you don't like your users remoting onto the server, chose 'remote launch' and 'remote activation'



  6. Repeat steps 4 - 5 for the "Access Permissions" area. Again, grant remote access if you don't allow users to log directly onto the server.





  7. Restart the Integration Services Service in SQL Configuration. Restart Management Studio if you had it open, and were trying to connect from that.