Recently I've been struggling with the integration of Reporting Services 2008 and SharePoint 2007. One of the fundamental steps in getting this to work, is to grant the Reporting Services service account (domain user of course) the necessary rights in your SharePoint databases. The provided addin is supposed to do this for you. However, in my farm setup I cannot get it to work properly. Every time I'm greeted with the following error: "Unable to connect to the Report Server WMI provider". It seems that the server hosting the Central Administration website cannot connect properly to the SSRS server.
One way to get around this, is to install the Central Administration website on the SSRS server – at least temporarily. But as you add content databases in the future, you must follow this scenario again.
Therefore I recorded all steps that this function performs:
1) adds the SSRS service account to the WSS_WPG group on the Reporting Server
2) Grants the SSRS service account the following rights on your SharePoint databases:
Contentdb for central administration: db_owner & WSS_Content_Application_Pools
Configuration db: WSS_Content_Application_Pools
For every other content db: db_owner
That's all there is to it!