Chapter 23

SQL Server 2012 SharePoint 2010 Integration

WHAT’S IN THIS CHAPTER

• Advanced Reporting and Self Service BI Capabilities
• Supporting SharePoint as a DBA
• Managing Data in a SharePoint Environment

With the advent of all the new features discussed in preceding chapters, Microsoft is leveraging previous improvements to SharePoint to enable increased SQL Server 2012 capabilities around business intelligence, performance, and office integration. Users demand faster access to changing data, and the business intelligence stack in SQL Server 2012 delivers that with some exciting new enhancements focused specifically at integrating with users’ experience in SharePoint 2010.

SharePoint 2010 leverages the power and scalability of SQL Server to drive servicing content, configuration data, and metadata about users and security. The major area of integration for SharePoint 2010 is databases that support the service applications. This chapter dives deeper into this improvement, proving a look into the features of SQL Server that interact with and require SharePoint to experience their full functionality.

COMPONENTS OF INTEGRATION

When you think about integration in the SQL Server and SharePoint world, you are looking at service applications inside of SharePoint that interact with products or features in the SQL Server ecosystem. This integration has many components including service applications, SQL Server features, and new Reporting Services features such as Power View. The following sections cover each component in greater detail.

PowerPivot

PowerPivot is a free versatile self-service add-in for Excel. This add-in is used for working with large amounts of data within Excel and is great for BI professionals and also for DBAs working with performance data and other counters. The PowerPivot add-in in for Excel provides a lot of the capabilities of Analysis Services within Excel from a data sorting and manipulation perspective. The functionality of PowerPivot is greatly extended when used with SharePoint to provide an extension of personal BI, sometimes referred to as team BI. In either scenario use case PowerPivot provides users a simple, familiar interface for slicing data without the need for consolidating data into a data warehouse. Even with collaboration in SharePoint being the end goal, PowerPivot documents will be created inside Excel. In this PowerPivot environment, data will be imported, compressed by the VertiPaq engine, relationships built, KPIs created, and charts and graphs created.

When the initial creation of the PowerPivot document has been completed, it is saved as an Excel 2010 file just like any other Excel workbook. At this point the workbook will be uploaded to a SharePoint site for consumption by others inside the organization.

Using Excel with SharePoint

There are several requirements for setting up PowerPivot for SharePoint. This includes an Excel Services application to render the workbook in the browser for the user to consume. Additionally, you need an Analysis services instance for PowerPivot workbooks to run interactive queries. The Analysis Services instance is the main difference between the client and SharePoint versions of PowerPivot. On a client workstation analysis services runs within the context of the Excel application. In SharePoint analysis services requires their own instance.

Installation

Before installing PowerPivot, the SSAS instance name “PowerPivot” must be available. The easiest way to get PowerPivot up and running is on a new SharePoint farm installation; although you can add it after by doing the SharePoint installation and indicating you are doing the setup for an existing farm.

To install PowerPivot for SharePoint, follow these steps:

SharePoint Configuration

After you complete all the necessary SQL Server installation steps to create the PowerPivot instance (as described in the previous section) and after the farm configuration has been run, you need to create a new service application.

You must also set up an unattended service account to handle things such as data refresh. The security for this application account is very specific for the environment of your organization. Treat this as a regular application account that needs access to read data from your data source. The data source will likely be the SQL Server Analysis ServicesPowerPivot Instance. To get started, set aside a location on the site to store the PowerPivot workbooks, which is known as a PowerPivot gallery. To create a new one, either create a site using the Business Intelligence Center or click Site Actions More Options, and search for the PowerPivot Gallery.

From this point PowerPivot data refresh in SharePoint can be scheduled and restricted, the history retention settings can be set, workbook performance and use can be monitored all right from inside central administration.

Reporting Services

Reporting Services continues to be an integral part of the Microsoft Business Intelligence stack. With SQL Server 2012 a massive overhaul occurred on the Reporting Services SharePoint integration side. The main enhancement comes by way of a new SharePoint Service application for Reporting Services. Anyone who has configured Reporting Services for SharePoint integrated mode can appreciate this change.

Prior to SQL Server 2012

With SQL Server 2008 R2 and previous editions, Reporting Services was required to be configured as a part of the SQL Server installation and then integrated into SharePoint. This was accomplished by first creating an instance of Reporting Services, configured to run in SharePoint integrated mode instead of native mode.

One issue, although easily fixed, that would often arise is the need for the Reporting Services database to be switched from a native mode configuration to a SharePoint integrated configuration. This change had to be made through the Reporting Services Configuration Manager. Consequently, other settings would need to be managed here as well including the report server URL, the execution account, and email settings. Meanwhile all reports would be managed on the SharePoint side.

After the Windows Service was correctly configured, additional setup needed to be completed on the SharePoint Central Administration side. This final step was in place to tell SharePoint where to look for the Reporting Services instance as well as the authentication for connecting to it. At this point reports were ready to be deployed and viewed inside SharePoint.

SQL Server 2012 and Beyond

