SQL Server 2005 Database Engine Security
(Page 1 of 4 )
In this second part of a six-part series on administering the SQL Server 2005 database engine, you'll learn how to administer the security page, the connections page, and more. This article is excerpted from the book
SQL Server 2005 Management and Administration, written by Chris Amaris, Alec Minty and Ross Mistry (Sams Publishing, 2008; ISBN: 0672329565).
Administering the Security Page
The Security page, shown in Figure 1.4, maintains server-wide security configuration settings. These SQL Server settings include Server Authentication, Login Auditing, Server Proxy Account, and Options.
Figure 1.4. Administering the Server Properties Security page.
Server Authentication
The first section in the Security page focuses on server authentication. At present, SQL Server 2005 continues to support two modes for validating connections and authenticating access to database resources: Windows Authentication Mode and SQL Server and Windows Authentication Mode. Both of these authentication methods provide access to SQL Server and its resources.
Note
During installation, the default authentication mode is Windows. The authentication mode can be changed after the installation.
The Windows Authentication Mode setting is the default Authentication setting and is the recommended authentication mode. It tactfully leverages Active Directory user accounts or groups when granting access to SQL Server. In this mode, you are given the opportunity to grant domain or local server users access to the database server without creating and managing a separate SQL Server account. Also worth mentioning, when Windows Authentication mode is active, user accounts are subject to enterprise-wide policies enforced by the Active Directory domain, such as complex passwords, password history, account lockouts, minimum password length, maximum password length, and the Kerberos protocol. These enhanced and well-defined policies are always a plus to have in place.
The second Authentication Option is SQL Server and Windows Authentication (Mixed) Mode. This setting, which is regularly referred to as mixed mode authentication, uses either Active Directory user accounts or SQL Server accounts when validating access to SQL Server. SQL Server 2005 has introduced a means to enforce password and lockout policies for SQL Server login accounts when using SQL Server Authentication. The new SQL Server polices that can be enforced include password complexity, password expiration, and account lockouts. This functionality was not available in SQL Server 2000 and was a major security concern for most organizations and database administrators. Essentially, this security concern played a role in helping define Windows authentication as the recommended practice for managing authentication in the past. Today, SQL Server and Windows Authentication mode may be able to successfully compete with Windows Authentication mode.
Note
Review the authentication sections in Chapter 12, “Hardening a SQL Server 2005 Environment,” for more information on authentication modes and which mode should be used as a best practice.
Login Auditing
Login Auditing is the focal point on the second section on the Security page. You can choose from one of the four Login Auditing options available: None, Failed Logins Only, Successful Logins Only, and Both Failed and Successful Logins.
Tip
When you’re configuring auditing, it is a best practice to configure auditing to capture both failed and successful logins. Therefore, in the case of a system breach or an audit, you have all the logins captured in an audit file. The drawback to this option is that the log file will grow quickly and will require adequate disk space. If this is not possible, only failed logins should be captured as the bare minimum.
Server Proxy Account
You can enable a server proxy account in the Server Proxy section of the Security page. The proxy account permits the security context to execute operating system commands by the impersonation of logins, server roles, and database roles. If you’re using a proxy account, you should configure the account with the least number of privileges to perform the task. This bolsters security and reduces the amount of damage if the account is compromised.
Additional Security Options
Additional security options available in the Options section of the Security page are
Enable Common Criteria Compliance—When this setting is enabled, it manages database security. Specifically, it manages features such as Residual Information Protection (RIP), controls access to login statistics, and enforces restrictions where, for example, the column titled GRANT cannot override the table titled DENY.
Note
Enable Common Criteria Compliance is a new feature associated with SQL Server 2005 Service Pack 2 Enterprise Edition.
- Enable C2 Audit Tracing—When this setting is enabled, SQL Server allows the largest number of the success and failure objects to be audited. The drawback to capturing for audit data is that it can degrade performance and take up disk space.
- Cross Database Ownership Chaining—Enabling this setting allows cross database ownership chaining at a global level for all databases. Cross database ownership chaining governs whether the database can be accessed by external resources. As a result, this setting should be enabled only when the situation is closely managed because several serious security holes would be opened.
Next: Administering the Connections Page >>
More Database Articles
More By Sams Publishing
