Table of Contents

Introduction

Microsoft SQL Server 2005 and the 2007 Microsoft Office System are key parts of Microsoft Business Intelligence (BI) strategy. These products and technologies work together to put tools in the hands of business users to enable them to make better, faster, and more relevant decisions about their businesses. This paper provides best practices and troubleshooting tips for integrating Microsoft SQL Server 2005 with Microsoft SharePoint� technologies (both Microsoft Windows� SharePoint� Services 3.0 and Microsoft Office SharePoint� Server 2007). To understand how to configure Reporting Services to work in SharePoint integrated mode and to learn how to manage reports, see Microsoft SQL Server 2005 Integration with Microsoft SharePoint Technologies.

This white paper is for users who have already installed SQL Server 2005 Reporting Services, SQL Server 2005 Service Pack 2 (SP2), and either Windows SharePoint Services 3.0 or Office SharePoint Server 2007.

Best Practices

This section outlines best practices and tips for administering the integration between SharePoint and SQL Server 2005 Reporting Services.

Backup Report Server Database

By default, when you install Microsoft Office SharePoint Server (MOSS), Windows SharePoint Services (WSS), or Reporting Services, no backup schedule is configured. It is a best practice to back up your databases regularly. It is also a good idea to configure a maintenance plan to reorganize indexes and perform general cleanup of your databases. For more information about configuring backups, see SQL Server 2005 Books Online. The name of the Report Server database to backup is specified during the setup process. By default, the Report Server database is named ReportServer.

Backup Encryption Key

Reporting Services encrypts security-sensitive report information, such as user names, passwords, and embedded data sources. Encryption is done by using a symmetric key that is automatically generated when Reporting Services is installed and configured.

The symmetric key is encrypted by using a private key value that is based on the Windows service account that you specified in the Reporting Services Configuration tool. Therefore, if you change the account directly in the Service Control Manager under which the Report Server Windows service runs, Reporting Services can no longer decrypt encrypted content. Use the configuration tool to change this service account and to back up the encryption key before you change the Report Server Windows service account or immediately after initial configuration.

To back up the encryption key

1.    Open the Reporting Services Configuration tool.

2.    Click the Encryption Keys link.

3.    Click the Backup button. You are prompted for a password and a file name.

Keep the backup file in a safe place. If you have to restore the report server from a backup, you must restore the encryption keys or you may not be able to run your reports.

Configuring SharePoint Integration Authentication Mode

To configure Reporting Services to operate in SharePoint integrated mode, you must install the Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies. Once this add-in is installed, you can configure Reporting Services integration with SharePoint technologies by browsing to the SharePoint Central Administration Web page and clicking the Application Management tab. Under the Reporting Services application, click Manage integration settings to specify the URL location of the Reporting Server Web service (see Figure 1).

 

Figure 1: Reporting Services integration settings

 

Figure 1 also shows Authentication Mode configuration options, which specify the security authentication mechanism used by SharePoint technologies to communicate to the Report Server Web service. Two Authentication Mode settings are available:

�         Windows Authentication � Uses Kerberos authentication between the SharePoint site and the report server. If you are using Kerberos-enabled Windows authentication, you must use Windows Authentication mode. Windows Authentication is the most secure way for SharePoint technologies to communicate with the report server.

�         Trusted Account � Uses the service account of the SharePoint Web application that has permission to impersonate a SharePoint user between the SharePoint site and the report server. A SharePoint user token representing the user who is logged on to the SharePoint Web application is also contained within the request to Report Server. Any work performed is done by using the context of this SharePoint user token. If you are not using Kerberos-enabled Windows authentication on the SharePoint Web application, you must use trusted account authentication mode. In scenarios where Kerberos is required and tedious to set up, this mode can be used to validate whether Reporting Services and SharePoint 2007 technology integration works as expected prior to setting up Kerberos.

Configuring Report Server Identities

The security credentials used for the report server must be configured to enable Reporting Services to operate securely. You can configure the Windows Service identity and the Web Service identity separately by using the Reporting Services Configuration tool.

Windows Service Identity

The Windows Service identity can be viewed in the Service Control Manager in Control Panel or by using the Reporting Services Configuration tool. Figure 2 shows that the same account is configured in both places.

 

Figure 2: Selecting the Windows Service Identity account

 

If you need to change the Windows Service identity account, it is a recommended practice to make the change in the Reporting Services Configuration tool and not in Service Control Manager. Changing it in the configuration tool automatically backs up the encryption keys, changes the account, sets the applicable system privileges for the account, restores the encryption keys, and restarts the service.

Following are recommended settings for the account that you select:

�         Domain account � You must select a domain account if your database server and / or SharePoint server are not on the same physical server as the report server. This is because a local account that is created on one server is not the same account on another server, even if the accounts have the same name. It is a recommended practice to use a domain account as the Windows Service identity.