A lot has changed with SQL Server 2012; Reporting Services is now consolidated in with the rest of the SharePoint shared services. The biggest requirements for setting up Reporting Services 2012 in SharePoint integrated mode is SharePoint 2010 Service Pack 1. The first major difference in setup is now two options need to be selected on the Feature Selection page of the SQL Server installation: Reporting Services — SharePoint and Reporting Services Add-In for SharePoint Products. Following the completion of the installation, all further setup is handled within SharePoint.

Much like configuring other service applications such as Excel Services, in SharePoint 2010 Central Administration click on Manage Service Applications, click on New in the top left corner, and select SQL Server Reporting Services Service Application. All the settings that would previously have been entered in the Reporting Services Configuration manager can now be entered inside SharePoint. This includes specifying an application pool, a corresponding security account, and the location of the Reporting Services database.

While undoubtedly users can appreciate the ease provided by a consolidated installation and configuration process, this new approach provides even more benefits. As mentioned previously, the reporting databases have their location specified inside SharePoint Central Administration. This is advantageous because they can easily be placed on any server, including on the same instance as the SharePoint content databases, without the need to go outside the SharePoint environment. A Service Application is the name of an application that runs inside SharePoint. It is easy to set up multiple service applications for Reporting Services for use in the farm. When this happens each gets its own set of databases on the backend; they are not shared.

Improvements to SharePoint Integrated Mode in SQL Server 2012

Now that all the Reporting Services pieces for SharePoint integrated mode are contained inside SharePoint, the maintenance model will be simplified. Scale out can also be handled on the SharePoint side, rather than worrying about scaling Reporting Services and SharePoint.

Among the other enhancements is the dramatically improved report performance. Part of the basis for this improvement is that SharePoint no longer has to go to a separate Reporting Services server for information — it is all managed by SharePoint.

For farms with multiple servers, it is no longer required to configure the rsreportserver.config file on each server because this information is stored in the configuration database. Set the values once and all the machines can pick up the values. For information on how this affects licensing, visit the Microsoft Web site because the pricing model is always subject to change.

Power View

Power View is a new feature introduced with SQL Server 2012 in combination with SharePoint 2010 to provide interactive ad-hoc reporting in real time. Power View works with Developer, Enterprise, and Business Intelligence versions of SQL Server 2012 and requires an Enterprise edition of SharePoint 2010 and the Reporting Services add-in for SharePoint to be enabled.

What Is Power View

Power View provides a new innovative way to interact with your data by way of Silverlight renderings. These reports are developed and fed from PowerPivot workbooks deployed to a SharePoint PowerPivot gallery or the new tabular model deployed to an Analysis Services (SSAS) 2012 instance.

This is not meant to be a replacement for traditional Reporting Services reports or report builder. Power View is meant for ad-hoc data exploration, which is to say moving through your data and looking at how it relates across your business. The traditional file created by the current Reporting Services tools is a report definition file (RDL) and cannot be edited or viewed in Power View. Because Power View is also used only inside SharePoint, it is not applicable for use in every situation depending on the reporting needs. Each of these technologies has its place in the reporting environment.

Presentation Layer

Unlike a traditional Reporting Services report, Power View is an “always on” presentation type that doesn’t require you to preview reports. The data is always live. There is also the added advantage of exporting Power View reports to PowerPoint where each individual report becomes its own slide. The full-screen viewing and PowerPoint slides work in the same way. Both enable interaction with data such as applying filters that the builder added or using visualizations. However, neither enables further development. In this state, the data is interactive, but you cannot add new filters or visualizations.

Some of the visualizations available in Power View follow:

• Table/Matrix
• Chart
• Bubble Chart
• Scatter Chart
• Cards
• Tiles

Creation

You initiate all Power View report creation from tabular model elements, xlsx files, or connection files pointing to BISM models, within SharePoint 2010 document libraries or PowerPivot Galleries. Clicking the arrow next to an applicable element can reveal an option for Create Power View Report. Selecting this option opens the Power View Designer. You can edit existing reports by clicking the drop-down arrow next to a report and selecting Edit in Power View.

When saving Power View reports, they are in RDLX format. The option to save corresponding images is also available. When exporting to PowerPoint, if you do not use the option to save images, only placeholders appear.

Service Application Architecture

Service applications refer to the new service application architecture in SharePoint 2010. Deep SharePoint architecture discussions are beyond the scope of this book. For more details see http://technet.microsoft.com/en-us/library/cc560988.aspx.

DATA REFRESH

As a SQL professional your job focuses on the ability to get data in and out quickly and efficiently. This is important for the performance of your data environment and the applications serviced by it. You’ve already seen a significant amount of DBA related items such as PowerPivot and Power View implementations in the previous section, but more is added here. This section focuses on managing and controlling data refresh with your reports and data in SharePoint. There are many possible data sources, so it is best to focus on those that typically come out of SQL Server, such as the following.

• Excel Services
• PerformancePoint Services
• Visio Services
• PowerPivot

Using Data Connections in Excel

