Connecting to data from SharePoint Server 2010 BI Apps

Note: The information in this article applies to Most of Microsoft SharePoint BI Related Services see here

Service application	Secure Store	Data connections
PerformancePoint Services	Unattended Service Account only.	Always made by using a PPSDC file.
Excel Services	Secure Store Target Application can be specified in ODC file or embedded in XLSX file. When no Target Application is embedded or specified in an ODC file, the Unattended Service Account is used.	Embedded in spreadsheet or specified in an ODC file. ODC files must be edited in Excel 2010.
Visio Services	Secure Store Target Application can be specified in ODC file. When no ODC file is used or when ODC file does not specify a Target Application, the Unattended Service Account is used. Anytime non-Integrated Windows authentication is used, the unattended account is required except if the ODC file specifies a different target application.	Embedded in Web drawing or specified in an ODC file. Limited support for complex queries. ODC files must be edited in Excel 2010. (Visio 2010 cannot edit ODC files.)

Visio Services in Microsoft SharePoint Server 2010 supports data-connected Web drawings connected to various data sources, including the following:

Data hosted within the SharePoint farm, such as a Microsoft Excel workbook or a SharePoint list.

External data, such as Microsoft SQL Server data, or an OLE DB or ODBC data source. (Visio Services cannot parse complex SQL queries. If you attempt to use an ODC file containing a complex query, Visio Services may be unable to run the query and retrieve the data.)

Retrieving data from a data source requires that a user be authenticated by the data source and then authorized to access the data that is contained therein. In the case of a Web drawing, Visio Services will authenticate to the data source on behalf of the user who is viewing it in order to refresh the data to which the drawing is connected.

The following data sources are supported in Microsoft Visio but not in Visio Services:

Access databases

Excel workbooks not hosted on SharePoint Server

OLAP

Web drawings that are connected to data hosted within the SharePoint farm, including the following:

Excel workbooks residing in a document library

Data in SharePoint lists

Connecting to Excel workbooks

Excel Services must be provisioned correctly and configured on the SharePoint farm.

The workbook must be hosted on the same farm as the Web drawing.

The Web drawing viewer must have at least "read" permissions to the Excel workbook.

No other configuration steps are required to enable this kind of data connection.

As part of connecting to an Excel workbook, Visio Services requests that Excel Services refresh the workbook if it contains connections to external data. In this case, the drawing viewer’s identity is passed on to Excel Services so that Excel Services can authenticate to underlying data sources to refresh the workbook.

Connecting to SharePoint lists

Visio Services uses the Web drawing viewer’s SharePoint Server credentials to connect to a SharePoint list. For the authentication operation to succeed the following conditions must be met:

• The SharePoint list must be hosted on the same farm as the Web drawing.
• The Web drawing viewer must have at least "read" permissions to the SharePoint list.
• No other configuration steps are required to enable this kind of data connection.
• Connecting to external data

Visio Services can connect to various external data sources, including SQL Server, OLE DB/ODBC, and custom data providers. To connect to the data source, Visio Services uses a specific data provider for each data source. As a security measure, Visio Services must explicitly trust data providers before they can be used and they are built into SharePoint already.

Connecting to a Microsoft SQL Server data source can be done by using either:

Windows authentication

SQL Server Authentication

Visio Web drawings use one of two kinds of connections:

Embedded connections

Linked connections

Embedded connections are stored as part of the Visio Web drawing. Linked connections are stored externally to a Web drawing in Office Data Connection (ODC) files. To use a linked connection, a Web drawing must reference an .odc file that is also stored in the same farm as the Web drawing. Each data connection consists of:

A connection string

A query string

An authentication method

Optionally, some metadata required to retrieve external data

Each kind of connection has its advantages and drawbacks discussed here; choose the one that best suits your scenario.

Connection type	Embedded connections	ODC files
Data sources supported	SQL Server (only supports Kerberos delegation and the Unattended Service Account) OLE DB/ODBC Excel workbooks SharePoint lists Custom Data Providers	SQL Server (supports all authentication methods) OLE DB/ODBC
Advantages	All connection information is stored in the Web drawing. Embedded connections require little administrative overhead to support. Embedded connections are easy to create.	Linked connections can be centrally stored, managed, audited, shared and access to them controlled by using a data connection library. Drawing authors can use existing connections without having to create queries and connection string. If the data connection details for a data source change, an administrator only need update one ODC file. With that change, all Web drawings that refer to the ODC file will use the updated connection information when the next refresh occurs. (An example of this scenario is when the database server is moved or the database name is changed.)
Drawbacks	If the data connection details for a data source change, all Web drawings with embedded connections to that data source will have to be republished with updated connection information. Embedded data connections are more difficult to audit by SharePoint administrators.	Creating a linked connection must be done by using Excel. Linked connections may require the help of a SharePoint administrator to share, manage and secure. Linked connections are saved in clear text and may contain database passwords. Extra care must be taken to help secure these files.

