Configure a SharePoint Server 2010 farm for business intelligence by using NTLM

Článek
07/22/2014

Applies to: SharePoint Server 2010

Integrated Windows authentication enables Windows clients to seamlessly authenticate with Microsoft SharePoint Server without having to manually provide credentials (user name/password). Two protocol types often used to authenticate are the Kerberos protocol and the NT LAN Manager (NTLM) protocol. This article describes NTLM authentication detailing options for each SharePoint Server service, and it shows you the steps that are required to configure a Microsoft SharePoint Server 2010 environment for performing business intelligence tasks that use NTLM. To review the advantages and disadvantages of Kerberos authentication and NTLM authentication, see Overview of Kerberos authentication for Microsoft SharePoint 2010 Products.

The step-by-step instructions in this document cover several SharePoint Server 2010 scenarios that can be configured to use NTLM; links to additional resources are also provided. The scenarios covered include the following:

Scenario 1: Core configuration
Scenario 2: SQL Server and Analysis Services Configuration
Scenario 3: Reporting Services configuration
Scenario 4: PerformancePoint Services configuration
Scenario 5: Connect to SQL Server data from Excel and publish to a SharePoint site by using Excel Services

Scenario 6: PowerPivot for SharePoint 2010 configuration
Scenario 7: Create a data-connected Web diagram and publish to a SharePoint site by using Visio Services

You may have to configure Kerberos due to the deployment topology of the IT assets, but in many production and test scenarios this is not necessary. If you want to learn more about scenarios for various service applications dedicated to business intelligence, see the white paper Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products.

SharePoint Server 2010 in Classic Mode Authentication with NTLM

In SharePoint Classic mode, NTLM is the default protocol used for authentication flow into and out of the SharePoint Server farm, while claims authentication is used for authentication flow within the farm. This can introduce a configuration pitfall where you expect Windows Integrated authentication to use the client credentials to authenticate with a remote data source without an authorization barrier, but this is not allowed by the “double-hop” limitation in NTLM. The connection from the client to the SharePoint farm is considered the first hop, and the connection from the SharePoint farm to the remote data source is the second hop. To learn more about claims and the three authentication scenarios, incoming authorization, inter/intra-farm authorization, and outgoing authorization, see Overview of Kerberos authentication for Microsoft SharePoint 2010 Products

SPServer14_BIservices_IdentityScenarios2

In such cases, SharePoint 2010 Products must use the trusted subsystem model for data-tier access. As shown in the following illustration, a trusted subsystem uses an account of a trusted user for access to external systems.

SharePoint Server2010 Trusted Subsystem

Note

The independent account is called different things between SharePoint Server and SQL Server products. SharePoint Server calls this the Unattended Service Account while SQL Server calls it an Unattended Execution Account. It is important to know that it is a generic account, independent of the client and not being passed through the client.

SharePoint 2010 Products use one of the following strategies to implement trusted subsystems and enable data tier access in NTLM:

Prompt to the user for data source credentials
Embedded logon
Stored database credentials

Either one of the strategies creates a “first-hop” connection to the data tier, which eliminates the NTLM “second hop”. Using stored data source credentials eliminates the additional credential prompt when you are using the business intelligence applications. All users of the SharePoint Server resource share the same access to the data resources because only one data source credential can be stored on the SharePoint Server resource.

Using SQL Server products, you can also prompt the user for data source credentials.

The following video describes the architecture for NTLM authentication.

Running time: 4:17

Play video Watch the video about NTLM in a multi-tier architecture.

Download video For an optimal viewing experience, download the video about NTLM in a multi-tier architecture.

Right-click the link, and then click Save Target As to download a copy. Clicking the link opens a .wmv file in the default video viewer for full-resolution viewing.

Data access options for business intelligence products in SharePoint Server 2010

This section lists the data access options for each business intelligence product when the SharePoint farm is configured to use Classic mode authentication with NTLM and when the farm connects to remote data sources. Each SharePoint Server 2010 service implements its access to the data tier differently.

The Secure Store Service is a frequently used method for removing the double-hop problem while authenticating to external sources of data. The walkthrough in this article lists the steps that are required for enabling this deployment scenario by using Classic mode authentication with NTLM. The sequence of events that occurs is as follows:

A SharePoint Server 2010 user accesses a data-connected object such as an Excel Services or PowerPivot worksheet, Visio Services Web drawing, or PerformancePoint Services dashboard.
If the object is configured to use Secure Store for data authentication, the business intelligence Service Application calls the Secure Store Service to access the Target Application specified by the object.
The service application uses an unattended account to authenticate with the remote data source.

