SharePoint Security for DBAs

Microsoft’s SharePoint technologies are among the fastest growing products released by the company. As organizations around the world adopt SharePoint as the platform for intranet, extranet, and Internet sites, DBAs and IT pros are stepping in as SharePoint administrators for their organizations. This article is an introduction to the security considerations associated with the installation and administration of Windows SharePoint Services (WSS) and Microsoft Office SharePoint Server (MOSS) 2007.

Understanding SharePoint’s Architecture

SharePoint is an n-tier client-server application that allows for redundancy at each of its layers. Although not technically part of a SharePoint server farm, the first tier is the client-workstation tier, which represents the myriad types of machines that can be used to access SharePoint. (Note that SharePoint automatically enables mobile access to its content.) The second tier is where the web servers, known as web front ends, serve up SharePoint content in the form of web pages, documents, images, and RSS feeds. The third tier is the application tier, on which servers such as the SharePoint Search Index service and Excel Calculation Services (ECS) server are found. Finally, the back-end data is stored in SQL Server on the fourth tier. Note that this simplified model leaves out services, such as Active Directory (AD), network load balancers, firewalls, and monitoring solutions (e.g., System Center Operations Manager). Even this simplified model hints at the complexity of SharePoint and the need for a careful approach to security during the setup, administration, and connection to external systems. Controlling access to these external systems begins with the definition of service accounts.

Defining AD Service Accounts

When adding SharePoint to your environment, it’s important to identify and document the service accounts you’ll be using to install SharePoint, to connect SharePoint to the various applications and services it needs, and to provide SharePoint with access to external systems and repositories. Microsoft has identified four categories of SharePoint security accounts: server farm–level accounts, Shared Service Provider (SSP) accounts, WSS search accounts, and application pool accounts. (For more information, see "Plan for administrative and service accounts" and "planning spreadsheet.") Let’s look at how these AD service accounts should be defined.

Server Farm–Level Accounts

The server farm–level service accounts are used to set up SQL Server and SharePoint and to provide an identity to SharePoint’s administrative tools such as Central Administration and the WSS Timer service. These accounts are typically domain user accounts that belong to a variety of AD groups, including the local Administrators group. In SQL Server, these service accounts can operate with the least privilege administration rights by granting db_owner privileges inside of the various SharePoint databases and securityadmin and dbcreator fixed server roles. Although these seem to be powerful rights, they’re the least privileges required by SharePoint. SharePoint takes over the creation and ownership of its back-end databases on SQL Server.

The SQL Server service account is required during the installation of SQL Server and isn’t utilized by SharePoint. SQL Server will prompt for this Local System or domain user account. A domain account is preferred because only domain user accounts can make remote procedure calls, back up to network drives, participate in replication, use the SQL Mail features, or any other activity requiring network resource access controlled by Windows domain authentication. This service account is used to run some of the ten SQL Server services, including MSSQLServer, SQL Server Agent, and potentially MSSQLServerOLAPService, ReportServer, and SQLBrowser. This service account should be kept completely separate from SharePoint. The different SQL Server component services might run under different service accounts to provide more granular security. These service accounts are the basis for security in SQL Server. The rights assigned to these accounts and the context associated with each one defines which resources are available on your server. If your other layers of security are penetrated by a malicious program or attacker, the different limits on each service account help to limit the attack surface.

The SharePoint Setup user account is the account used by IT staff to log into the server and to run the setup application for the SharePoint installation process. It must be a domain user account and a member of the Administrators group on the local server(s). In addition, this account must be made a member of the securityadmin and dbcreator fixed server roles inside of SQL Server and must be granted db_owner privileges for any database that might be affected by SharePoint or the SharePoint command-line utilities. By default, these fixed server roles and database privileges are granted during the installation of the SharePoint software if you choose the simple (SQL Server Express) installation. However, when choosing the more complex server farm installation, the Setup user account must have the required access to the database before beginning the install. The Setup user account also runs the Stsadm and PSConfig command-line utilities. The Microsoft article "How to change service account and service account passwords in SharePoint Server 2007 and Windows SharePoint Services 3.0" outlines the procedure to change the passwords for MOSS 2007 service accounts using the Stsadm UpdateFarmCredentials command.

The final server farm–level service account is the Server farm account or the Database Access account. This account acts as the IIS application pool identity for the Central Administration site and is the Log On identity of the WSS Timer service, as shown in Figure 1.