Choose a linked data connection, by using an ODC file, for scenarios in which you must have a data connection to an enterprise-scale role out in which they will be shared across many users and in which administrator control of the connection is important. Choose an embedded connection for scenarios in which you have to have a quick data connection to a small or file-based data source that will only be used by some users.

ODC files must first be created in Excel and exported to SharePoint Server before it can be used with Visio Services, this does not apply to Embedded Connections.

ODC files can be stored in a data connection library, a special kind of SharePoint document library. Centralizing data connections in such a document library has several advantages:

Administrators can restrict write access to a data connection library to trusted data connection authors to ensure that only well tested and secure data connections are used by Web drawing authors.

Administrators have a single location to manage data connections for a large group of users.

Administrators can easily approve, audit, revert and manage data connection files by using document library versioning and workflow features.

Data connection libraries can be reused across other Office applications such as Excel, Excel Services, Microsoft InfoPath 2010, InfoPath Forms Services, and Microsoft Word.

End-users only have a single location to find drawing data, reducing confusion and user training.

How to Create a Data connection Library

A Data Connection Library in Microsoft SharePoint Server 2010 is a library that can contain two kinds of data connections: an Office Data Connection (ODC) file or a Universal Data Connection (UDC) file. Uploading data connection files to a Data connection library enables the data sources described by these files to retrieve and submit information.

Browse to an SharePoint Server 2010 site on which you have at least Design permissions. If you are on the root site, create a new site before you continue with the next step.

On the Site Actions menu, click More Options.

On the Create page, click Library under Filter By, and then click Data Connection Library.

On the right side of the Create page, type a name for the library, and then click the Create button.

Copy the URL of the new data connection library.

How to use Excel to Create an Office Data Connection (ODC) file

By using the Connection Properties dialog box or the Data Connection Wizard create an (ODC) file (.odc).

Create a new connection to the data source. For more information, see the following help topics:

Exchange (copy, import, export) data between Excel and Access

Import or export text files

Connect to (import) an OLAP database

Or use an existing connection. Save the connection information to a connection file by clicking Export Connection File on the Definition tab of the Connection Properties dialog box to display the File Save dialog box, and then save the current connection information to an ODC file. For more information, see Connection properties.

For more information, see Connect to (Import) external data.

Windows authentication

Windows authentication requires that Visio Services present to SQL Server a set of Windows credentials. This kind of credential is common on Windows networks and is the same credential used to log on to computers on a Windows domain or to connect to a computer that is running Exchange Server. Windows credentials are considered the most secure and manageable means of controlling access to SQL Server databases. However, one obstacle to using Windows authentication with Visio Services is the Windows double hop security measure, wherein a user’s credentials cannot be passed across more than one computer in a Windows network. Given that Visio Services is a multi-tiered system, special authentication methods are required for Visio Services to retrieve data on behalf of the end-user.

The authentication method to choose depends on various factors as outlined in the following table. Choose the one that best suits your scenario.

Authentication method	Kerberos delegation	Secure Store	Unattended Service Account
Description	Using constrained Kerberos delegation, the drawing viewer’s Windows credentials are sent to the data source directly.	Using the Secure Store Service, the viewer’s Windows credentials are mapped to another set of credentials specified in a Secure Store target application.	Using the Secure Store Service, all viewers are mapped to a unique set of credentials called the Unattended Service Account that is stored in a specific Secure Store target application specified in Visio Services Global Settings.
Data connection credentials	The Windows credentials of the Web drawing viewer.	The credentials specified in the Secure Store target application.	The credentials of the Unattended Service Account.
Advantages	The Kerberos protocol is an industry standard in credentials management. Kerberos ties into the existing Active Directory infrastructure. Kerberos delegation permits auditing of individual accesses to a data source. Given that the Web drawing viewer’s identity is known, Web drawing creators can embed personalized database queries into Web drawings.	The Secure Store Service is part of SharePoint Server and is easier to configure than Kerberos. Mappings are flexible: a user can be mapped either 1-to-1 or many-to-1. Non-Windows credentials can be used to connect to data sources that do not accept Windows credentials. Mappings created for Visio can be re-used by other business intelligence applications such as Excel Services.	The Unattended Service Account is the easiest authentication method to deploy and setup. The Unattended Service Account does not require much administrative overhead.
Drawbacks	Additional administrative effort required to configure for SharePoint Server and Visio Services.	Establishing and managing mapping tables requires some administrative overhead. Secure Store permits limited auditing. In the many-to-1 scenario, individual incoming users are mapped into the same credentials through a target application, effectively blending them into one user.	Given that everyone is mapped to the same credentials, an administrator cannot distinguish who accessed a data source.
For the authentication operation to succeed …	Kerberos delegation must be set up on a SharePoint farm.	The Secure Store Service must be provisioned and configured on the Farm. It must also contain appropriate mapping information for a particular incoming user. Additionally the mapping information may need to be updated periodically to reflect password changes on the mapped account.	The Secure Store Service must be provisioned and configured on the Farm. It must also contain appropriate the credentials for the Unattended Service Account. Additionally the mapping information may need to be updated periodically to reflect password changes on the mapped account. Visio Services must be configured to use Unattended Service Account.

