Information Integration: SSRS and MOSS 2007

The SQL Server 2005 Reporting Services (SSRS) reporting framework satisfies the typical information requirements of most organizations, but the next generation of features available with SQL Server 2005 SP2 and Microsoft SharePoint products takes reporting to new levels. The integration of SSRS and Microsoft Office SharePoint Server (MOSS) 2007 Enterprise Edition not only lets information workers more easily find, use, and share information across the enterprise, but it also simplifies report management and security implementation for application administrators. But what does better information access and simpler report administration really mean - and how much work will you have to do to make it work?

Part 2: SSRS and MOSS 2007: Deploying the Power

In this article, the first in a two-part series, I briefly introduce you to SSRS and MOSS's rich information-integration environment, explain the technical architecture, and walk you through setting it up. In the follow-up article, I'll show you how to deploy the integrated environment to support better information access and integration, including how to implement security, report properties, versioning, approval workflow, alerts, information management policies, and the business intelligence support features built into MOSS.

Improving Information Access

SSRS integration with MOSS does more than enable information consolidation. MOSS's search capabilities let users find all relevant information and reports at once instead of requiring them to look through a variety of repositories. MOSS also lets you target reports to specific users or groups and supports sending alerts to users when report definitions or properties change. In addition, MOSS collaboration features let users supplement reports with background context using wikis or blogs and implement action plans using task lists.

These capabilities all sound great for users, but how much extra effort does this mean for you as an administrator? Consider having to define permissions for all types of information - reports, documents, spreadsheets, and more - in just one place. You can manage data sources in one place as well. You can also better manage report content through MOSS by requiring users to check out reports to make modifications and check them back in afterward, implementing workflow to approve new reports before they are published, incorporating version control to track changes over a report's lifetime, and enforcing report retention policies. The most challenging aspects of SSRS and MOSS integration are component configuration and security implementation, which I'll cover in the second article of this series. However, before you leap to SSRS-MOSS integration, be aware that MOSS doesn't support the following SSRS features:

If you can't live without these features, you might consider maintaining two instances of SSRS. You can run one instance in native mode, keeping these features available in your environment, and the other instance in integrated mode, letting you take advantage of the MOSS features that enable better information access. These two instances can even coexist on the same server, although you might achieve better performance by installing them on separate servers.

Inside the Architecture

Before installing the components required for SSRS-MOSS integration, you need to understand the difference between a single-server deployment and a distributed deployment so that you can select the one best suited for your environment.

Single-server deployment. The simplest integrated architecture includes all components on one server, as Figure 1 shows. This approach works when you have a limited number of users and reports. However, SSRS alone requires a lot of memory and will compete for memory with SQL Server and MOSS.

Let's review each component's role in the integrated environment. Installing the Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies updates your MOSS installation to include a new SSRS proxy endpoint, a new Web Part for viewing integrated-mode reports, and application pages for storing and managing integrated-mode reports. MOSS databases host the SSRS configuration information as well as SSRS content. Specifically, the MOSS configuration database stores the information you provide when you configure SSRS for the MOSS instance. The content database is the primary storage location for report server items such as report definition files, report models, and data sources as well as for report server item properties. In addition, the MOSS content database stores security permissions for report server resources.

Installing SQL Server 2005 SP2 on your report server adds a new Simple Object Access Protocol (SOAP) endpoint for integrated report management and new extensions that let MOSS manage security and enable subscription delivery to a MOSS document library. SP2 also modifies the Reporting Services Configuration Tool to support MOSS integration and to generate database scripts for integrated-mode report server databases.

The report server uses the same two databases that it uses in native mode but with a slight variation. The report server database is the primary storage location for report snapshots, report history, schedules, and subscriptions. In addition, this database serves as secondary storage for the report definition files, report models, data sources, and their properties, avoiding repeated transmission of files to the report server for processing. As in native mode, the report server temporary database stores session data and cached instances.

When you add, modify, or execute a report on the MOSS server, the report server first compares the item and its properties from the MOSS site with the corresponding copy (if one exists) in the report server. If the report in MOSS is new or has a later timestamp, a synchronization process copies the new version to the report server database. Even in integrated mode, the report server performs all data processing, rendering, subscription delivery, and snapshot and history generation. The main difference between native and integrated mode is the storage location for report server content and properties.

Distributed deployment. Distributed deployments come in many variations, but I limit my review to two approaches that illustrate key configuration points. You can set up MOSS on one server and store its configuration and content databases on a remote server. I call this the remote SQL Server deployment method and assume that SSRS also is installed on the remote server. Or, you can install all MOSS components, including the databases, on one server and install SSRS on a second server. I call this the MOSS stand-alone deployment method.

