Integrating Reporting Services with MOSS 2007 Forms based authentication site

I was planning to write about integrating reporting services with SharePoint 2007 since long and finally i am here to write about it. It is a step by step process to integrate reporting services with forms based authenticated MOSS site. It is a quite big blog but could be helpful to a person who is integrating reporting services with MOSS 2007 for the first time 🙂

1. Before start installing anything make sure you have following AD accounts created :
   • Service account – should be local admin on all the WFE servers as well as SQL Server
   • MOSSSVC
   • SQLSVC
   • Setup Account – MOSSAdmin – Should be the local admin in all WFE servers
2. Login to the SQLServer box using Setup account
3. Install SQL Server
4. Create Logins for all the above accounts in the SQL Server
5. Add all the above logins to DBCREATOR and SERVERADMIN Server roles.
6. Login to the WFE box using Setup account
7. Install .Net Framework 2.0
8. Install .Net Framework 3.0
9. Enable ASP.Net 2.0 Web Service Extension on IIS for all frontend severs
10. Do the “Advanced” installation of MOSS.
11. Step 1: Start the installation by clicking the Setup.exe in the x86/x64(for 64 bit machine), and the installation starts with the following screen. Select the Complete Server Type option and click the Install Now button. Change the file location from C:\ to some other drive where the space is enough…. This will take few minutes and will install the basic components.·
12. · Step 2: After the installation of basic components, it will automatically start the SharePoint Products and Technologies Wizard. This Wizard can even be started from the Start–>All Programs–>Microsoft Office Server–>SharePoint Products and Technologies Wizard link also. This will present a Welcome screen, Click the Next > button
13. 
14. Step 3: Next screen will be of Connect to a server farm, if it is the first server in the farm select the option “No, I want to create a new server farm” and if there are already one or more existing server, you can select either of the option “Yes, I want to connect to an existing server farm” or “No, I want to create a new server farm”, Click the Next > button
15. ·
16. Step 4: In this step specify the Configuration Database Settings. Do remember if your configuration database is hosted on another server, you must specify a domain account (Global Domain Account), Click the Next > button
17. ·
18. Step 5: Next step is to Specify the port number for the SharePoint Central Administration Web Application. details of Web Application and the port number can be read from the following screen shot, Click the Next > button
19. 
20. Step 6: Finally it will show the Configuration Successful screen, Click the Finish button
21. 
22. Step 7: This will open up the Central Administration homepage in the browser.

Configure Central Admin

1. Go to Operations Services on server
2. Start following services
   • Office SharePoint server search – (Note: Always enable this service because it is required to create SSP – shared service provider)
   • Windows SharePoint services help search
3. Create SSP if you need any of the shared services. – Create SSP by creating SSP web app.
4. Check the domain name in the SSP to check if MOSS is connected to AD or not
5. Create a Web application from Application Management SharePoint web application Management
6. Crate a Site collection – Blank site (Not necessary is restoring the site from dev. server).
7. Click on the created site collection. Add this site as a TRUSTED SITE.

Reporting Services Integration

1. Download Reporting Services Add in from – http://www.microsoft.com/downloads/details.aspx?familyid=58EDD0E4-255B-4361-BD1E-E530D5AAB78F&displaylang=en#filelist
2. Once the portal is up and running run the “Reporting services Add in”
3. Configure Reporting Service configuration manager at SQL 2008 side
4. Go to Microsoft SQL Server 2008 Configuration Tools Reporting Services Configuration Manager.
5. Connect to the server
6. 
7. Enable the service if not already started
8. 
9. Configure the service account
10. 
11. Create a Web server URL – Specify Report server name and TCP Port and then press Apply. Check the Created URL
12. 
13. Configure Database
14. 
15. Configure Report Manager URL – specify the Report manager name , Press Apply, Check the created URL (Not required to be configured in SharePoint integration mode)
16. 
17. Reset IIS.
18. Once you install the “Reporting Services Add in”, Central admin will have section to configure Reporting Services Integration named “Reporting Services” in “Application Management”.
19. Click on “Manage Integration Settings”
20. Specify “Report Server URL” as configured previously.
21. Specify “Authentication Mode” – set it to “Trusted Account”.
22. Click on “Grant Database Access” – Specify the Server name on which the SQL Reporting services are running and Press OK. Specify the credentials of SQLSVC.
23. Be aware of the WMI provider exception while configuring Database Access. This is just a bug in SQL Server 2008 Feb CTP(It will not be there anymore but if there is any WMI provider exception error just ignore it).