Every Excel workbook that uses external data contains a connection to a data source. Connections consist of everything required to establish communications with and retrieve data from an external data source. These requirements include the following:

• Connection string, which specifies which server to connect to and how to connect to it
• Query, which is a string that specifies what data to retrieve
• Any other specifics required to get the data such as impersonation, proxy mode etc.
• Embedded and linked connections

Excel workbooks can contain embedded connections and can link to external connections. Embedded connections are stored internally as part of the workbook. External connections are stored in the form of Office Data Connection (ODC) files that can be referenced by a workbook.

Excel embedded and external connections function the same way. Both correctly specify all the required parameters to connect to data successfully. External connection files can be centrally stored, secured, managed, and reused. They are a good choice when planning an overall approach to getting a large group of users connected to external data. For more information, see http://technet.microsoft.com/en-us/library/ff604007.aspx#section4.

For a single connection, a workbook can have both an embedded copy of the connection information and a link to an external connection file. The connection can be configured to always use an external connection file to refresh data from an external data source. In this case, if the external connection file cannot be retrieved, or if it does not establish a connection to the data source, the workbook cannot retrieve data to refresh what is there. The results of this vary based on the data connection settings. If the connection is not configured to use only an external connection file, Excel attempts to use the embedded copy of a connection. If that fails, Excel attempts to use the connection file to connect to the external data source.

For security purposes, Excel Services can be configured to enable only connections from connection files. In this configuration, all embedded connections are ignored for workbooks loaded on the SharePoint server, and connections are tried only when there is a link to a valid connection file that is trusted by the server administrator. For more information, see http://technet.microsoft.com/en-us/library/ff604007.aspx#section4.

Data Providers

Data providers are drivers that applications (such as Excel and Excel Services) use to connect to specific data sources. For example, a special MSOLAP data provider can connect to Microsoft SQL Server 2008 Analysis Services (SSAS). The data provider is specified as part of the connection string when you connect to a data source.

Data providers handle queries, parsing connection strings, and other connection-specific logic. This functionality is not part of Excel Services. Excel Services cannot control how data providers behave.

Any data provider used by Excel Services must be explicitly trusted by Excel Services. For information about how to add a new data provider to the trusted providers list, see http://technet.microsoft.com/en-us/library/ff191200.aspx.

By default, Excel Services trusts many well-known data providers. In most cases, you do not have to add a new data provider. Data providers are typically added for custom solutions.

Authentication to External Data

Database servers require a user to be authenticated, that is, identify oneself to the server. The next step is authorization, communicating to the server the permitted actions associated with the user. Authentication is required for the data server to perform authorization, or to enforce security restrictions that prevent data from being exposed to anyone other than authorized users.

Excel Services must communicate to the data source which user is requesting the data. In most scenarios, this is the user viewing an Excel report in a browser. This section explains authentication between Excel Services and an external data source. Authentication at this level is shown in Figure 23-1. The arrow on the right side shows the authentication link from an application server that runs Excel Calculation Services to an external data source.

Excel Services supports the following authentication options:

• Windows authentication: Excel Services uses Integrated Windows authentication and attempts to connect to the data source by using the Windows identity of the user who displays the workbook.
• Secure Store Service (SSS): Excel Services uses the credentials associated with the specified Secure Store target application.
• None: Excel Services impersonates the unattended service account and passes the connection string to the data source.

The authentication option is configured in Microsoft Excel and is a property of the external data connection. The default value is Windows Authentication.

Integrated Windows Authentication

If you choose the Windows Authentication option, Excel Services attempts to pass the Windows identity of the user viewing the Excel workbook to the external data source. Kerberos delegation is required for any data source located on a different server than the server where Excel Calculation Services runs, if that data source uses Integrated Windows authentication.

In most enterprise environments, Excel Calculation Services runs on a different computer from the data source. This means that Kerberos delegation (constrained delegation is recommended) is required to enable data connections that use Windows authentication. For more information about how to configure Kerberos constrained delegation for Excel Services, see http://technet.microsoft.com/en-us/library/ff829837.aspx.

Secure Store Service

Secure Store is a SharePoint Server 2010 service application used to store encrypted credentials in a database for use by applications to authenticate to other applications. In this case, Excel Services uses Secure Store to store and retrieve credentials for use in authenticating to external data sources.

If you choose the SSS option, you must then specify the application ID of a Secure Store target application. The specified target application serves as a lookup used to retrieve the appropriate set of credentials. Each target application can have permissions set so that only specific users or groups can use the stored credentials.

When provided with an application ID, Excel Services retrieves the credentials from the Secure Store database for the user who accesses the workbook (either through the browser, or using Excel Web Services). Excel Services then uses those credentials to authenticate to the data source and retrieve data.

For information about how to use Secure Store with Excel Services, see http://technet.microsoft.com/en-us/library/ff191191.aspx.

None

When you select the None option, no credential retrieval occurs, and no special action is taken for authentication for the connection. Excel Services does not try to delegate credentials and does not try to retrieve credentials stored for the user from the Secure Store database. Instead, Excel Services impersonates the unattended service account and passes the connection string to the data provider that handles authentication.