Alternatively, you can create a MOSS farm to support multiple Web front-end servers. SSRS integrates with the farm itself, not with any particular MOSS server in the farm, and uses only one report server database for the farm. If the MOSS farm has multiple Web applications, each Web application can use different authentication providers. For example, one Web application could use NTLM authentication while another uses Kerberos, and the report server would interact with each MOSS server by using the appropriate authentication mode.

Installation Prerequisites Because

this article is about SSRS-MOSS integration, I assume that you have the report server installed correctly and, if you're planning a distributed deployment, that you have MOSS installed on another server. The first step to installing the integration components is to download and install on the report server the Microsoft .NET Framework 3.0 Redistributable Package, available at http://msdn.microsoft.com/en-us/library/aa964978%28v=vs.85%29.aspx. If you haven't already, you also need to download SQL Server 2005 SP2 from http://go.microsoft.com/fwlink/?linkid=82758. Then you need to download the Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies from http://go.microsoft.com/fwlink/?linkid=82753.

Using the Reporting Services Configuration Tool, which you launch after installing SP2, you must configure a service account for the SSRS Windows service and its Web service. The service accounts will later require write and execute permissions on the MOSS content database to let the report server add, change, or delete report server items and properties. These permissions are assigned when you configure the MOSS Web application for SSRS integration. But for now, make sure you select the correct type of service account for your environment, as Table 1 shows.

If you plan to use domain user accounts as the SSRS service accounts, create them now. You also need to know the username and password for the account that MOSS uses to connect to its databases. In addition, make sure you have the necessary privileges, or schedule time with a person who has them, before performing the five integration configuration tasks that Table 2 describes.

Installing and Configuring MOSS on the Report Server

You start the integration setup by installing SP2 on the report server. Next, install the Windows SharePoint Services (WSS) object model on the same server, following the appropriate installation steps for your deployment mode, as follows. (For information about using WSS or MOSS Standard Edition instead of MOSS Enterprise Edition, see the Web sidebar "What Can I Accomplish with Other SharePoint Technologies?")

Single-server. In a single-server deployment, the object model installs when you perform an Advanced installation of Microsoft Office SharePoint Server 2007 Enterprise Edition and select the Complete server type. When installation is complete, reboot if prompted, and run the SharePoint Products and Technologies Configuration Wizard, which you find in the Microsoft Office Server program group. On the wizard's Connect to a Server Farm page, select No, I want to create a new server farm. Type the name of your SQL Server system, and provide a domain account for MOSS database access. Then select an authentication provider - NTLM or Kerberos - and click Finish.

When the wizard completes, the SharePoint Central Administration page opens. To complete a basic installation, first start the Office SharePoint Server Search service to support indexing and searching of MOSS content. Click the Operations tab, click Services on server in the Topology and Services section, and click Start next to Office SharePoint Server Search. On the following configuration page, select the Use this server for indexing content and Use this server for serving search queries check boxes. You must also provide an email address for an administrator to be contacted if indexing problems occur and a domain account and password to run the search service, then click Start to start the service.

Next, configure a new Web application to create the SharePoint site on your Microsoft IIS server. To do this, in SharePoint Central Administration, click the Application Management tab, click Create or extend Web application in the SharePoint Web Application Management section, then click Create a new Web application. On the next page, keep the default settings, changing the authentication provider to Kerberos if your deployment requires it instead of NTLM. You must also specify a domain account or select a built-in account for the application pool assigned to this Web application. If you have a remote SQL Server deployment, be sure to change the database server name. The MOSS content and configuration databases will be created on the specified server. Click OK.

The last configuration step in this deployment model requires you to create a site collection for the Web application you created. A site collection is essentially a Web page that displays a tab for each MOSS site you create in that collection. Each site has one or more Web pages that you can use to organize content to support a specific goal, such as storing reports in a library or managing collaborative activities. On the Application Management page, in the SharePoint Site Management section, click Create site collection. Provide a title for the site collection, and set the URL for the Web Site Address to http://your_server/sites/ Reports, for example. In the Template Selection section, click the Enterprise tab, and select Report Center. This site template adds to your MOSS site a set of predefined Web pages that you'll use later to manage reports and data sources. Specifically, a Report Center site contains the document libraries and other business intelligence (BI) features that make information integration easy. Type the domain account of the user who will be the primary administrator of the site collection, and optionally provide a domain account for a secondary site collection administrator. Click OK to create the site collection.

