SQL
Server has a strong relationship with SharePoint Server 2010 as it is
the back-end database repository for SharePoint data. All of SharePoint
configuration and content databases are stored within SQL Server, which
makes it highly important and recommended to follow security best
practices on SQL Server, just as one would when securing SharePoint to
minimize vulnerabilities.
The enforcement of SQL
Server security should be one of the most important tasks SQL Server
database administrators commit themselves to. Furthermore, to properly
ensure that vulnerabilities are minimized, SQL Server security should be
a part of both the test and production SQL Server systems.
Equally important, as a
result of continuous advancements made by Microsoft, SQL Server
2005/2008 has significant enhancements to the security model of the
database platform, which now provides more precise and flexible control
resulting in tighter security. Some of the features that have been
enhanced include the advanced security of surface area reduction, data
encryption, native encryption, authentication, granular permissions, and
user and schema separations. These advancements contribute to
Microsoft’s Trustworthy Computing initiative that defines the steps
necessary to help support secure computing.
At present, numerous SQL
Server security best practices are applicable when deploying SharePoint.
The following sections discuss some of these best practices.
Windows Versus SQL Server Authentication
Authentication is a
security measure designed to establish the validity of a user or
application based on criteria such as an account, password, security
token, or certificate. Typically, once the validity has been verified,
the user or application is granted authorization to the desired object.
SQL Server
continues to support two modes for validating connections and
authenticating access to database resources: Windows authentication and
SQL Server authentication. Both authentication methods provide the
SharePoint application access to SQL Server and its resources, such as
the SharePoint config, site, and content databases.
Windows Authentication Mode
Windows
Authentication mode leverages Active Directory user accounts or groups
when granting access to SQL Server. This is the default and recommended
authentication mode, and it allows IT professionals to grant domain
users access to the database server without creating and managing a
separate SQL Server account. In addition, when using Windows
Authentication mode, user accounts are subject to enterprise-wide
policies enforced in the Active Directory domain such as complex
passwords, password history, account lockouts, minimum password length,
and maximum password length.
SQL Server Authentication Mode
SQL Server authentication
also referred to as mixed-mode authentication utilizes either Active
Directory user accounts or SQL Server accounts when validating access to
SQL Server. Unless some reason exists for using mixed-mode
authentication, it is highly recommended not to use this with SharePoint
and to instead use Windows Authentication mode.
Determining Which Authentication Mode to Utilize
Windows Authentication works
best if the SQL Server will be accessed from within the organization and
all user accounts needing access reside in Active Directory. For
example, Windows Authentication can be used when deploying SharePoint if
both the SharePoint and SQL Server reside in the same domain or in
separate domains that are trusted. On the other hand, SQL Server
mixed-mode authentication works best if users or applications require
access to SQL Server and are not associated with the domain that SQL
Server resides in. For example, SQL Server authentication should be
leveraged if the SharePoint server is not in the same domain as the SQL
Server and a trust does not exist between the two environments.
Even though SQL Server now
has the ability to enforce policies such as SQL Server account password
complexity, password expiration, and account lockouts, Windows
Authentication mode is still the recommended alternative for controlling
access to SQL Server. The added advantage of Windows Authentication is
that Active Directory provides an additional level of protection with
the Kerberos protocol and administration is reduced by leveraging Active
Directory groups when providing access to SQL Server.
Minimize SQL Server Attack Surface
Once SQL Server for SharePoint
has been installed the SQL, Server Surface Area Configuration tool
should be run to reduce the system’s attackable surface area by
disabling unused services, components, features, and remote connections.
To reduce surface attack and
secure the SQL Server installation for SharePoint, it is recommended
that an IT professional launch the SQL Server Surface Area Configuration
tool and disable all unnecessary services, components, and connections
that will not be used.
Typically, only the Database
Engine, SQL Server Agent, and SQL Server Browser services are required
for a base SharePoint installation. If, however, SharePoint Reporting or
PowerPivot
capabilities are required, more advanced SQL installations involving
Analysis Services and Reporting Services may be needed.
Using SQL Server Security Logs
Enabling security auditing
on SQL Server will monitor and track activity to log files that can be
viewed through Windows application logs or SQL Server Management Studio.
SQL Server offers four security levels with regards to security
auditing, as follows:
None— Disables auditing so no events are logged.
Successful Logins Only— Audits all successful login attempts.
Failed Logins Only— Audits all failed login attempts.
Both Failed and Successful Logins— Audits all login attempts.
Security auditing is set to
Failed Logins Only by default. It is a best practice to configure
security auditing to capture both failed and successful logins. At the
very least, security auditing should be set to Failed Logins Only. As a
result, failed logins can be saved, viewed, and acted upon.