This account must be a domain user account and is automatically granted the appropriate dbcreator, securityadmin, and db_owner permissions to the back-end SQL Server during the SharePoint installation process. Furthermore, this account is used by SharePoint to access AD when MOSS’s People Picker is used to find and validate people in the portal. The People Picker field can be found when manipulating site collection owners, when adding users to People and Groups, and when using the People column type in lists. In addition, if the server is configured to receive email and automatically create AD distribution groups and contacts, this account is used to configure AD.

SSP Accounts

SSP accounts are associated with the premium editions of MOSS—Microsoft Office SharePoint Server Standard Edition and Enterprise Edition. These accounts are used to control MOSS search services, timer services, content crawling, AD or LDAP profile import, ECS, and IIS Application Pool accounts. These SSP service accounts operate in a similar set of least privilege conditions as the server farm–level accounts. Typically, these accounts are domain user accounts and not members of the local Administrators group. In SQL Server, these accounts are granted db_owner fixed database roles for some databases and simple read and write privileges for others. Once the account has been designated in the SSP administrative pages, SharePoint will automatically configure the correct levels of access in SQL Server, using its higher-privilege account as a management connection.

Like other SharePoint components, SSPs require database ownership privileges to manage objects and security within those databases. Most DBAs are used to having to configure database security separately from application security. However, SharePoint and its components smoothly integrate this process, bypassing the need for separate database security management. The most prominent service account at the SSP level is the SSP application pool account. This account provides process isolation for the SSP web application. During the set up of the SSP, the required permissions for this domain user account are granted automatically. This service account is granted db_owner permission for the SSP content database, read and write access to the content databases of the web applications that utilize the SSP, read access to the configuration database, and read access to the Central Administration database.

The SSP service account is used by the SSP web services for inter-server communications and the SSP Timer service. This service account is also the application pool identity. No manual configuration is necessary for this account because it’s automatically granted the same permissions as the SSP application pool account. Although this account needs to be a domain user account, it shouldn’t be a member of the Administrators group on any computer in the server farm. Although Microsoft’s documentation refers to the SSP service account and the SSP application pool account as different service accounts, it’s actually a best practice to make these the same domain user.

The SharePoint search services can actually utilize several distinct service accounts. The Office SharePoint Server Search service account is used by the Search service. This account is unique in that there’s only one instance of this search service in a farm, and it’s shared by all of the SSPs that exist in the network. This account must be a domain user account but not a member of the Farm Administrators group. Database access is granted automatically. The default content access account is the default crawl account used by the Search service to crawl content (unless a specific authentication method is required by a crawl rule for a specific URL). The default content access account is a domain user account that must have read access to all external or secure content sources that will be crawled by this account. This means that any site that’s not a member of the server farm must include this account as a user and grant this account full read access. Database access is granted automatically. Although there’s a default content access account, it’s possible that some content sources might require specific authentication using a Content Access account, which is specified in the SSP’s crawl rules.

The other two major services provided by a SSP are the Profile import service and ECS. Each of these services has a unique service account. The Profile import default access account is used to import profile data from AD, LDAP directories, Business Data Catalog (BDC) applications, or other directory sources. This account must have read access to the directory service and must have the Manage User Profiles personalization services permission (Read "Manage user profiles and user profile properties" to learn more about this permission). The Profile import default access account is automatically granted this personalization permission unless the default content access account (Farm Search Service Account) isn’t a member of the Farm Administrators group. If BDC imports are used, the service account must have view permissions on the entities used in the BDC connection. The other service account is the Excel Services unattended service account. This account is used to connect to external data sources that don’t use Kerberos or Integrated Windows Authentication in the connection string. Although the target data source doesn’t use Windows for authentication, the service account must be a domain user account.

WSS Search Accounts

WSS Search accounts are granted high-level rights on the server and in SharePoint because they require access to all of the available content for search indexing, regardless of applied permissions. The WSS search account is used to control the WSS Search service and to access content in SharePoint sites. This account must be a domain user account that’s not a member of the Farm Administrators group. Database permission, including read access to the configuration database and the SharePoint_Admin content database and membership in the db_owner role for the SharePoint Services search database are all granted automatically. As with the Office SharePoint Server Search service account, there’s only one WSS search account in a farm.

The WSS Search Content Access account is used to crawl content across the various SharePoint sites, which might require distinct credentials. Typically, these accounts are used when crawl rules specifying login credentials are created. The requirements for this account are the same as the requirements for the WSS Search service account above. This service account is automatically added to the web application’s full-read policy for the farm if MOSS isn’t installed. If MOSS is installed, the WSS Search service account is used to index Help content for searching inside of the Help popup, and the Content Access account isn’t used.

IIS Application Pool Accounts

