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.
   

How to install SQL Configuration Manager

When moving Sharepoint Foundation 2010 databases, the official advice is to set up a SQL Alias so that the Web Application Servers point at the new SQL server while using the old SQL Server name.

In order to do this, you need to have installed SQL Server Configuration Manager on the Web and Application Servers.

It's very easy to install SQL Server Configuration Manager without installing all the rest of SQL Server: just install the 'Client Connectivity Tools'. Ensure you install the version of the tools which matches the version of SQL Server installed on your database server.

• Choose Install -> New installation or add features to an existing installation.
• Choose all defaults until you get to the Feature Selection tab
• Select Shared Features -> Client Tools Connectivity
• Choose all remaining defaults and confirm installation

And that's it. No restart required. I did find that CPU on the server shot up to 100% for much of the install in our QA environment, so I don't recommend doing this in anger during office hours. Leave it to out of hours if you can.