Enable Forms Based Authentication for MOSS Site

1. Go to Application Management Authentication Providers.
2. Change the Authentication Type to “Forms”
3. Set “LdapMembershipProvider” in Membership provider name
4. Set “LdapRoleProvider” in RoleManager Name
5. Check “Enable anonymous access”, Select “Yes” for “Enable Client Integration?”- This setting is required for Report Builder to open in the Forms authentication mode.
6. Go to “Policy for web application” in “Application Management” and add “Mossadmin” and give “full control”. Otherwise it will not allow you to login to the forms based authenticated site.
7. Open the web.config of central admin site.
8. Change the key name to “LdapMembershipProvider” for “PeoplePickerWildcards” section.
9. Add following configuration in the <system.web> section. [Replace <Organizational Unit name> and <domain controller name > with respecting OU and DC names of your AD structure]

10. <membership defaultProvider="LdapMembershipProvider">
    <providers>
    <add name="LdapMembershipProvider" type="Microsoft.Office.Server.Security.LDAPMembershipProvider, Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C" server="<server name>" port="389" useSSL="false" userDNAttribute="distinguishedName" userNameAttribute="sAMAccountName" userContainer="OU=<Organizational Unit name>, DC=<domain controller name>, DC=<domain controller name>" userObjectClass="person" userFilter="((ObjectCategory=group)(ObjectClass=person))" scope="Subtree" otherRequiredUserAttributes="sn,givenname,cn" />
    </providers>
    </membership>

11. Open the web.config of Home Site.

1. Change the key name to “LdapMembershipProvider” for “PeoplePickerWildcards” section.
2. Add following configuration in the <system.web> section. Use AD information to do the following configuration.

3. <membership defaultProvider="LdapMembershipProvider">
   <providers>
   <add name="LdapMembershipProvider" type="Microsoft.Office.Server.Security.LDAPMembershipProvider, Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C" server="<server name>" port="389" useSSL="false" userDNAttribute="distinguishedName" userNameAttribute="sAMAccountName" userContainer="OU=<Organizational Unit name>, DC=<domain controller name>, DC=<domain controller name>" userObjectClass="person" userFilter="((ObjectCategory=group)(ObjectClass=person))" scope="Subtree" otherRequiredUserAttributes="sn,givenname,cn" />
   </providers>
   </membership>
   <roleManager defaultProvider="LdapRoleProvider" enabled="true" cacheRolesInCookie="true" cookieName=".PeopleDCRole">
   <providers>
   <add name="LdapRoleProvider" type="Microsoft.Office.Server.Security.LDAPRoleProvider, Microsoft.Office.Server, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C" server="<server name>" port="389" useSSL="false" groupContainer="OU=<Organizational Unit name>,DC=<domain controller name>, DC=<domain controller name>" groupNameAttribute="cn" groupMemberAttribute="member" userNameAttribute="sAMAccountName" dnAttribute="distinguishedName" groupFilter="(ObjectClass=group)" scope="Subtree" />
   </providers>
   </roleManager>
   

This is how you can integrate reporting services with MOSS 2007.

Now you can enable site collection feature “Report Server Integration Feature” which will create content types Report Builder Model,Report Builder Report and Report Data Source.

After this installation you can create different document libraries for deploying reports and data sources of the reports. you can deploy reports directly from Visual Studio just by specifying the path of the document library.

To run reports with different set of the parameters you can either consume out of the box webpart “SQL Server Reporting Services Report Viewer”(Available after enabling “Report Server Integration Feature”) or you can create and use a custom developed webpart (which i did).

Apart from providing functionality to run reports we can provide various functionalities like Scheduling reports, Creating packages of reports etc. We can achieve this by writing custom code and using different web services provided by Reporting Services.