�         Local account � A local account can be used if the database server and the WSS or MOSS server are the same physical server as the report server.

�         Built-in account � This enables you to use Local System, Network Service, or Local Service as the Report Server Windows Service identity. This is generally recommended when the database and MOSS or WSS software is installed on the same physical computer as the report server. If the only SharePoint Server is on a different physical computer, you can use a built-in account for Report Server; however, this requires manual configuration (covered in detail in Troubleshooting Tips later in this paper).

Note   The account that you specify for the Windows Service identity must have permissions to access the Report Server database.

Web Service Identity

The Report Server Web Service identity is the identity of the Internet Information Services (IIS) 6.0 application pool that hosts the Report Server URL that you specified on the Manage Integration Settings Web page of the SharePoint Central Administration Web site. You can change the identity of this application pool by using IIS Manager. Figure 3 shows example security credentials for the ReportServer application pool.

 

Figure 3: Setting an IIS 6.0 application pool security account

 

The recommendations for choosing the security account are similar to those for the Report Server Windows service covered in the previous section. One additional recommendation for security purposes is that if you use a predefined account, choose the Network Service account because it is the least privileged of the predefined accounts.

Troubleshooting Tips

This section outlines troubleshooting tips for common issues that can arise when integrating SharePoint 2007 technologies with SQL Server 2005 Reporting Services SP2 in SharePoint integrated mode.

Cannot Publish a Reporting Services Project to SharePoint

After installing Business Intelligence Development Studio (BIDS), which comes with SQL Server 2005 SP2, you can design and publish reports, data sources, and models to a report server in SharePoint integrated mode. In SharePoint integrated mode, you must publish these reports, data sources, and models directly to a SharePoint document library. The Report Designer or Model Designer project properties must be configured correctly to publish reports to a SharePoint document library.

 

Symptom: If you deploy reports, data sources, or models to a report server in SharePoint integrated mode, you may receive the error message shown in Figure 4.

 

Figure 4: Connection error when publishing to an invalid URL

 

Solution: Ensure that you specify the project properties with correct URLs. The following table assumes a server named nighthawk, a SharePoint site named ReportCenter, and a document library named Reports:

 

 

Message Indicating text/xml Was Expected During Deployment

Versions of Business Intelligence Developments Studio (BIDS) earlier than SQL Server 2005 SP2 do not work correctly when communicating with a report server in SharePoint integrated mode.

 

Symptom: After you configure the correct URLs for your SharePoint site and deploy your project, you receive the error message shown in Figure 5.

 

Figure 5: Expected text/xml error during deployment

 

Solution: At a minimum, install the client components from SQL Server 2005 with SP2 on the computer from where reports are deployed. Typically, this is a developer�s workstation that contains Microsoft Visual Studio� 2005 or Business Intelligence Development Studio. Installing the workstation components from SQL Server 2005 with SP2 updates the report designer, model designer, and the components that are required to deploy to a SharePoint site.

Reporting Services Prompts for Login During Deployment

Symptom: If you uninstall Reporting Services from the server where MOSS or WSS is installed and reinstall it on the same server, you may be prompted to log on when you deploy reports, as shown in Figure 6.

 

Figure 6: Login prompt when deploying reports

 

Solution: Verify that there is only one ReportServiceAuthentication endpoint specified for each SharePoint Web application that encounters this error by following these steps:

1.    On the SharePoint server, open IIS Manager.

2.    Navigate to the site folder for the SharePoint Web application.

3.    Right-click the site folder and click Explore.

4.    Open the Web.config file by using Notepad or another file editor.

5.    Search for ReportServiceAuthentication.asmx. There will probably be more than one search result for this string.

6.    Edit the Web.config file and ensure that there is only one instance of the following XML element structure:

�<location path="_vti_bin/ReportServer/ReportServiceAuthentication.asmx">

��� <system.web>

����� <authorization>

������� <allow users="*" />

����� </authorization>

��� </system.web>

� </location>

Cannot Decrypt Symmetric Key Error

Symmetric keys are used to decrypt encrypted Reporting Services data, such as security-sensitive information stored in the Report Server database. The symmetric key is generated when the SQL Server Reporting Services service starts for the first time. Therefore, if you change the account under which the Reporting Services service runs, any existing encrypted content cannot be decrypted.

 

Symptom: The Windows Event Viewer on the Reporting Services server displays the error shown in Figure 7.

 

Figure 7: Event Viewer showing symmetric key error

 

Solution: To correct this error, follow these steps:

1.    Change the Reporting Services service so that it logs on as the account that was used when the symmetric key was generated.

2.    Back up the encryption keys by using the Reporting Services Configuration tool.

3.    Change the Reporting Services service account to use the new credentials.

4.    Restart the Reporting Services service.

5.    Restore the encryption key from the backup file generated in step 2.