The connection string may specify a username and password to connect to the data source or may specify that the Windows identity of the user or computer issuing the request be used to connect to the data source. In either case, the unattended account is impersonated first, and then the data source connection is made. The connection string and the provider determine the authorization method. Additionally, authorization can be based on either the credentials found in the connection string or the impersonated unattended account’s Windows identity.

Excel Services Security and External Data

Excel Services manages workbooks and external data connections by using the following:

• Trusted file locations: Locations designated by an administrator from which Excel Services can load workbooks
• Trusted data connection libraries: SharePoint Server 2010 data connection libraries that have been explicitly trusted by an administrator from which Excel Services can load data connection files
• Trusted data providers: Data providers that have been explicitly trusted by an administrator
• Unattended service account: A low-privileged account that Excel Services can impersonate when it makes data connections

Trusted File Locations

Excel Services loads workbooks only from trusted file locations. A trusted file location is a SharePoint Server location, network file share, or Web folder address that the administrator has explicitly enabled workbooks to be loaded from. These directories are added to a list that is internal to Excel Services. This list is known as the trusted file locations list.

Trusted locations can specify a set of restrictions for workbooks loaded from them. All workbooks loaded from a trusted location adhere to the settings for that trusted location. Following is a short list of the trusted location settings that affect external data:

• Allow External Data: Defines how external data can be accessed.
• No Data Access Allowed (Default): Only connection files in a trusted SharePoint Server 2010 data connection library are allowed.
• Warn on Refresh: Defines whether to show the query refresh warnings.
• Stop When Refresh on Open Fails: Defines whether to fail the workbook load if external data does not refresh when the workbook opens. This is used in scenarios in which the workbook has cached data results that will change depending on the identity of the user viewing the workbook. The objective is to hide these cached results and make sure that any user who views the workbook can see only the data specific to that user. In this case, if the workbook is set to refresh on open and the refresh fails, the workbook does not display.
• External Data Cache Lifetime: Defines external data cache expiration times. Data is shared among many users on the server to improve scale and performance, and these cache lifetimes are adjustable. This accommodates scenarios in which query execution should be kept to a minimum because the query might take a long time to execute. In these scenarios, the data often changes only daily, weekly, or monthly instead of by the minute or every hour.

Trusted Data Connection Libraries and Managed Connections

A data connection library is a SharePoint Server 2010 library designed to store connection files, which can then be referenced by Office 2010 applications, such as Excel and Microsoft Visio. Excel Services loads only connection files from trusted SharePoint Server 2010 data connection libraries. A trusted data connection library is a library that the server administrator has explicitly added to an internal trusted list. Data connection libraries enable you to centrally manage, secure, store, and reuse data connections.

Managing Connections

Because workbooks contain a link to the file in a data connection library, if something about the connection changes (such as a server name or a Secure Store application ID), only a single connection file must be updated instead of potentially many workbooks. The workbooks can obtain the connection changes automatically the next time that they use that connection file to refresh data from Excel or Excel Services.

Securing Connections

The data connection library in a SharePoint library supports all the permissions that SharePoint Server 2010 does, including per-folder and per-item permissions. The advantage that this provides on the server is that a data connection library can become a locked-down data connection store that is highly controlled. Many users may have read-only access to it. This enables them to use the data connections, but they can be prevented from adding new connections. By using access control lists (ACLs) with the data connection library, and letting only trusted authors upload connections, the data connection library becomes a store of trusted connections.

You can configure Excel Services to load connection files only from data connection libraries explicitly trusted by the server administrator and block loading of any embedded connections. In this configuration, Excel Services uses the data connection library to apply another layer of security around data connections.

You can use data connection libraries together with the new Viewer role in SharePoint Server 2010 that enables those connections to refresh workbooks rendered in a browser by Excel Services. If the Viewer role is applied, users cannot access the connection file contents from a client application, such as Excel. Therefore, the connection file contents are protected but still can be used for workbooks refreshed on the server.

Storing Connections

Storing data connections is another important role for document libraries. These data connections are stored as objects like documents or images in a library and can be accesses by services and reports throughout the farm depending on permissions.

Reusing Connections

Users can reuse connections created by other users and create different reports that use the same data source. You can have the IT department or a business intelligence expert create connections, and other users can reuse them without understanding the details about data providers, server names, or authentication. The location of the data connection library can even be published to Office clients so that the data connections display in Excel or in any other client application that uses the data connection library.

Trusted Data Providers

Excel Services uses only external data providers on the Excel Services trusted data providers list. This is a security mechanism that prevents the server from using providers that the administrator does not trust.

Unattended Service Account

Excel Services runs under a highly privileged account. Because Excel Services has no control over the data provider and does not directly parse provider-specific connection strings, using this account for the purposes of data access would be a security risk. To lessen this risk, Excel Services uses an unattended service account. This is a low-privileged account that is impersonated by Excel Services if any of the following conditions are true:

