Thursday 4 February 2010

Remote access to SSIS or SSRS

I've recently encountered an issue with enabling users to remotely access SSRS and SSIS (SQL Server Reporting Services and SQL Server Integration Services).

My problem comes about when the users affected are not in the Local Admins group on the destination server - we want to lockdown rights to the box whilst still allowing SQL services to be accessible via SSMS (SQL Server Management Studio).

The users can still access SSRS remotely because they can go via the web interface but as anyone who uses it frequently will know it is so slow compared to using SSMS.

The fix to enable remote access to SSIS is as follows :
Goto Control Panel -> Administrative Tools on the destination server
1) Add the users (their login accounts, or even better an AD group) to the Distributed Com Users group unders Users & Groups. We found this step necessary but that might vary on different servers, the ordering of these steps doesn't really matter so if you fancy you could try it later. We have also added the users into the other SQL server groups there too (probably just "for good measure").

2) Goto Component services -> Computers -> My Computer, right click and go into Properties, Com Security and add the same users / group with the Remote Access privilege - you shouldn't need to add others because the everyone group should do that for you.

At this point Integration Services should now be accessible via Management Studio but Reporting Services is probably telling you it does not know the machine name you've entered (try it, go on). So to finish off for Report Services :

3) Open Computer Management, browse to Services and Applications -> WMI Control and right click on it then open Properties -> Security and finally add the requried users or group with the Remote Enable privilege. This is rather brute force, if you want more granular control let me know if you find which individual modules need amending.
Job done.