If the authentication is successful, the data is displayed to the user within the context of the worksheet, Web drawing, or dashboard.

Although the steps are similar, there are some differences between service applications. To learn more about how to configure the Secure Store Service for services in SharePoint Server 2010, see Use Secure Store with SQL Server Authentication (SharePoint Server 2010).

There are methods to embed user logon information into queries that allow a direct connection to the external system. For example, in PerformancePoint Services a Multidimensional Expression (MDX) function can be used to apply dynamic OLAP security to access SQL Server Analysis Services values.

Important

Authentication methods may have different names with similar purpose and functionality. For example, in PerformancePoint Services, Per User Identity refers to Integrated Windows authentication. The clarifications are made in the following table.

Service Application	Implementation Detail
Excel Services	Excel Services Application supports three data authentication methods: Integrated Windows authentication: Use to allow for Windows clients to seamlessly authenticate with the data source without manually providing credentials (user name/password). This method does not work with remote data sources unless Kerberos authentication is configured. Secure Store Service Authentication: Use when you want to access multiple system resources without having to provide authentication credentials one or more times. Also use when you must support individual and group mappings. None: use when the Excel Services Application uses incoming connection strings to connect to the database. Depending on the specific database provider, the database can use the connection string to authenticate the user. Tip Use this method when you want quick access to data for a single person and have no specific options for parsing the string. To learn more see Plan Excel Services authentication (SharePoint Server 2010).
PerformancePoint Services	PerformancePoint Services supports three data authentication methods: Per User Identity: Use to apply data-level security on the database. This is the same as Excel’s and Visio Service’s Integrated Windows Authentication in that each user’s account is used to access all data sources. This method does not work with remote data sources unless Kerberos authentication is configured. Note External data sources must reside within the same domain to authenticate to the external data sources; otherwise authentication will fail. Unattended Service Account: Use to access all data sources from a single shared user account. This is a low privileged domain account stored in the Secure Store Service. In establishing your unattended service account, first determine whether this account has the necessary access to the data sources that will be required in the Dashboard. Custom Data: Use to make SQL Server Analysis Services include the currently authenticated user name as a parameter on the custom data field in an Analysis Services connection string. The Custom data option is only used for Analysis Services data sources and can be used against SQL Server 2005 Analysis Services and SQL Server 2008 Analysis Services servers.
Visio Services	Integrated Windows authentication: Use to enable Windows clients to seamlessly authenticate with the data source without having to manually provide credentials (user name/password). This method does not work with remote data sources unless Kerberos authentication is configured. Secure Store Service: Use Visio Graphics Service to map the user’s credentials to an independent credential that has access to the database and use the Secure Store Service. This authentication model can only be used by drawings that use an ODC file to specify the connection. The ODC file specifies the target application that is used for credential mapping. Unattended Service Account: Visio Graphics Service provides an authentication method where an administrator creates a mapping for all users through a single account. The mapped account is called the unattended service account and is a low-privilege Windows domain account that is given access to databases through the Secure Store Target Application. The Visio Graphics Service impersonates this account when it connects to the database if no other authentication method is specified. This is the default authentication method if no ODC file is used in the Visio Web drawing that specifies a different authentication method.
PowerPivot for SharePoint 2010	When you are accessing an Excel worksheet with PowerPivot data, the PowerPivot service application accesses the local Analysis Services VertiPaq engine, which does not cross computer boundaries to a server outside the SharePoint farm. In case of data refresh, the PowerPivot service application uses credentials stored in Secure Store Services to refresh data from an external Analysis Services database.
SQL Server 2008 R2 Reporting Services1	Each report and its data sources can be configured to prompt for credentials or use preconfigured credentials that are stored as part of the report or data source’s metadata, and the option that is configured will be used when the user executes the report. In the case of an unattended report execution, such as a scheduled subscription, SQL Server Reporting Services uses the Unattended Execution Account stored on the report server to access the external data source.

1SQL Server 2008 R2 is not a service application in SharePoint Server 2010 and is not claims-aware; it does not take advantage of the intra-farm claims authentication architecture.

SharePoint Server 2010 business intelligence in a multi-tier scenario