• Any time that it tries a connection where the None authentication option is selected.
• Whenever the Secure Store Service (SSS) option is selected and the stored credentials are not Windows credentials.
• If the None option is selected and the unattended account does not have access to the data source, Excel Services impersonates the unattended service account and uses information stored in the connection string to connect to the data source.
• If the None option is selected and the unattended account has access to the data source, a connection is successfully established using the credentials of the unattended service account. Use caution when you design solutions that intentionally use this account to connect to data. This is a single account that potentially can be used by every workbook on the server. Any user can open a workbook with an authentication setting of None using Excel Services to view that data by using the server. In some scenarios, this might be needed. However, Secure Store is the preferred solution for managing passwords on a per-user or per-group basis.
• If the SSS option is selected and the stored credentials are not Windows credentials, Excel Services impersonates the unattended service account and then attempts to connect to the data source by using the stored credentials.
• If the Windows Authentication option is selected, or if the SSS option is selected and the stored credentials are Windows credentials, then the unattended service account is not used. Instead, Excel Services impersonates the Windows identity and attempts to connect to the data source.

PerformancePoint Data Refresh

In PerformancePoint Services you must create a connection to the data source or sources you want to use in your dashboard. All data used in PerformancePoint Services is external data, living in data repositories outside of PerformancePoint. After you establish a data connection, you can use the data in the various PerformancePoint feature areas.

PerformancePoint supports both tabular data sources including SharePoint Lists, Excel Services, SQL Server tables and Excel workbooks; as well as multidimensional (Analysis Services) data sources; and also supports PowerPivot for Excel.

Tabular Data Sources

A user can create a data connection to SharePoint Lists, Excel Services, SQL Server tables, or Excel workbooks. For these kinds of data sources, you can view a sample of the data from the Dashboard Designer tool and set specific properties for the data depending how you want the data to be interpreted within PerformancePoint. For example, you can indicate which datasets should be treated as a dimension; you can specify if a dataset is to be treated as a dimension or a fact; or if you do not want the data to be included, you can select Ignore. If you decide to set the value as a fact, you can indicate how those numbers should be aggregated in PerformancePoint Services. You can also use datasets that have time values within PerformancePoint Services and use the PerformancePoint Services time intelligence features to set time parameters and create dashboard filters.

SharePoint Lists

You can use data contained in a SharePoint List on a SharePoint Site in PerformancePoint Services by creating a SharePoint List data source in Dashboard Designer. Data from SharePoint Lists can only be read but not modified. Modification to SharePoint List data must be done from SharePoint. Users may connect to any kind of SharePoint List.

Excel Services

Data in Excel files published to Excel Services on a SharePoint Site can be used in PerformancePoint Services by creating an Excel Services data source. Supported published data can be read only in PerformancePoint Services. Published parameter values can be modified from the Dashboard Designer. If you use an Excel Services parameter in calculating a KPI, it is easy to make additional changes. PerformancePoint Services supports the following Excel Services components: Named Ranges, Tables, and Parameters.

SQL Server Tables

You can create a data source connection to a SQL Server database and use the data within PerformancePoint Services. Tables and views are supported data sources within PerformancePoint Services.

Excel Workbooks

You may use the content of an actual Excel file stored in PerformancePoint as a data source in PerformancePoint Services by creating an Excel Workbook data source connection and selecting only the data to be used. The original Excel file will be independent from the PerformancePoint copy. PerformancePoint Services 2010 supports Excel 2007 and Excel 2010 workbooks as data sources.

Multidimensional Data Sources

Use data residing in a SQL Server Analysis Services multidimensional cube in PerformancePoint Services by creating a data connection to the source. PerformancePoint Services enables you to map the wanted time dimension and the required level of detail for its hierarchies to the internal PerformancePoint Services Time Intelligence.

PowerPivot for Excel

In PerformancePoint Services you can use a PowerPivot model as a data source to build your PerformancePoint Services dashboards. To use PowerPivot as a data source within a PerformancePoint Services dashboard, you must have PerformancePoint Services activated on a SharePoint Server 2010 farm and have PowerPivot for SharePoint installed. After a PowerPivot model has been created by using the PowerPivot add-in for Excel, this Excel file must be uploaded or published to a SharePoint site that has PowerPivot services enabled. Create the data source connection in Dashboard Designer using the Analysis Services data source template.

Visio Services Data Refresh

The Visio Graphics Service can connect to data sources. These include SharePoint lists, Excel workbooks hosted on the farm, databases such as Microsoft SQL Server, and custom data sources. You can control access to specific data sources by explicitly defining the data providers trusted and configuring them in the list of trusted data providers.

When Visio Services loads a data connected Web drawing, the service checks the connection information that is stored in the Web drawing to determine whether the specified data provider is a trusted data provider. If the provider is specified on the Visio Services trusted data provider list, a connection is tried; otherwise, the connection request is ignored.

After an administrator configures Visio Services to enable connections to a particular data source, additional security configurations must be made, depending on the kind of the data source. The following data sources are supported by Visio Services:

