External Data Sources in SharePoint

Executive Summary: SharePoint offers the ability to pull data from a wide variety of external data sources. This article shows you can better manage the connection to an external system independently of reports and forms that could be using it by uploading Office data connection (.odc) file or Universal data connection (.udc) files to DCLs. Excel Services, InfoPath Forms, and SQL Server Reporting Services (SSRS) in SharePoint integrated mode all take advantage of SharePoint’s security model, which allows for item-level permissions to be applied, preventing unauthorized data access.

Part of SharePoint’s attractiveness is its ability to act as a framework to surface data from a wide variety of sources. Want to replace the outdated SQL Server Reporting Services (SSRS) Report Manager? Microsoft Office SharePoint Server (MOSS) 2007 lets you use SharePoint integration mode to manage reports like other library documents. Want to connect to your PeopleSoft HR application and expose personnel data along with W2 and I9 documents? MOSS Enterprise lets you use the Business Data Catalog (BDC) to manage this connection. Dealing with semi-structured data? InfoPath Forms Services might be your solution. Trying to control the proliferation of spreadsheets? With Excel Services you can render your Excel spreadsheet directly from a web browser without having to install the full Office client.

SharePoint offers a great deal of functionality for end-user applications and report designers, but don’t forget about its SharePoint Designer data connections as well as third-party and custom connections. Let’s look at how to consume and secure end-user and developer-centric data connections.

Data Connection Libraries