The following diagram shows the deployment scenario used to configure SharePoint Server 2010 business intelligence in the sections. As noted earlier in the discussion about subsystems, the front-end service authenticates and authorizes the client and then authenticates with additional back-end services, without passing the client identity to the back end system. The back-end system "trusts" the front-end service to perform authentication and authorization on its behalf. The farm topology is load balanced and scaled out between multiple tiers to demonstrate how identity delegation would work in multi-server, multi-hop scenarios. Load balancing on the SharePoint Server front-end Web and SQL Server Reporting Services servers was implemented by using Windows Server 2008 Network Load Balancing (NLB). How to configure NLB and NLB best practices are not covered in this document. For more information on NLB, refer to Overview for Network Load Balancing.

Note

The topology in this example may be more or less complex than your own, but the essential characteristics of the client, SharePoint Server 2010 farm, and external system remain the same. For more information on how to design and build a production SharePoint Server environment, see Deployment for SharePoint Server 2010.

SharePoint Server 2010 NTLM authentication

Base configuration

There are scenarios that you can follow for configuration between the various services. Steps in this section show how to configure PerformancePoint Services, Excel Services, and Visio Services when you have not run the Farm Configuration Wizard. If you select the option to configure your farm by using a wizard, the wizard helps you create a default site collection and automatically configures your selection of service applications. The scenario assumes that you have chosen to configure everything yourself. To learn more about the different scenarios to deploy SharePoint Server 2010, see:

As you walk through the scenarios, you will recognize other differences in configuration.

Step 1: Create a Web application on SP10WFE-01.

To learn more see Create a Web application that uses Windows-classic authentication (SharePoint Server 2010). From the article configure using following steps.

Browse to Central Administration and select Application Management and Manage Web Applications.
In the toolbar, select New and create your Web application.
Select Windows “classic mode” Authentication.
Configure the port and host header for each Web application.
Select NTLM as the Authentication Provider

Note

If you select Negotiate and Kerberos authentication is not configured, authentication will default back to NTLM.
Under application pool, select Create New Application Pool and then select the Managed Account.

Note

A Managed Account is an Active Directory user account that uses credentials managed by and contained within SharePoint Server. To see how to register a new Managed Account, see Configure automatic password change (SharePoint Server 2010).

Note

It is a security practice to use a separate managed account to run each service application.

When creating the new Web applications, you also create a new zone, the default zone, configured to use the Windows authentication provider. Zones represent different logical paths for gaining access to the same sites in a Web application and may imply various authentication methods for a specified Web application.

If users will be able to access site content anonymously, enable anonymous access for the Web application zone before you enable anonymous access at the SharePoint site level; later, site owners can configure how anonymous access is used within their sites. To learn more about zones, see the section planning zones for Web applications, in Plan authentication methods (SharePoint Server 2010).

Step 2: Create a site collection on SP10WFE-01. Follow the steps in Create a site collection (SharePoint Server 2010).

SQL Server and Analysis Services configuration

In this section, you configure the SQL Server 2008 R2 database server and the SQL Server 2008 R2 Analysis Services server for access by the business intelligence applications and install the AdventureWorks sample databases and AdventureWorks sample cube.

Step	For information, see
Install the SQL Server engine instance on dbsrvSQL and the Analysis Services instance on dbsrvSQLAS.
Open ports 1433 and 1434 on dbsrvSQL.	Configuring the Windows Firewall to Allow SQL Server Access
Open port 2383 on dbsrvSQLAS.	Configure windows firewall to enable Analysis Services Access
Enable TCP/IP and Named Pipes for the SQL Server engine instance on dbsrvSQL.	How to: Enable or Disable a Server Network Protocol (SQL Server Configuration Manager)
Download the sample databases from CodePlex and install them on both dbsrvSQL and dbsrvSQLAS. The install packages includes the sample Analysis Services project. You must manually deploy it.
Install the Analysis Services sample project and deploy the sample cube.	Installing Analysis Services Sample Database

Reporting Services configuration

In this section, you will configure SQL Server 2008 R2 Reporting Services to publish reports to a SharePoint site and view them in the SharePoint site. For an overview of the architecture for Reporting Services in SharePoint Server integration, see https://msdn.microsoft.com/en-us/library/bb283324.aspx.