• Excel workbooks stored on SharePoint Server with Excel Services enabled
• SharePoint lists
• Databases such as SQL Server databases
• Custom data providers
• Visio Web drawings connected to SharePoint lists

There are a number of ways Visio content in SharePoint can be refreshed. Two of the most common ways are discussed in the following sections. They include Excel Services and SQL Server data.

Visio Web Drawings Connected to Excel Services

Published Visio drawings can connect to Excel workbooks hosted on the same farm as the Web drawing with Excel Services running and configured correctly. To view the Web drawing, the user must have access to both the drawing and the Excel workbook that the drawing connects to. These permissions and credentials are managed by SharePoint Server 2010.

Visio Web drawings Connected to SQL Server Databases

When a published Visio Web drawing is connected to a SQL Server database, Visio Services uses additional security configuration options to establish a connection between the Visio Graphics Service and the database.

Visio Services Authentication Methods

Visio supports several authentication types. These are listed here:

• Integrated Windows authentication: In this security model the Visio Graphics Service uses the drawing viewer’s identity to authenticate with the database. Integrated Windows authentication with constrained Kerberos delegation is more helpful for increasing security than the other authentication methods shown in this list. This configuration requires constrained Kerberos delegation to be enabled between the application server running the Visio Graphics Service and the database server. The database might require additional configuration to enable Kerberos-based authentication.
• Secure Store Service: In this security model the Visio Graphics Service uses the Secure Store Service to map the user’s credentials to a different credential that has access to the database. The Secure Store Service supports individual and group mappings for both Integrated Windows authentication and other forms of authentication such as SQL Server Authentication. This gives administrators more flexibility in defining one-to-one, many-to-one, or many-to-many relationships. This authentication model can be used only by drawings that use an Office Data Connection (ODC) file to specify the connection. The ODC file specifies the Secure Store target application that can be used for credential mapping. The ODC files must be created by using Microsoft Excel.
• Unattended Service Account: For ease of configuration the Visio Graphics Service provides a special configuration where an administrator can create a unique mapping associating all users to a single account by using a Secure Store Target Application. This mapped account, known as the unattended service account, must be a low-privilege Windows domain account that is given access to databases. The Visio Graphics Service impersonates this account when it connects to the database if no other authentication method is specified. This approach does not enable personalized queries against a database and does not provide auditing of database calls. This authentication method is the default authentication method used when you connect to SQL Server databases: If no ODC file is used in the Visio Web drawing that specifies a different authentication method, then Visio Services uses the credentials specified by the unattended account to connect to the SQL Server database.

In a larger server farm, it is likely that Visio drawings use a mix of the authentication methods described here. Consider the following:

• Visio Services supports usage of both the Secure Store Service and the unattended service account in the same farm. In Web drawings connected to SQL Server data that do not use ODC files, the unattended account is required and always used.
• If Integrated Windows authentication is selected, and authentication to the data source fails, Visio Services does not attempt to render the drawing using the unattended service account.
• Integrated Windows authentication can be used together with the Secure Store by configuring drawings to use an ODC file that specifies a Secure Store target application for those drawings that require specific credentials.

PowerPivot Data Refresh

PowerPivot data refresh is a scheduled server-side operation that queries external data sources to update embedded PowerPivot data in an Excel workbook stored in a content library.

Data refresh is a built-in feature of PowerPivot for SharePoint, but using it requires that you run specific services and timer jobs in your SharePoint farm. Additional administrative steps, such as installing data providers and checking database permissions, are often required for data refresh to succeed.

After you ensure that the server environment and permissions are configured, data refresh is ready to use. To use data refresh, a SharePoint user creates a schedule on a PowerPivot workbook that specifies how often data refresh occurs. Creating the schedule is typically done by the workbook owner or author who published the file to SharePoint. This person creates and manages the data refresh schedules for the workbooks that he or she owns. The following sections provide a list of steps you should follow in order to perform a successful PowerPivot data refresh.

Step 1: Enable Secure Store Service and Generate a Master Key

PowerPivot data refresh depends on Secure Store Service to provide credentials used to run data refresh jobs and to connect to external data sources that use stored credentials.

If you installed PowerPivot for SharePoint using the New Server option, the Secure Store Service is configured for you. For all other installation scenarios, you must manually create and configure a service application and generate a master encryption key for Secure Store Service. This is completed by performing the following steps:

Step 2: Turn Off Credential Options That You Do Not Want to Support

PowerPivot data refresh provides three credential options in a data refresh schedule. When workbook owners schedule data refresh, they choose one of these options, thereby determining the account under which the data refresh job runs. As an administrator, you can determine which of the following credential options are available to schedule owners.

• Option 1: Use the data refresh account configured by the administrator, which always appears on the schedule definition page but works only if you configure the unattended data refresh account.
• Option 2: Connect using the credentials show in Figure 23-2, which always appear on the page but work only when you enable the Allow Users to Enter the Custom Windows Credentials option in the service application configuration page. This option is enabled by default, but you can disable it if the disadvantages of using it outweigh the advantages. (See option 3.)

  FIGURE 23-2

  