Distributed servers. For a distributed deployment, the WSS object model installs when you run the MOSS Web front-end setup on the report server. Note that you can't mix SharePoint technologies, which means you can't install WSS on your report server and then integrate SSRS with a MOSS server or vice versa.

Start the MOSS setup program on the report server, choose the Advanced installation option, and then select the Web front end option. When the installation completes, reboot if prompted, and then run the SharePoint Products and Technologies Configuration Wizard. On the Connect to a Server Farm page, select Yes, I want to connect to an existing server farm. On the Specify Configuration Database Settings page, type the name of the database server, click Retrieve Database Names, and select the MOSS configuration database, which by default is SharePoint_Config. Provide the password for the account that the Web front end uses to connect to this database, select either NTLM or Kerberos authentication on the next wizard page, and then click Finish. Finish the distributed deployment installation by configuring a new Web application and site collection as described in the previous section.

Reporting Services configuration. If your MOSS Web application uses the default port 80, which is the case for a default MOSS configuration, you must reassign the Web site that's hosting the Report Server Virtual Directory to an unused port, such as 8080, and be sure that Web site is started in IIS. To reassign the report server's Web site port, open Internet Information Services (IIS) Manager in the Administrative Tools program group, expand the server hosting SSRS, and expand the Web Sites folder. Right-click the Default Web Site (or the Web site you've assigned the SSRS virtual directories to), and click Properties. In the Default Web Site Properties dialog box, on the Web Site tab, type the number of the unused port in the TCP port box. Keep the dialog box open for subsequent steps.

Next, verify authentication on the Web site to ensure that anonymous access is disabled and that integrated Windows authentication is enabled. In the Default Web Site Properties dialog box, click the Directory Security tab. In the Authentication and access control section, click the Edit button. In the Authentication Methods dialog box, clear the Enable anonymous access check box and select the Integrated Windows authentication check box.

Last, revise the RSReportServer.config file (which you can find at Program FilesMicrosoft SQL ServerMSSQL.nReporting ServicesReportServer) by changing the URLRoot property to include the port number (e.g., http://your_server:8080/reportserver); this provides the correct link to reports in email subscriptions. Before continuing, restart IIS, and then test the URL in your browser to make sure SSRS works correctly.

Setting Up Integrated Mode

You're now ready to prepare the report server for integrated mode. First, use the Reporting Services Configuration Tool to change the Windows Service Identity and Web Service Identity to use a domain user account if the MOSS databases are on the same machine as the report server in a remote SQL Server deployment. Start the Reporting Services Configuration Tool by clicking Start, pointing to All Programs, Microsoft SQL Server 2005, Configuration Tools, and clicking Reporting Services Configuration. Select the correct SSRS instance by specifying the report server name and selecting the SSRS instance when prompted, then click Connect. Click Windows Service Identity in the left pane of the configuration tool's window, then select Windows account, provide the domain username and password created as explained in the earlier Installation Prerequisities section, and click Apply.

To change the Web Service Identity, you use IIS Manager, but first confirm the application pool that SSRS is using. Click Web Service Identity in the left pane of the Reporting Services Configuration Tool, and in the drop-down list next to Report Server, note the name of the application pool. Don't worry about the Report Manager application pool's identity because you won't use it after you switch the report server to integrated mode. Keep the configuration tool open, and switch to IIS Manager. Expand the server hosting SSRS, expand the Application Pools folder, right-click the application pool you identified for Report Server in the configuration tool, click Properties, and click the Identity tab. Select Configurable, provide the domain username and password, and click OK. Remember that this step is necessary only for a remote SQL Server deployment.

To convert the report server to integrated mode, switch back to the Reporting Services Configuration Tool, click Database Setup in the left pane, connect to the database server, then click Change. Click Yes to confirm the request to change the report server mode. In the resulting SQL Server Connection dialog box, provide a new report server database name, confirm that the Create the report server database in Share Point Integrated mode check box is selected, as Figure 2 shows, and click OK. At the bottom of the Database Setup page, click Apply, and then click OK to confirm the credentials used to finalize the database.

The Report Manager Virtual Directory page in the Reporting Services Configuration Tool is now disabled because the report server is in integrated mode. Also notice the red status icon next to the Share- Point Integration link in the left pane. This icon means SharePoint Integration is not configured. The status will change to Configured (a green icon) after you configure the MOSS server for integration, which I cover next.

Before closing the Reporting Services Configuration Tool, make sure you back up the encryption key. You'll need the encryption key backup if you ever need to restore the report server databases. Without this backup, after restoring the report server databases, you must delete encrypted data such as data sources or user information because there would be no way to decrypt it. To create the backup, click Encryption Keys in the left pane, click Backup, and type a password. Click the ellipsis button to the right of the Key File box, and then in the Save As dialog box, navigate to a secure folder or removable media location, provide a file name, and click Save.

A common question from report server administrators planning to convert an existing SSRS server from native to integrated mode is how to migrate the existing report server contents. Unfortunately, there's no migration utility to transfer the contents of the native mode report server database to the integrated mode version of the database. Instead, you have to redeploy existing reports to the MOSS server and reconfigure execution and security settings of each report in MOSS.

Installing and Configuring the SSRS Add-in on the MOSS Server

Now, you're ready to install the SSRS add-in on the MOSS server. If you have a MOSS farm, you must install the add-in on all Web front-end servers in the farm except the report server. When you've updated all the servers, you can configure SSRS integration in MOSS. Remember from Table 2 that you must use a domain user account that has MOSS farm administrator and site collection administrator privileges to perform this task. To begin the SSRS configuration, click Start, point to All Programs, point to Microsoft Office Server, and click SharePoint 3.0 Central Administration. Click the Application Management tab. The addin has updated this page to include a Reporting Services section. Click Manage integration settings to specify the URL for the report server (e.g., http://your_server:8080/reportserver) and set the authentication mode, as Figure 3 shows. Click OK when finished. (For information about which authentication mode to use, see the Web sidebar "The Differences Between Authentication Modes.")

On the Application Management page, click Grant database access. Provide the report server name and instance in preparation for granting permissions to the SSRS Windows and Web services to access the MOSS databases. After you click OK, you're prompted for report server administrator credentials. If you have trouble here, you might need to reapply the settings on the Windows Service Identity and Web Service Identity pages of the Reporting Services Configuration Tool. After you properly configure the service accounts, the status of SharePoint Integration in the Reporting Services Configuration Tool should be Configured (green).

Optionally on the Application Management page, you can click Set server defaults to provide default settings for report history and logging in the Reporting Services Server Defaults page, as Figure 4 shows. If an error displays when you try to open this page, make sure you've created a Web application and site collection. Also be sure you're logged in using a site collection administrator account. Finally, be aware that sometimes the configuration changes you make in the Reporting Services section require a restart of IIS before you can open the Reporting Services Server Defaults page.

Your next step is to configure Component Services with the correct permissions for the SSRS Web Service Identity to activate MOSS. On the MOSS server, click Start, point to Administrative Tools, click Component Services, and then expand Computers, My Computer, DCOM Config. Right-click IIS WAMREG Admin Service, and click Properties. On the Security tab, click the Edit button for Launch and Activate Permissions. Click Add and type the built-in account or domain user account you assigned to the Report Server application pool. Alternatively, you can click the Advanced button to search for and select the account. After you add the account, highlight it in the Group or user names list in the Launch Permission dialog box, then select the Local Activation check box in the Allow column. Click OK twice to close all dialog boxes and save the permission changes.

Last, update the document libraries in the MOSS Web application to use the SSRS content types. You can make these changes to any document library in your MOSS Web application, but I limit my explanation to the Reports and Data Connections libraries that are part of the standard Report Center site. In Internet Explorer (IE), navigate to the Reports document library in your MOSS Web application. For example, if the URL you assigned to the Web application is http://your_server/sites/Reports, go to http://your_server/sites/Reports/ReportsLibrary to open the Reports library. On the Settings menu, click Document Library Settings. In the Content Types section, click Add from existing site content types. In the Available Site Content Types list, select Report Builder Report, click Add, and click OK.

Report Center includes the Data Connections library to restrict access to data source files and related management tasks. To add the SSRS content types for data sources, open the library, and repeat the steps you followed to add report content types, except select Report Data Source.

You can add the Report Builder Model content type to either the Reports library or the Data Connections library. I find it easier for users to use a report model if it's in the same library as the Report Builder Report content type; the Report Builder application displays a list of all models in the same library in which the user creates the new Report Builder Report. If the model is in a different library, the user must click the default library link in the Getting Started pane of Report Builder, must know which MOSS library contains the desired model, and must navigate to that location.

Successful Configuration

By following these steps, you have successfully installed and configured the components required to integrate SSRS with MOSS Enterprise Edition. (For more information about these steps, see the Microsoft resources listed in the Learning Path at InstantDoc ID 96840.) In the second article in this series, I'll show you how to perform familiar SSRS tasks, such as deployment, security implementation, and property management, in the new MOSS environment. I'll also show you how to take advantage of new features enabled by MOSS, such as versioning, workflow, alerts, information management policies, and how to achieve BI information integration with SSRS and MOSS.