If you cannot back up and restore your existing symmetric keys, you cannot access reports after you change the Reporting Services account. In this case, you must delete the encrypted content by using the Reporting Services service account and republish your data sources, reports, and other Reporting Services content.

If you need to change the Windows Service identity account, you can avoid problems with the symmetric key by changing the account in the Reporting Services Configuration tool instead of changing it in Service Control Manager in Control Panel.

Service Unavailable

The security credentials used in the application pool assigned to the ReportServer virtual directory in IIS must have permissions to use the IIS worker process.

 

Symptom: When you browse the ReportServer virtual folder, you may see the following:

�         A �Service Unavailable� message in the browser window.

�         A red �X� next to the application pool name in the IIS manager.

 

Solution: To correct this error, perform the following steps:

1.    Log on to the SharePoint server by using an account that has administrative permissions.

2.    Determine which application pool is assigned to the ReportServer virtual directory.

3.    Determine the security account used for the application pool discovered in step 2.

4.    Open the Local Users and Groups node in Computer Management.

5.    Locate and open the IIS_WPG group.

6.    Add the security account discovered in step 3 to the IIS_WPG group.

7.    Restart IIS.

Error Connecting to the Report Server

Symptom: The Windows Event Viewer on the Reporting Services server displays the error shown in Figure 8, indicating an issue connecting to the Report Server database.

 

Figure 8: Event Viewer showing database connectivity error

 

Solution: To correct this error, make sure that the account specified for the Windows Service identity has access to the Report Server database. Note that the Windows Service identity account is the account shown in the Logon column of the Control Panel Services applet for the SQL Server Reporting Services service.

Report Server Only Supports SharePoint Default Zone

Symptom: When you access a Reporting Services item via a URL that belongs to a SharePoint zone that is not the Default zone, the following error is encountered:

 

The specified path refers to a SharePoint zone that is not supported. The default zone path must be used.

 

Solution: In the Alternate Access Mappings section of the Operations tab in the SharePoint Central Administration Web site, configure the URL you are using for accessing Reporting Services items to be in the Default zone.

Error When Using Built-in Accounts for Report Server Service Accounts

Symptom: When either the Report Server Web service or the Report Server Windows service runs under a built-in account such as NetworkService, the Grant database access option in SharePoint Central Administration does not work correctly. Consequently, accessing any Reporting Services feature through a SharePoint site results in the following error:

 

An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. --> Client found response content type of 'text/html; charset=utf-8', but expected 'text.xml'.

 

Solution: To avoid this error, use one of the following approaches on the computer that hosts Report Server.

�         One option is to:

1.    Continue to run the Report Server Web service as NetworkService.

2.    Add the built-in account, such as NT_AUTHORITY\NetworkService to the WSS_WPG Windows group.

3.    Set IIS to use NTLM as its w3svc/NTAuthenticationProviders.

�         Another option is to configure the service accounts to run under a domain user account.

Error When Using WSS and MOSS in the Same Server Farm

Symptom: Viewing a report that is stored in a SharePoint document library on a server when working with a report server that is configured in SharePoint integrated mode results in the following error:

 

w3wp!library!7!5/14/2007-00:59:43:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details.

 Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> Microsoft.SharePoint.SPException: Failed to find the XML file at location '12\Template\Features\Publishing\Feature.xml'

�

   --- End of inner exception stack trace ---

 

Solution: You cannot mix Windows SharePoint Services 3.0 and Office SharePoint Server 2007 in the same farm (that is, they cannot be connected to the same configuration database). To avoid this error, upgrade the server on which the Report Server service is running to Office SharePoint Server 2007.

Unable to Use Forms or Custom Authentication

Symptom: When you access Reporting Services items in a SharePoint document library contained in a SharePoint Web application that uses forms or custom authentication, you may encounter the following error:

 

<html><head><title>Object moved</title></head><body>

<h2>Object moved to <a href="/_layouts/login.aspx?ReturnUrl=%2fsites%2ftestsite%2f_vti_bin%2fReportServer%2fReportService2006.asmx">here</a>.</h2>

</body></html>

 

Solution: Install hotfix 939942.

Conclusion

It is easy to install and configure SQL Server 2005 Reporting Services to integrate with Microsoft SharePoint products and technologies. However, problems can occur depending on your specific scenario and the configuration of either Reporting Services or the SharePoint product or technology. The troubleshooting steps in this white paper are the most common situations that you might encounter in your deployment. This paper also discusses best practices for keeping your system performing as well as possible and how to help avoid disasters.

 

For more information:

Microsoft SQL Server site

Microsoft Business Intelligence site

 

About the Author

Anthony T. Mann is the President of Mann Publishing Group, which publishes business and technical books under its Rational Press and Agility Press imprints. He is a veteran computer book author, writing more than 15 titles and regularly produces technical whitepapers and other content for Microsoft. He can be reached by email at tmann@mannpublishing.com.