• Option 3: Connect using the credentials saved in Secure Store Service, which always appear on the page but work only when a schedule owner provides a valid target application. An administrator must create these target applications in advance and then provide the application name to those who create the data refresh schedules. This option only works if this service is configured and prevents other credentials such as option 2.

PowerPivot service application includes a credential option that allows schedule owners to enter an arbitrary Windows username and password to run a data refresh job. This is the credential option is shown in Figure 23-2 (and also referred to previously in Option 2).

This credential option shown in Figure 23-2 is enabled by default. When this credential option is enabled, PowerPivot System Service generates a target application in Secure Store Service to store the username and password entered by the schedule owner. A generated target application is created using this naming convention: PowerPivot DataRefresh_<guid>. One target application is created for each set of Windows credentials. If a target application already exists that is owned by the PowerPivot System Service and stores the username and password entered by the person defining the schedule, PowerPivot System Service uses that target application rather than creating a new one.

The primary advantage to use this credential option is ease of use and simplicity. Advance work is minimal because target applications are created for you. Also, running data refresh under the credentials of the schedule owner (who is most likely the person who created the workbook) simplifies permission requirements downstream. Most likely, this user already has permissions on the target database. When data refresh runs under this person’s Windows user identity, any data connections that specify “current user” work automatically.

The disadvantage is limited management capability. Although target applications are created automatically, they are not deleted automatically or updated as account information changes. Password expiration policies might cause these target applications to become out of date. Data refresh jobs that use expired credentials will start to fail. If alerting is configured, DBA’s can get an email or test alert. When this occurs, schedule owners need to update their credentials by providing current username and password values in a data refresh schedule. A new target application is created at that point. Over time, as users add and revise credential information in their data refresh schedules, you might have a large number of auto-generated target applications on your system.

Currently, there is no way to determine which of these target applications are active or inactive, nor is there a way to trace a specific target application back to the data refresh schedules that use it. In general, you should leave the target applications alone because deleting them might break existing data refresh schedules. Deleting a target application still in use causes data refresh to fail with the message Target Application Not Found appearing in the data refresh history page of the workbook.

If you choose to disable this credential option, you can safely delete all of the target applications that were generated for PowerPivot data refresh.

Step 3: Create Target Applications to Store Credentials Used in Data Refresh

When Secure Store Service is configured, SharePoint administrators can create target applications to make stored credentials available for data refresh purposes, including the PowerPivot unattended data refresh account or any other account used to either run the job or connect to external data sources.

Recall from the previous section that you need to create target applications for certain credential options to be usable. Specifically, you must create target applications for the PowerPivot unattended data refresh account, plus any additional stored credentials that you expect would be used in data refresh operations.

Step 4: Configure the Server for Scalable Data Refresh

By default, each PowerPivot for SharePoint installation supports both on-demand queries and scheduled data refresh.

For each installation, you can specify whether the Analysis Services server instance supports both query and scheduled data refresh, or is dedicated to a specific type of operation. If you have multiple installations of PowerPivot for SharePoint in your farm, consider dedicating a server for just data refresh operations if you find that jobs are delayed or failing.

Additionally, if the underlying hardware supports it, you can increase the number of data refresh jobs that run in parallel. By default, the number of jobs that can run in parallel is calculated based on system memory, but you can increase that number if you have additional CPU capacity to support the workload.

Step 5: Install Data Providers Used to Import PowerPivot Data

A data refresh operation is essentially a repeat of an import operation that retrieved the original data. This means that the same data providers used to import the data in the PowerPivot client application must also be installed on the PowerPivot server.

You must be a local administrator to install data providers on a Windows server. If you install additional drivers, be sure to install them on each computer in the SharePoint farm that has an installation of PowerPivot for SharePoint. If you have multiple PowerPivot servers in the farm, you must install the providers on each server.

Step 6: Grant Permissions to Create Schedules and Access External Data Sources

Workbook owners or authors must have Contribute permission to schedule data refresh on a workbook. Given this permission level, they can open and edit the workbook’s data refresh configuration page to specify the credentials and schedule information used to refresh the data.

In addition to SharePoint permissions, database permissions on external data sources must also be reviewed to ensure that accounts used during data refresh have sufficient access rights to the data. Determining permission requirements requires careful evaluation on your part because the permissions that you need to grant can vary depending on the connection string in the workbook and the user identity under which the data refresh job runs. When making this determination, it is important to consider the following questions:

• Why do Existing Connection Strings in a PowerPivot Workbook Matter to PowerPivot Data Refresh Operations? When data refresh runs, the server sends a connection request to the external data source using the connection string created when the data was originally imported. The server location, database name, and authentication parameters specified in that connection string are now reused during data refresh to access the same data sources. The connection string and its overall construction cannot be modified for data refresh purposes. It is simply reused as-is during data refresh. In some cases, if you use non-Windows authentication to connect to a data source, you can override the username and password in the connection string.

  For most workbooks, the default authentication option on the connection is to use trusted connections or Windows integrated security, resulting in connection strings that include SSPI=IntegratedSecurity or SSPI=TrustedConnection. When this connection string is used during data refresh, the account used to run the data refresh job becomes the current user. As such, this account needs read permissions on any external data source accessed via a trusted connection.

