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.