Step	For information, see
Install SharePoint Server 2010 on the SP10App-02 and join it to the SharePoint Server farm.The report server computer requires SharePoint Foundation 2010 or SharePoint Server 2010 as a prerequisite.	How to: Configure SharePoint Integration on Multiple Servers
Install SQL Server 2008 R2 Reporting Services in SharePoint integrated mode on SP10App-02.	How to: Configure SharePoint Integration on Multiple Servers
Configure a domain account to run the report server instance. Note You must use domain user credentials to run your report server instance if both of the following are true: Your report server instance does not run on the same computer as your front-end Web server. Your report server instance runs on the same server as the SharePoint Server databases.	How to: Configure a Service Account for Reporting Services
In rsreportserver.config, remove the `<RSWindowsNegotiate>` tag if it exists under `<Authentication>`.	How to: Modify a Reporting Services Configuration File
Use the Trusted Account option when you set up report server integration in SharePoint Central Administration. This account is not used to access the data tier. It enables the Reporting Services Add-in for SharePoint Server 2010 to communicate with the Reporting Services Windows service on SP10App-02.	How to: Configure Report Server Integration in SharePoint Central Administration
Test the report server integration by accessing the link http://< hostname >/<site >/_layouts/ReportServer/SiteLevelSettings.aspx.
Download the sample reports from CodePlex and publish the sample reports to the SharePoint site.	How to Publish a Report to the SharePoint Library Deploying Models and Shared Datasets to a SharePoint Site
Locate the sample reports in the SharePoint Server catalog, configure the DataSources\AdventureWorks2008R2 shared data source to prompt for credentials, and select the Use as Windows credentials check box.	How to: Create and Manage Shared Data Sources (Reporting Services in SharePoint Integrated Mode)
Test the report view by opening a sample report in the SharePoint site. You should be prompted to input your Windows credentials. Type the credentials of a user who has access to the AdventureWorks2008R2 database.

PerformancePoint Services configuration

In this section, you configure PerformancePoint Services. You will configure security so that users have access to external data systems. For more detailed steps, see Configure PerformancePoint Services.