Kerberos delegation

Choose Kerberos delegation for secure and fast authentication to enterprise-scale relational data sources that support Windows authentication. For information about configuring Kerberos delegation, see:

Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products⁵

Configure Kerberos authentication (SharePoint Server 2010)⁶

Secure Store

Choose Secure Store for authentication to enterprise-scale relational data sources that may or may not support Windows Authentication. Secure Store is also useful in scenarios in which you want to control user credential mappings.

For information about using Secure Store with Visio Services, see Secure Store for Business Intelligence service applications⁷.

This demonstration shows the steps for configuring Visio Services with Secure Store.

Secure Store Service

Secure Store is a feature in SharePoint Server 2010 that helps provide access to data outside SharePoint Server 2010 (for example, SQL Server data) by allowing a Business Intelligence service application to use a set of credentials with data access on behalf of a SharePoint Server 2010 user who is attempting to access that data. Such use of credentials by Business Intelligence service applications on behalf of users is called impersonation.

Secure Store provides this mapping between Business Intelligence services applications, users, and credentials through the use of a Target Application. A Secure Store Target Application is a collection of metadata that specifies which users shall be allowed access to a particular set of credentials that a Business Intelligence service application will use for impersonation when accessing external data. This metadata is stored in the Secure Store database along with the credentials themselves, which are encrypted.

Secure Store Target Applications can be used in many ways within SharePoint Server 2010, but for the purposes of SharePoint Server 2010 Business Intelligence scenarios, Target Applications consist of the following settings, configurable by the Farm Administrator:

Administrators   Target Application Administrators are users who have privileges to administer a given Secure Store Target Application. This can be the Farm Administrator or a specific user or users, depending on your needs. For Target Applications created by PerformancePoint Services, the Administrator is configured automatically by PerformancePoint Services and the user configuring the Unattended Service Account is added as the Administrator.

Members   The Members of a Target Application are the users on behalf of whom the Business Intelligence Service Application will impersonate the Target Application Credentials when it accesses external data. This could be a single user, multiple users, or an Active Directory group. Members are also referred to as Credential Owners. For Target Applications created by PerformancePoint Services, the service account used by the PerformancePoint Services application pool is used as the Member.

Credentials   Target Application Credentials consist of an Active Directory account with direct access to data sources. (You must grant the required data access to this account directly — access to external data sources is not controlled by SharePoint Server 2010. This should be a low privileged account that only allows data access.) It is this account that is impersonated by Business Intelligence service applications to give users access to data.

The Administrators, Members, and Credentials are configurable by the Farm Administrator directly through Secure Store for Excel Services and Visio Services. For PerformancePoint Services, these values are configured through the PerformancePoint Service Application Settings and should not be modified through Secure Store.

Visio Services and Excel Services can use Secure Store using one of two methods:

Specified Target Application   A specific Target Application is specified by the Excel worksheet or the Visio Web drawing. When a user accesses the worksheet or Web drawing, Secure Store uses the credentials associated with that Target Application for data access. For Visio Services, this Target Application must be specified using an ODC file that is hosted on SharePoint Server 2010.

No specified Target Application (Unattended Service Account)   No Target Application is specified by the Excel worksheet or the Visio Web drawing. When a user accesses the worksheet or Web drawing connected to an external data source, Secure Store uses the Target Application specified in the Global Settings of Excel Services or Visio Services. When a Target Application is specified globally for a Business Intelligence service application, the Target Application Credentials are referred to as the Unattended Service Account.