The settings that make up a data connection, such as connection strings and OLAP queries, can be saved in an Office data connection (.odc) file or Universal data connection (.udc) file. These data connection files can be stored and secured separately from the SSRS reports, Microsoft Excel workbooks, and InfoPath form templates that use them. MOSS provides Data Connection Libraries (DCLs) that centrally store these Office data connections for reuse. DCLs are a new type of SharePoint library (similar to a document library) that can store and manage connection files. You can use the Data Connection Wizard, available in InfoPath 2007 and Excel 2007, to manage the upload of .odc and .udc files to the DCL. Figure 1 shows a sample DCL with a .odc file used by Excel, a .odc file used by InfoPath Forms, and a Remote Data Connection (RDC) used by SSRS. (For more information about managing data connections, see "Plan data connection management."

SharePoint gives business users the ability to manage the connection to an external system independently of reports and forms that could be using it. This capability is useful both for Excel Services and InfoPath Forms Services because it prevents embedded data connections, which can become stale and difficult to troubleshoot.

InfoPath Forms Services Connections

InfoPath lets you create custom forms for data entry. This data will be stored in a SharePoint list that separates the fields and stores them in the SharePoint SQL Server content database. InfoPath’s form templates can take advantage of .udc files stored in a DCL. These data connections can be used for primary and secondary data connections, as well as for send connections. The following are some of the benefits of storing InfoPath data connections in DCLs:
• You can create the data connection once and use the connection on multiple servers and across multiple domains with both InfoPath client form templates and InfoPath Forms Services form templates.
• You can centrally publish approved data connections, modify data connections without affecting form templates, and determine which connections are safe for cross-domain access.

Figure 2 shows a sample InfoPath form retrieving an employee’s first name and last name from a secondary, read-only data source called vEmployee.

Excel Services Connections

Similar to InfoPath forms, Excel workbooks have the capability to tie to external data sources. These connections can be stored in the workbook itself or in an external .odc file or .udc file, giving end users the ability to reuse connection information and centrally administer connection details. The Microsoft article "Plan external data connections for Excel Services" goes into great detail about connections and Excel workbooks, as well as Excel Services considerations. Figure 3 shows an Excel spreadsheet pulling data from an external data source. The connection information is stored in a DCL in SharePoint.

SSRS Integration

SSRS ships out of the box with a Report Manager web application that lets you manage user access, subscriptions, and centrally controlled data sources. So why would anyone bother to integrate SSRS and SharePoint? The value add is that SharePoint lets you put your reports into a secure collaborative environment—a dashboard—with presentation details from other types of reporting systems (e.g., Excel Services) and BDC information. The following are some additional benefits to using an integrated SSRS and SharePoint environment:

• SSRS data connections are stored in DCLs similarly to InfoPath Forms Services and Excel Services data connections, providing you with a centralized list of connections.
• Because reports are stored in SharePoint document libraries, they can take advantage of SharePoint features such as workflow, information management policies, and versioning.
• Reports fall under the same security model as all other SharePoint content and no longer need to have separate ACLs maintained in the Report Server.
• SSRS reports can be pulled into dashboards, which use SharePoint’s Filter Web Part to provide a dynamic user experience.

Figure 4 shows an SSRS report with a shared data connection. (For more information about performing SSRS tasks in MOSS, see "SSRS and MOSS 2007: Deploying the Power.")

The Microsoft article "Security Overview for Reporting Services in SharePoint Integration Mode" describes how to secure SSRS in SharePoint integration mode. SharePoint’s authentication mechanism determines how connections or requests are made between SSRS and SharePoint. If SharePoint is set up to use Windows authentication with Kerberos, then SharePoint will pass the user’s credentials to the Report Server, which will verify that the user has permissions on the SQL objects required by the report. If SharePoint is set up to use Windows authentication without Kerberos, or is configured to use forms-based authentication, SharePoint will connect to SSRS with a service account—typically the IIS application pool identity of the SharePoint web application.

Business Data Catalog

SharePoint is also a platform for developers, enabling them to use the BDC and SharePoint APIs to create truly customized business applications to bring new views of data into SharePoint. The BDC is a set of customizable connections to a variety of data sources that give developers a standard interface into any line of business (LOB) back-end data using database or web service connections. The BDC lets developers create an interface between their custom code and any data source that’s accessible via ADO.NET or web services. LOB applications such as ERP systems, CRM systems, and HR systems are frequently the primary target of BDC applications. In fact, the BDC ships with some out-of-the-box Web Parts that can be used by site designers to display LOB data on the portal without writing any code beyond the creation of the XML mapping file. In addition to displaying business data records in BDC Web Parts, the BDC lets MOSS Search crawl structured business data, and lets business data be imported into user profiles.

The important thing to keep in mind with BDC applications is that you can make their connections bi-directional (i.e., read and write) using some third-party tools such as BDC Meta Man. The danger here is that careless developers could accidentally circumvent business rules by connecting directly to the data source. Having said that, the BDC supports the following five authentication modes:

• PassThrough—authenticates as the identity of the end user
• RevertToSelf—uses the identity of the IIS application pool account to connect to the database
• WindowsCredentials—uses Windows credentials from the SharePoint single sign-on service
• RdbCredentials—uses the credentials of the single sign-on service
• Credentials—used for web services that require non-Windows account credentials

For more information about BDC’s authentication modes, see "Business Data Catalog Authentication."

Once you’ve chosen the authentication mechanism, an XML document is authored or generated by a tool such as the Microsoft Business Data Catalog Definition Editor from MOSS 2007’s Software Development Kit (SDK), which models the database system. The XML document, complete with connection string details, is then saved and added to SharePoint as a BDC application’s configuration. These applications are defined and stored in the Shared Services Provider (SSP) for the web application.

Third-Party and Custom Database Connections

At its heart, SharePoint is a highly extensible ASP.NET 2.0 web application. Developers can easily add third-party and custom database connections to SharePoint components. Although Microsoft’s SharePoint support teams strongly discourage developers from modifying the core SharePoint databases, there’s no restriction on using external databases to store data for custom web parts or applications surfaced on SharePoint. As a DBA, you’ll need to work closely with the development staff to ensure that best practices are followed when it comes to database access during SharePoint development, just as you would with any other ASP.NET development project.

To help protect production SharePoint installations, administrators are encouraged to learn about Code Access Security and discouraged from setting the SharePoint web application’s trust level to Full Trust. Code Access Security and ASP.NET development is much too large a topic to be covered in this article. Instead we recommend researching the topic and reading "Code Access Security in SharePoint 2007 for Administrators." Microsoft has recently released MOSS SP2 and WSS SP2, which include an upgrade pre-scan utility that can help you prevent issues when it comes to planning for future versions of SharePoint.

SharePoint Designer and Database Connections

SharePoint offers one additional mechanism for connecting to external data sources—SharePoint Designer. Officially a Microsoft Office product, SharePoint Designer is the evolutionary next step from FrontPage 2003 to a truly powerful developer tool targeted at the SharePoint environment. SharePoint Designer offers the capability to connect to several different data sources, only one of which is actually a database. The other types of data sources available in the SharePoint Designer Data Source Library are shown in Figure 5.

Unfortunately for administrators, SharePoint Designer-based application data sources aren’t stored in a DCL. Instead, this information is stored with the application itself and is editable through SharePoint Designer.

Better Manage Data Connections

Centralized data connections in SharePoint DCLs offer a simplified management experience; however, it’s important to note that not every external data connection can be encapsulated in a .odc or .udc file. This is unfortunate because Excel, InfoPath, and SSRS in SharePoint integrated mode all take advantage of SharePoint’s security model which allows for item-level permissions to be applied, preventing unauthorized data access.