Author: Carl Rabeler
This technical note describes how we designed and implemented a business intelligence solution that utilized a server farm containing Microsoft® Office SharePoint® Server 2007, Microsoft Office PerformancePoint® Server 2007 Monitoring Server, and Microsoft SQL Server® 2008 Reporting Services in SharePoint mode, all running on Windows Server® 2008 R2 and with all servers and applications configured for Kerberos authentication. In this technical note, we discuss the design requirements for this business intelligence solution, its logical architecture, the challenges we faced in architecting and implementing this solution, and our resolutions to these challenges.
Note: The business intelligence solution discussed in this technical note was configured and tested in the SQL CAT customer lab and is currently being deployed (substantially as discussed in this article) at a customer site.
The design requirements for this business intelligence solution are:
· Deploy the solution into a Windows Server 2008 R2 domain.Deploy all server applications in this solution onto servers running Windows Server 2008 R2.
· Deploy a server farm running Office SharePoint Server 2007 (SharePoint), which includes the SharePoint Web front-end (WFE) and the application server, on one server and the SQL Server (SQL Server) database on another server.
· Configuring the SharePoint services for later scale-out to multiple SharePoint WFE front-end servers and multiple SharePoint application servers
· Deploy SQL Server 2008 Reporting Services (Reporting Services) on a server, integrate Reporting Services with SharePoint, and deploy its databases to a shared metadata database server
· Deploy Microsoft Office PerformancePoint Server 2007 Monitoring Server (PerformancePoint) on the SharePoint application server, deploying its Monitoring System database to the shared metadata database server, and configuring PerformancePoint for later scale-out to a dedicated application server.
· Utilize SQL Server and SQL Server Analysis Services (Analysis Services) data sources (the data source servers) for the Reporting Services reports and PerformancePoint dashboards.
· Design reports using Report Builder 2.0 and PPS Dashboard Designer.
· Execute Reporting Services reports and view PPS dashboards utilizing Windows® Internet Explorer® 7 or Internet Explorer 8.
· Configure the MOSS application server to authenticate business users retrieving business intelligence data into their Reporting Services reports and PPS dashboards from the the data source servers using the business user’s authentication credentials.
The following two diagrams represent the solution architectures for the initial deployment and for eventual scale-out. The discussion in the remainder of this document describes the challenges we faced and our resolutions in implementing this solution architecture.
In this initial deployment architecture, the SharePoint WFE and application server along with the PerformancePoint Server are located on a single application server (called the MOSS App Svr in the following diagram), with a single dedicated server on which Reporting Services is installed in SharePoint mode (called the SSRS Svr in the following diagram), and with a single server on which SQL Server is installed that contains all of the metadata for SharePoint, PerformancePoint and Reporting Services (called the Metadata Svr in the following diagram).
In this full scale-out deployment architecture, the components in the previous diagram are scaled out to the following components:
· Multiple SharePoint WFE servers (called WFE (1) through WFE (n) in the following diagram).
· Multiple SharePoint application servers (called MOSS App Svr (1) through MOSS App Srv (n) in the following diagram).
· A dedicated SharePoint metadata server (called the MOSS DB Svr in the following diagram).
· A dedicated PerformancePoint Server application server (called the PPS App Svr in the following diagram).
· A dedicated PerformancePoint Server metadata server (called the PPS Mntr Sys DB Svr in the following diagram).
· Multiple Reporting Services application servers (called SSRS Svr (1) through SSRS Svr (n) in the following diagram).
· A dedicated Reporting Services metadata server (called the SSRS DB Svr in the following diagram).
In designing and deploying this solution, we encountered a number of installation challenges. Some were unique to Kerberos, and others were related to software installation. These challenges and our resolutions are discussed next.
Note: This technical note assumes a basic understanding of Kerberos; a full discussion of Kerberos architecture and step-by-step configuration procedures is beyond the scope of this article. For more information about Kerberos architecture, see Microsoft TechNet. It contains has many articles, including the following one: Kerberos. For step-by-step guidance, see the articles that are mentioned in context in this technical note. We include our comments on lessons learned from our use of these articles. There is no single article for this combination of components.
To enable the middle-tier applications (MOSS, PPS, and SSRS) on the Application server to seamlessly authenticate users in accessing report and dashboard data on the data source servers, Kerberos is required. If it is properly configured, the client computer obtains the Kerberos ticket for the user from the Kerberos Key Distribution Center (KDC) based on properly registered Service Principal Names (SPNs), and then it presents this ticket to the SharePoint server farm when it establishes a connection.
Note: The KDC is a network service that supplies session tickets and temporary session keys to users and computers within an Active Directory® domain. The KDC runs on each domain controller as part of Active Directory Domain Services.
The application servers in the SharePoint server farm, if they are properly configured, impersonate this user when then retrieve the requested data from the appropriate SQL data server (SQL Server or Analysis Services). This is the classic double-hop scenario, which requires Kerberos to securely impersonate a user credentials when connecting to a service on another computer. In order for the Internet Information Services (IIS) service or the Reporting Services service to impersonate the user during connection to the appropriate SQL Data server, the service account under which the IIS service or the Reporting Services service is running must have permission to impersonate, and the computers involved must be authorized for delegation.
Note: For more information about troubleshooting with respect to Kerberos and delegation, see Kerberos authentication and troubleshooting delegation issues.
On the application server in our solution, there are multiple IIS Web applications within MOSS (Central Administration, a portal site, a My Site, and an SSP Administration site) as well as the PPS web application and the SharePoint WFE. These Web applications utilize different identities via dedicated application pools, and these Web applications listen on both default and nondefault ports (that is, ports other than TCP port 80 and SSL port 443). In addition, the WFE forwards the SSRS requests to the SSRS server. In order for impersonation of the user’s Kerberos credentials to work properly, the identity for each Web application must have an SPN registered. Furthermore, the appropriate SPN must be utilized when the client connects to a particular IIS Web application. In addition, the SSRS service on the SSRS server along with the Analysis Services and SQL Server services on the SQL Data servers must each have properly registered SPNs. To implement Kerberos authentication for this environment, we utilized the approach described in the following Microsoft TechNet article: Configure Kerberos authentication (Office SharePoint Server), supplemented by the Excel® Services documentation in the following Knowledge Base article: How to configure SharePoint Server 2007 and Excel Services for Kerberos authentication and PPS-specific information in the following Microsoft TechNet article: Configure Monitoring Server for Kerberos delegation.
Note: One of things we really like about the Configure Kerberos authentication (Office SharePoint Server) TechNet article is its step-by-step approach to verifying that Kerberos is configured and working properly. However, you should be aware that the Event ID in the Event log for a successful login using Kerberos has changed with Windows Server 2008 and Windows Vista®. It is now Event ID 4624 (see Knowledge Base article 947226); it was Event ID 540 in Windows Server 2003 (as documented in the article).
As pointed out in the Configure Kerberos authentication (Office SharePoint Server) article, Internet Explorer does not, by default, include port numbers in its Kerberos ticket requests. If port numbers are not included, impersonation is attempted using the Kerberos ticket associated with the application pool identity for the Web application listening on port 80 (or port 443 if SSL is involved). Because in our solution architecture we have Web applications listening on both nondefault and default port numbers, this default behavior does not result in the appropriate Kerberos ticket being retrieved for Web applications listening on nondefault ports – resulting in authentication errors.
To enable Internet Explorer to include port numbers in every request, you can change a registry setting on every client. For more information about this behavior and registry key, see Knowledge Base article 908209. While this Knowledge Base article refers only to Internet Explorer 6.0, the behavior and the registry key changes also apply to Internet Explorer 7 and 8. For the customer environment for which we were designing this solution, modifying the registry on each client (even by means of a global policy) was not acceptable. Without the port numbers in the Kerberos ticket request, only one SPN can be registered for each host name.
Our challenge was to design a solution that enabled an SPN for each Web application in IIS such that a unique Kerberos ticket is retrieved for each Web application on the application server if the application is listening on a nondefault port.
To enable a unique SPN and Kerberos ticket for each IIS Web application listening on a nondefault port, we did the following:
To illustrate, we began by creating host names in DNS for the SharePoint portal site (hostname), for Central Administration (hostnameca), and PerformancePoint (hostnamepps). Then we registered SPNs for the short host name as well as the fully qualified domain name (FQDN) (hostname.mydomain.com), registering these names both with and without the port number.
Note: We registered SPNs both with and without port numbers to ensure that our Kerberos configuration worked successfully whether Internet Explorer submitted the port numbers or not.
Note: We also registered SPNs for the service account for the SQL Server relational engine, Analysis Services, and Reporting Services services. Only two SPNs were registered for these services because connectivity to these services is not made by Internet Explorer; instead, the middle-tier services on the application servers connect directly to these services.
Our solution to Kerberos Challenge 1 required that we configure IIS to utilize different application pool identities for each Web application: for example, hostname, hostnameca, and hostnamepps each used a different application pool identity. In IIS 7.5 with Windows Server 2008 R2 (as well as with IIS 7.0 in Windows Server 2008), IIS utilizes kernel mode authentication by default. Kernel mode authentication runs under the machine account of the IIS server. For more information, see Security Authentication <authentication> and Advanced Settings Dialog Box – Windows Authentication Feature. This change to kernel mode authentication is generally a good thing, because performance is improved and the configuration for Kerberos is simplified in many scenarios; authentication is done under the LocalSystem account, regardless of the application pool identity. However, in a Web farm environment (such as in our scenario), we needed to use the application pool for authentication to ensure the correct Kerberos ticket would be utilized for the appropriate Web service when it was scaled out to multiple application and front-end servers.
There are two ways to resolve this problem:
· Set the useAppPoolCredentials attribute in the system.webServer/security/authentication/Windows-Authentication configuration section of the ApplicationHost.config file to “True”.
· Disable Kernel Mode authentication.
After discussing the situation with the developers on the SharePoint and Internet Explorer teams, we discovered that we had run into a recently discovered incompatibility with kernel mode authentication when using an application pool identity. The incompatibility was traced back to performance enhancements in Internet Explorer 8 that impact SharePoint 2007. We ran both functional and performance tests in our environment using Internet Explorer 7 and Internet Explorer 8, with kernel mode authentication disabled for all of the Web applications that used dedicated application pool identities, and we determined that this configuration functioned properly and performed well. As a result, our recommended solution is to disable kernel mode authentication with SharePoint 2007 to seamlessly support a mix of Internet browsers.
The resolutions to our first two Kerberos challenges performed as expected when the SharePoint farm consisted of a single SharePoint server (with either a local or a remote SQL Server database). However, we encountered an issue with this approach when we scaled out to multiple front-end Web servers and multiple application servers. When we scaled out, we changed the name of our Web applications for Network Load Balancing (NLB) to a virtual name, which NLB then directed to a specific physical server. For example, we changed the name for our SharePoint portal site from hostname to kerbportal.mydomain.net and changed our SPNs accordingly. We followed the steps in Update a Web application URL and IIS bindings (Office SharePoint Server) to update the Web application URL and IIS bindings. When we did so, we were unable to view our deployed Reporting Services reports and PerformancePoint dashboards. We received errors stating that the published PPS dashboards and SSRS reports were not able to find the underlying dashboard and report locations. For example, reports that were deployed to http://hostname/ReportServer were still trying to connect to that location, but the newly configured location for the report server was http://kerbportal.mydomain.com/ReportServer. However, the deployed report was not configured to use this new URL path.
Note: For more information about this problem, see section 8.4 of Reporting Services SharePoint Integration Troubleshooting.
The solution was quite straightforward after we identified the problem. Namely, we needed to configure virtual host names in DNS that would eventually be utilized in NLB, but until it was time to scale out, we pointed these virtual host names in DNS to the single SharePoint server.
So, for example, for the SharePoint portal site, create a DNS entry for a virtual host called (for example) kerbportal that points to the SharePoint server. Later, you can update this entry in DNS to point to the NLB server. Then, make sure to specify kerbportal.mydomain.com in the Host Header field when you create the portal Web site in the Central Administration application in SharePoint. Similarly, configure virtual names for your My Site and SSP Administration sites. Following this approach, adjust the SPNs that you register to utilize the virtual host names that you will use when you later scale out. If you use this approach, you will not have to reconfigure your SPNs when you later scale out, and you will not have to redeploy your Reporting Services reports or your PPS dashboards.
For more information, see Plan for host-named site collections (Office SharePoint Server).
During the installation of the various software components, we also encountered a number of installation challenges. While none of these challenges were showstoppers, we present these resolutions to ensure that you can easily resolve these issues if you encounter them. The challenges we faced and how we resolved them are discussed in the following sections.
When we installed the SQL Server Reporting Services Add-in (rsSharepoint.msi) on the SharePoint server, we followed the steps listed in SQL Server Books Online: How to: Install or Uninstall the Reporting Services Add-in. When we attempted to install the Reporting Services Add-in (rsSharepoint.msi) on the SharePoint server on Windows Server 2008 R2, the Reporting Services Add-in did not complete successfully, and the installer rolled back the attempted installation. When we reviewed the Application log in Event Viewer, we discovered numerous “Insufficient SQL database permission” errors. We reran the Reporting Services Add-in from a command prompt with elevated permissions, but we received the same errors. To resolve this issue, we followed the “How to Install Reporting Services Add-in in Files-Only Mode” section in How to: Install or Uninstall the Reporting Services Add-in. These steps enabled us to successfully install the Reporting Services Add-in. We verified the successful installation of the Reporting Services Add-in by reviewing the RS_SP_<N>.log file in C:\Users\<login>\AppData\Local\Temp.
When we configured Reporting Services integration in SharePoint, we followed the steps listed in SQL Server Books Online: How to: Configure Report Server Integration in SharePoint Central Administration. When we configured report server integration settings in Central Administration, we were unable to select Windows Authentication to specify that connections be made using Windows integrated authentication. While this initially seemed to indicate a Kerberos problem, we checked and double-checked our Kerberos configuration and found no errors. After some research on the Internet using Bing, we found the following article Troubleshooting Server and Database Connection Problems that suggested that User Account Control (UAC) might be the problem – although the problem described in this article was not exactly the same as the one we were experiencing. We closed the Central Administration site in Internet Explorer and reconnected with elevated permissions, and then we were successfully able to select Windows Authentication for our SharePoint integration with Reporting Services.
When we installed PerformancePoint Monitoring Server, we followed the steps listed in the following TechNet article: Configure Monitoring Server for Kerberos delegation. As discussed previously, we configured a unique application pool and identity for the PPSMonitoringPreview and PPMonitoringWebService subsites, as well as a unique virtual host name and port number for the PPS Web application. The virtual host name enables us to use the appropriate Kerberos ticket for the PPS service account. However, when we used Internet Explorer to connect to the PPSMonitoring site (HTTP://ppsmonitoring.mydomain.com:40000), we were unable to connect to either the WebService or the Preview subsites. Because we were reasonably confident of our Kerberos configuration at this point, we immediately looked at the identity for the application pool for the root site as well as these two subsites. We discovered that while we had configured the application pool identity for each of the subsites to the PPS service account, the root site was not similarly configured – instead, it was configured to use the NetworkService account. As soon as we changed the identity of the application pool for the root site to the PPS service account, PPS worked as expected. Upon rereading the TechNet article, we noted that this step was not clearly stated.
During our testing of this solution, we created some reports using Report Builder 2.0 to display the user identity when connecting to our SQL Data servers in order to verify that Kerberos was configured and working properly. When we attempted to open one of these reports using the Report Builder ClickOnce application, we received the following error: “System.IO.StreamReader: The Report Element was not found.”
After a quick bit of research on the Internet using Bing, we learned that by default, the ClickOnce application opens Report Builder 1.0 rather than Report Builder 2.0. The solution is to install the add-in, Microsoft® SQL Server® 2008 Report Builder 2.0 ClickOnce for SharePoint in order to make Report Builder 2.0 ClickOnce available from a SharePoint site.
Note: This add-in is separate from the ClickOnce version of Report Builder 2.0 in SQL Server 2008 PCU1.
You then need to configure the default ClickOnce application in Report Manager and in SharePoint Central Administration to open Report Builder 2.0 rather than Report Builder 1.0. The following ReadMe and SQL Server Books Online articles explain the problem and solution:
· Microsoft SQL Server 2008 Report Builder 2.0 ClickOnce for SharePoint Readme
With proper planning, you can successfully you can successfully deploy a server farm using Microsoft Office SharePoint Server 2007, Microsoft SQL Server 2008 Reporting Services, and Microsoft Office PerformancePoint Server 2007 Monitoring Server, and you can configure Kerberos authentication for user authentication throughout the server farm. The use of virtual host names for each Web application with a unique application pool identity enables you to specify unique SPNs for each of these Web applications such that Internet Explorer clients can seamlessly connect to the resources within the server farm using their own authentication credentials.
Pingback from Kasper de Jonge BI Blog » Deploying a Business Intelligence Solution Using SharePoint, Reporting Services, and PerformancePoint Monitoring Server with Kerberos
This post was mentioned on Twitter by workerthread: Reading SQLCAT post on deploying BI solution using #SharePoint, #PerformancePoint and #Kerberos http://bit.ly/3ULjy
Pingback from S??curit?? des donn??es dans l???environnement SQL Server « La BI ??a vous gagne!
Here are this and some other articles on Analysis Services and Kerberos:
Probably the best article I've read on how to get this disaster working. Despite going through hundreds of Microsoft documents on implementation, your first problem encountered is what we hit as well. Thanks for this!