PerformancePoint Services cannot specify a specific Secure Store Target Application — it can only use Secure Store with the Unattended Service Account.

The basic sequence of events that occurs is as follows:

A SharePoint Server 2010 user accesses a data-connected object such as an Excel Services 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.

If the user is a Member of that Target Application, the credentials stored in the Target Application are returned and the Business Intelligence Service Application impersonates the credentials while accessing the data.

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

What is The Unattended Service Account

Unattended Service Account refers to the credentials of a Secure Store Target Application that is specified in the global settings of a Business Intelligence service application. This Target Application is used to provide data access to users when another authentication method is not specified. For Visio Services, the Unattended Service Account is required any time that Integrated Windows authentication is not used, even if additional connection information is provided in the connection file (for example, a SQL Authentication string).

Using the Unattended Service Account

For ease of configuration the Visio Graphics Service provides a special configuration where an administrator can create a unique mapping where all users are mapped into to a single set of credentials.

This account, known as the Unattended Service Account, must be a low-privilege Windows domain account. Visio Service impersonates this account when it connects to a data source on behalf of a Web drawing viewer.

It is a best practice to give this account as few network permissions as possible, typically only access to log into the network and access the data source you want to have users connect to. For best security, be sure that the Unattended Service Account does not have access to the SharePoint Configuration and Content databases.

The Unattended Service Account is used by Visio Services:

When an ODC file specifies the use of the Unattended Service Account for either Windows or SQL Server Authentication

When no ODC is used, and Kerberos authentication fails

The unattended account can be a local computer account of type Windows. If the unattended service account is configured as a local computer account, ensure that the configuration is identical on every application server running Visio Services. For manageability reasons, it is best practice to use a domain account

Choose the Unattended Service Account when connecting to small ad-hoc deployments in which security is less important or for which speed of deployment is essential.

For information about using the Unattended Service Account with Visio Services, see Secure Store for Business Intelligence service applications⁷.

This demonstration shows the steps for configuring Visio Services with the Unattended Service Account.

SQL Server Authentication

SQL Server Authentication requires that Visio Services present a SQL Server user name and password to a SQL Server data source to authenticate. Visio Services extracts this username and password from the data connection’s connection string and passes it to the data source.

To reduce security risks, Visio Services impersonates the Unattended Service Account when connecting to such a data source.

Authentication against OLEDB/ODBC data sources

Authentication to third party data sources typically requires that Visio Services present a user name and password to a data source. Like SQL Server Authentication, Visio Services extracts this user name and password from the data connection’s connection string and passes them to the data source.

To reduce security risks, Visio Services impersonates the Unattended Service Account when connecting to such a data source.

Data refresh

Visio Services supports refreshing drawings connected to one or more of the following data sources:

SQL Server

SharePoint lists

Excel workbooks hosted in SharePoint Server

Oracle 9i, 9iR2, 10g, 10gR2, 11g, 11gR2, and DB2 9.2

External data refresh is the result of the following set of steps through Visio Services.

Creating a drawing:   A drawing author uploads a data-connected Web drawing to SharePoint Server 2010.

Triggering Refresh:   The drawing viewer triggers refresh on a data-connected Web drawing.

Data Connections:   Visio Services retrieves data connection information for each external data source in the drawing.

Trusted Data Providers:   Visio Services checks to see if there is a trusted data provider it can use to retrieve data.

Authentication:   Visio Services authenticates into the data source and retrieves the requested data on behalf of the drawing viewer.

Drawing Refresh:   Visio Services updates the Web drawing based on the data source data and returns it to the viewer.

Refresh can be triggered in one of following ways from within the browser:

The end-user opens the Web drawing.

The end-user clicks on the refresh button on an already open Web drawing.

The end-user loads a page that contains the Visio Web Access Web part which was configured to refresh automatically by a site designer.

A SharePoint site designer must place the Visio Web Access Web part on a page and configure it to refresh periodically.

Refresh can also be triggered in third party solutions by calling through JavaScript the vwaControl.Refresh() method of the Visio Web Access Web Part’s Mash-up API. For more information, see Customizing Visio Web Drawings in the Visio Web Access Web Part¹¹ (http://go.microsoft.com/fwlink/?LinkID=196503) in the MSDN Library Online.

If there are no previously cached versions of this Web drawing, any of these actions will trigger a refresh and update the Web drawing. For information about configuring cache settings for Visio Services, see Configure Visio Graphics Service global settings (SharePoint Server 2010)¹².