Step	For information, see
If you decide to open PerformancePoint Dashboard Designer from a site other than the Business Intelligence Center, see Enable the PerformancePoint Services site feature (SharePoint Server 2010).	Enable the PerformancePoint Services site feature (SharePoint Server 2010)
If you did not run the Configuration Wizard to create service applications and proxies, you must create a PerformancePoint Service application. You must also start the PerformancePoint Services service. You can manage services by using Central Administration or by using Windows PowerShell 2.0 cmdlets.	Create a PerformancePoint Services service application (SharePoint Server 2010) "Starting or stopping a service" in Manage services on the server (SharePoint Server 2010)
After you create a PerformancePoint Services service, it is a best practice to create and register a new service account for an existing application pool dedicated for PerformancePoint Services. To do this, run the following Windows PowerShell script to grant the account access to the associated content database. The following is an example. PS C:\> $w = Get-SPWebApplication(“<your Web application>”) PS C:\> $w.GrantAccessToProcessIdentity("<insert service account>") This step is necessary for PerformancePoint Services to work correctly. Be aware that this action grants db_owner access to the SharePoint Foundation content databases. Note SQL Server Authentication is not supported to the content databases.	Managed Accounts in SharePoint 2010 (https://go.microsoft.com/fwlink/p/?LinkId=198229)
Create and configure a Secure Store Service application and Proxy. This is required to store the Unattended Service Account password for a PerformancePoint Services service application. To initialize the Secure Store Service application, refer to the following sections of Configure the Secure Store Service (SharePoint Server 2010). Initialize an instance of a Secure Store Service application Refresh the encryption key	Configure the Secure Store Service (SharePoint Server 2010) Note Only specific sections apply to PerformancePoint Services configuration. Sections about how to create a target application or how to set credentials for a target application do not apply to PerformancePoint as they do for Visio and Excel Services.
Make sure that the service application connection, PerformancePoint Services service application, and Secure Store Service are associated with the Web application. In Central Administration, in the Application Management section, click Manage Web applications. On the Web Applications tab, click Service Connections. A Service Application Associations page appears. This shows either the default group or a custom group of service applications associated with the Web application. Important If you did not select Make this application service the default when you created the PerformancePoint Services service application, you will not see the PerformancePoint Services Service Application Proxy selected.	Add or remove a service application connection to a Web application (SharePoint Server 2010)
Configure the unattended service account. The unattended service account must be set for PerformancePoint Services to connect to data sources other than the currently authenticated user. The Unattended Service account is set after you configure the PerformancePoint Service application. The setting is located in Manage service applications in Central Administration under the PerformancePoint Services management page.	Configure the unattended service account for PerformancePoint Services
By default, all locations are trusted. You may want to limit access to PerformancePoint Services data sources or any object dependent on a data source by making available one or more sites, lists, or document libraries instead of the complete site collection. You can enable trusted locations for PerformancePoint Services before or after you enable PerformancePoint Services features in sites and site collections.	Enable trusted locations for PerformancePoint Services (SharePoint Server 2010)
Create a data connection for Analysis Services. To learn how to configure Analysis Services to work with time intelligence feature, see Configure Analysis Services data source time settings by using Dashboard Designer.	Configure Analysis Services data source time settings by using Dashboard Designer
Test data connectivity by creating a basic dashboard. If you have successfully created a PerformancePoint Services enabled site collection, you should be able to open PerformancePoint Dashboard Designer and connect to an external data source.	Video: Creating a basic dashboard by using PerformancePoint Dashboard Designer

Connect to SQL Server data from Excel and publish to a SharePoint site by using Excel Services

Excel Services Application is a shared service that you can use to view and edit workbooks in Excel Web Access. The following Excel Services scenario assumes that a Web application exists and that NTLM authentication is configured as described in Scenario 1: Core Configuration at the beginning of the article.

Step	For more information, see:
Define a new trusted location from which Excel files can be loaded. Note You can also use the default trusted file location for Excel Services that SharePoint Server 2010 creates automatically. To learn more about how to plan security, see Plan Excel Services authentication (SharePoint Server 2010).	Manage Excel Services trusted locations (SharePoint Server 2010)
Set up and configure Secure Store Service for Excel Services Application in Microsoft SharePoint 2010 Products. Set the credentials for an application ID to include in the next step.	Use Excel Services with Secure Store (SharePoint Server 2010)
Connect an Excel 2010 client to the correct SQL Server server. In the steps outlined in Connect to (import) SQL Server data you will complete procedures in a Data Connection Wizard. Select the database and table that you want in your Excel worksheet. Determine a file name to save a data connection file and type a description. Select the Authentication button to specify how the external data source is accessed if the workbook is published to a SharePoint Server site and is opened in a Web browser. You will select Windows Authentication, SSS (Secure Store Service), or None. Select SSS and enter the Application ID that you configured in the previous step so that Excel Services can use it to authenticate. Warning Make sure that the database is not opened in exclusive mode.	Connect to (import) SQL Server data Also see: Overview of connecting to (importing) data
Publish the Excel workbook to SharePoint Server 2010.	Publish a workbook to a SharePoint site

PowerPivot for SharePoint 2010 configuration

In this scenario, you add PowerPivot to your existing SharePoint Server 2010 installation on SP10App-02.

Step	For information, see
Follow the instructions at Microsoft Support to add Setup1000.exe.config to the path %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64 on SP10App-02.
Install PowerPivot in the existing SharePoint Server farm on SP10App-02	How to: Install PowerPivot for SharePoint on an Existing SharePoint Server
Configure the server.	Steps from How to: Install PowerPivot for SharePoint on an Existing SharePoint Server
Upload a PowerPivot workbook (includes installation steps).	Steps from How to: Install PowerPivot for SharePoint on an Existing SharePoint Server
View the workbook.	Steps from How to: Install PowerPivot for SharePoint on an Existing SharePoint Server

Create a data-connected Web diagram and publish to a SharePoint site by using Visio Services

Visio Services in Microsoft SharePoint Server 2010 is a service application that lets users share and view Microsoft Visio Web drawings. The service also enables data-connected Microsoft Visio 2010 Web drawings that can be refreshed and updated from various data sources while published on a SharePoint Server site. For example, a shape can display the number of units currently at a specified stage in a process, or can configure color when a number goes over or under a specified threshold.

The following Visio Services scenario assumes that a Web application exists and that NTLM authentication is configured as described in Scenario 1: Core Configuration.

Step	For more information, see:
Note Plan security for Visio Graphics Service service application. Also plan for performance and other considerations.	Plan Visio Services security (SharePoint Server 2010) Plan Visio Services deployment (SharePoint Server 2010)
If you did not run the Configuration Wizard to create service applications, you must create a Visio Graphics Service application.	Create a Visio Graphics Service service application (SharePoint Server 2010)
Set up and configure Secure Store Service for Visio Services Application in Microsoft SharePoint 2010 Products.	Data authentication for Visio Services (SharePoint Server 2010) Video: Steps for configuring Visio Services with Secure Store Video: Configuring Visio Services with the Unattended Service Account
In Visio Professional or Premium, create a data-connected Web diagram. The Data Selector Wizard resembles the wizard used in Excel Services.	Import data from Excel, SQL Server, SharePoint sites, and other external sources
Publish the Visio Web diagram to SharePoint Server 2010.	Publish a diagram as a Web drawing

Sdílet prostřednictvím