• Did You Enable the PowerPivot Unattended Data Refresh Account? If yes, then you should grant that account read permissions on data sources accessed during data refresh. The reason why this account needs read permissions is because in a workbook that uses the default authentication options, the unattended account will be the current user during data refresh. Unless the schedule owner overrides the credentials in the connection string, this account needs read permissions on any number of data sources actively used in your organization.
• Are You Using Credential Option 2: Allowing the Schedule Owner to Enter a Windows Username and Password? Typically, users who create PowerPivot workbooks already have sufficient permissions because they have already imported the data. If these users subsequently configure data refresh to run under their own Windows user identity, their Windows user account, which already has rights on the database, will be used to retrieve data during data refresh. Existing permissions should be sufficient.
• Are You Using Credential Option 3: Using a Secure Store Service Target Application to Provide a User Identity for Running Data Refresh Jobs? Any account used to run a data refresh job needs read permissions, for the same reasons as those described for the PowerPivot unattended data refresh account.

Step 7: Enable Workbook Upgrade for Data Refresh

By default, workbooks created using the SQL Server 2008 R2 version of PowerPivot for Excel cannot be configured for scheduled data refresh on a Microsoft SQL Server 2012 version of PowerPivot for SharePoint. If you host newer and older versions of PowerPivot workbooks in your SharePoint environment, you must upgrade SQL Server 2008 R2 workbooks first before they can be scheduled for automatic data refresh on the server.

Step 8: Verify Data Refresh Configuration

To verify data refresh, you must have a PowerPivot workbook published to a SharePoint site. You must have Contribute permissions on the workbook and permissions to access any data sources included in the data refresh schedule.

When you create the schedule, select the Also Refresh as Soon as Possible check box to run data refresh immediately. You can then check the data refresh history page of that workbook to verify that it ran successfully. Recall that the PowerPivot Data Refresh timer job runs every minute. It can take at least that long to get confirmation that data refresh succeeded.

Be sure to try all the credential options you plan to support. For example, if you configured the PowerPivot unattended data refresh account, verify that data refresh succeeds using that option.

If data refresh fails, refer to the Troubleshooting PowerPivot Data Refresh page on the TechNet wiki for possible solutions. This can be found at http://technet.microsoft.com.

Modify Configuration Settings for Data Refresh

Each PowerPivot service application has configuration settings that affect data refresh operations. This section explains the two major ways to modify those settings.

Reschedule the PowerPivot Data Refresh Timer Job

Scheduled data refresh is triggered by a PowerPivot Data Refresh timer job that scans schedule information in the PowerPivot service application database at 1-minute intervals. When data refresh is scheduled to begin, the timer job adds the request to a processing queue on an available PowerPivot server.

You can increase the length of time between scans as a performance tuning technique. You can also disable the timer job to temporarily stop data refresh operations while you troubleshoot problems.

The default setting is 1 minute, which is the lowest value you can specify. This value is recommended because it provides the most predictable outcome for schedules that run at arbitrary times throughout the day. For example, if a user schedules data refresh for 4:15 P.M., and the timer job scans for schedules every minute, the scheduled data refresh request will be detected at 4:15 P.M. and processing will occur within a few minutes of 4:15 P.M.

If you raise the scan interval so that it runs infrequently (for example, once a day at midnight), all the data refresh operations scheduled to run during that interval are added to the processing queue all at once, potentially overwhelming the server and starving other applications of system resources. Depending on the number of scheduled refreshes, the processing queue for data refresh operations might build up to such an extent that not all jobs can complete. Data refresh requests at the end of the queue might be dropped if they run into the next processing interval.

To adjust the timer job schedule you can perform the following steps:

Disable the Data Refresh Timer Job

The PowerPivot data refresh timer job is a farm-level timer job that is either enabled or disabled for all PowerPivot server instances in the farm. It is not tied to a specific Web application or PowerPivot service application. You cannot disable it on some servers to force data refresh processing to other servers in the farm.

If you disable the PowerPivot data refresh timer job, requests that were already in the queue will be processed, but no new requests will be added until you reenable the job. Requests that were scheduled to occur in the past are not processed.

Disabling the timer job has no effect on feature availability in application pages. There is no way to remove or hide the data refresh feature in web applications. Users who have Contribute permissions or above can still create new schedules for data refresh operations, even if the timer job is permanently disabled.

Summary

SharePoint 2010 will continue to be an integral part of business intelligence within SQL Server 2012. Many enhancements make setup and maintenance much easier. The setup and configuration of PowerPivot along with Reporting Services being consolidated to a shared service lead the way in helping to make SharePoint a better tool. The addition of new features such as Power View will undoubtedly enhance the SharePoint experience for everyone who will begin using the new BISM analysis service format and continue using PowerPivot. In the end SharePoint 2010 together with SQL Server 2012 is getting better, more user-friendly, and more intelligent.