IIS application pools are used to provide process isolation in IIS. Process isolation prevents an error in one application from affecting applications running in a different process. The IIS application pool service accounts are typically granted a reduced set of server privileges and are used as the actual database owner of the SharePoint content database for the appropriate sites. The primary use for this service account is to provide SharePoint with read and write access to the content databases associated with the web applications that reside in that particular application pool. Joel Oleson, formerly from Microsoft, recommended in his blog that a single SharePoint server not attempt to use more than four distinct application pools (with 10 being the maximum) to prevent potential performance problems. (This recommendation doesn’t include the Central Administration or SSP application pools because these applications are used infrequently.) Each SharePoint application pool uses an average of 800MB of RAM, severely limiting 32-bit systems. Following these recommendations, you’ll likely want to group your SharePoint web applications under a single application pool.

Configuring Security in SharePoint

Securing SharePoint begins with the definition and assignment of AD service accounts but doesn’t end there. SharePoint also has many different internal security layers, including the server or server farm level, the shared services level, and the site level. (For more information about SharePoint’s logical layers, see "Logical architecture components.") Administration actions can occur on any of these layers, so SharePoint provides a security infrastructure to help control access to these locations.

Security in SharePoint is handled by the assignment of rights and roles. Foremost among these rights are the administrative permissions, which provide control over SharePoint’s functionality. The individuals performing administrative actions at any of these levels might or might not be the same individual. Users and groups can be granted administrative permissions on the following layers in SharePoint.

Farm level. Members of the Administrators group on the local server have the ability to change any aspect of SharePoint, including the fundamental building blocks upon which SharePoint relies, SQL Server, and IIS. Despite this enormous level of power, server administrators aren’t automatically granted access to the content stored in SharePoint sites.

Farm Administrators are identified in Central Administration. These users have permission to perform any administrative task in Central Administration. Members of this group can also use the Stsadm command-line tool to perform tasks such as scheduled solution deployments and unattended SharePoint backups. Similar to members of the local server’s Administrators group, farm administrators don’t automatically have access to site content.

Shared services level. SSP administrators are defined within the SSP itself and can control which services are included in the SSP, such as Excel Services and User Profile Imports. (For more information, see "Shared Service Providers and Delegation.") Service administrators at the shared services level are able to configure settings for a specific service within an SSP. Frequently, users are granted service administration permissions so that they have the ability to tune SharePoint’s search functionality for the SSP by defining authoritative pages and search keywords.

Site level. Site collection administrators are defined in Central Administration for each site collection in every web application and are given Full Control of every site within that site collection. Site collection administrators don’t require additional permissions at the site level to have access to that site’s content as their level of control is granted centrally. Because site collection administrators might be defined in Central Administration, a farm administrator or local server administrator could add themselves to this group to access otherwise unavailable content and perform site level administration activities.

The Home Owners group is automatically created on every SharePoint site in the People and Groups area. Members of the Home Owners group are site owners and are granted Full Control on that site. Site owners can be distinct for every site in a site collection or can be inherited from the top-level site. These users have access to the site settings menu and can modify the settings of any list or library on the site. In addition, custom groups can be created and assigned Full Control permissions, which equal the default permissions of the Home Owners group. The main difference between the Home Owners group and custom groups with Full Control and site collection administrators is that subsites can opt to specify unique site permissions that exclude the parent site’s Home Owners group. Site collection administrators can exercise Full Control through every site in the collection.

SharePoint’s Policy for Web Applications Feature

Although it’s not truly an administrative level, SharePoint’s Policy for Web Application feature deserves a mention here. Defined in Central Administration’s Application Management tab, members of the Read policy group or Deny policy group can be granted read access to the entire farm or denied read access to the entire farm. These policy settings override site-level permissions. A scenario in which you might use these settings is to provide an auditor access to the entire site for a short period of time. You wouldn’t want to navigate to each location the auditor needed but rather grant the auditor access for the time they’ll be on site and then remove the auditor’s rights once they leave. Another reason to add a user to a policy group might be if someone leaves your company and you need to remove their access rights immediately. In that case, you can deny the user’s rights in the policy of the web application without going to each location to remove them. Doing so prevents the user from using the site while giving you time to correctly remove the user from the site.

A Secure SharePoint Environment

DBAs and IT pros are increasingly finding themselves associated with SharePoint projects. SharePoint is a complex system that requires some serious study to follow Microsoft’s best practices. There are twelve different types of service accounts available on the network, three distinct levels of security configuration in SharePoint itself, and a host of data connection capabilities for extensibility. Gaining an understanding of the various surfaces for security configuration is an important aspect of an effective SharePoint installation.