SQL Server 2005 Database Engine Security

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.

1. 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.
   
2. 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.

Administering the Connections Page

The Connections page, as shown in Figure 1.5, is the place where you examine and configure any SQL Server settings relevant to connections. The Connections page is broken up into two sections: Connections and Remote Server Connections.

Figure 1.5.   Administering the Server Properties Connections page.

Connections

The Connections section includes the following settings:

1. Maximum Number of Concurrent Connections—The first setting determines the maximum number of concurrent connections allowed to the SQL Server Database Engine. The default value is 0, which represents an unlimited number of connections. The value used when configuring this setting is really dictated by the SQL Server hardware such as the processor, RAM, and disk speed.
   
2. Use Query Governor to Prevent Long-Running Queries—This setting creates a stipulation based on an upper limit criteria specified on the time period in which a query can run. 
    
3. Default Connection Options—For the final setting, you can choose from approximately 16 advanced connection options that can be either enabled or disabled, as shown in Figure 1.5.

 Note

For more information on each of the default Connection Option settings, refer to SQL Server 2005 Books Online. Search for the topic “Server Properties Connections Page.”

Remote Server Connections

The second section located on the Connections page focuses on Remote Server settings:

1. Allow Remote Connections to This Server— If enabled, the first option allows remote connections to the specified SQL Server.
   
2. Remote Query Timeout—The second setting is available only if Allow Remote Connections is enabled. This setting governs how long it will take for a remote query to terminate. The values that can be configured range from 0 to 2,147,483,647. Zero represents infinite. 
    
3. Require Distributed Transactions for Server-to-Server Communication—The final setting controls the behavior and protects the transactions between systems by using the Microsoft Distributed Transaction Coordinate (MS DTC).

Administering the Database Settings Page

The Database Settings page, shown in Figure 1.6, contains configuration settings that each database within the SQL Server instance will inherit. The choices available on this page are broken out by Fill Factor, Backup and Restore, Recovery, and Database Default Locations.

Default Index Fill Factor

The Default Index Fill Factor specifies how full SQL Server should configure each page when a new index is created. The default setting is 0, and the ranges are between 0 and 100. The 0 value represents a table with room for growth, whereas a value of 100 represents no space for subsequent insertions without requiring page splits. A table with all reads typically has a higher fill factor, and a table that is meant for heavy inserts typically has a low fill factor. The value 50 is ideal when a table has plenty of reads and writes. This setting is global to all tables within the Database Engine.

For more information on fill factors, refer to Chapter 8, “SQL Server 2005 Maintenance Practices” and Chapter 9, “Managing and Optimizing SQL Server 2005 Indexes.”

Figure 1.6.   Administering the Server Properties Database Settings page.

Backup and Restore

The Backup and Restore section of the Database Settings page includes

1. Specify How Long SQL Server Will Wait for a New Tape—The first setting governs the time interval SQL Server will wait for a new tape during a database backup process. The options available are Wait Indefinitely, Try Once, or Try for a specific number of minutes.
   
2. Default Backup Media Retention—This setting is a system-wide configuration that affects all database backups, including the translation logs. You enter values for this setting in days, and it dictates the time to maintain and/or retain each backup medium.

Recovery

The Recovery section of the Database Settings page consists of

• Recovery Interval (Minutes)—Only one Recovery setting is available. This setting influences the amount of time, in minutes, SQL Server will take to recover a database. Recovering a database takes place every time SQL Server is started. Uncommitted transactions are either committed or rolled back.

Database Default Locations

Options available in the Database Default Locations section are

• Data and Logs—The two folder paths for Data and Log placement specify the default location for all database data and log files. Click the ellipses on the right side to change the default folder location.

Administering the Advanced Page

The Advanced Page, shown in Figure 1.7, contains the SQL Server general settings that can be configured.

Figure 1.7.   Administering the Server Properties Advanced Settings page.

Miscellaneous Settings

Options available on the Miscellaneous section of the Advanced page are

1. Allow Triggers to Fire Others—If this setting is configured to True, triggers can execute other triggers. In addition, the nesting level can be up to 32 levels. The values are either True or False.
   
2. Cursor Threshold—This setting dictates the number of rows in the cursor that will be returned for a result set. A value of 0 represents that cursor keysets are generated asynchronously. 
    
3. Default Full-Text Language—This setting specifies the language to be used for full-text columns. The default language is based on the language specified during the SQL Server instance installation. 
    
4. Default Language—This setting is also inherited based on the language used during the installation of SQL. The setting controls the default language behavior for new logins. 
    
5. Max Text Replication Size—This global setting dictates the maximum size of text and image data that can be inserted into columns. The measurement is in bytes. 
    
6. Scan for Startup Procs—The configuration values are either True or False. If the setting is configured to True, SQL Server allows stored procedures that are configured to run at startup to fire. 
    
7. Two Digit Year Cutoff—This setting indicates the uppermost year that can be specified as a two-digit year. Additional years must be entered as a four digits.

Network Settings

Options available on the Network section of the Advanced page are

1. Network Packet Size—This setting dictates the size of packets being transmitted over the network. The default size is 4096 bytes and is sufficient for most SQL Server network operations. 
    
2. Remote Login Timeout—This setting determines the amount of time SQL Server will wait before timing out a remote login. The default time is 30 seconds, and a value of 0 represents an infinite wait before timing out.

Parallelism Settings

Options available on the Parallelism section of the Advanced page are

1. Cost Threshold for Parallelism—This setting specifies the threshold above which SQL Server creates and runs parallel plans for queries. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. Set this option only on symmetric multiprocessors. For more information, search for “cost threshold for parallelism option” in SQL Server Books Online.
    
2. Locks—The default for this setting is 0, which indicates that SQL Server is dynamically managing locking. Otherwise, you can enter a numeric value that sets the utmost number of locks to occur. 
    
3. Max Degree of Parallelism—This setting limits the number of processors (up to a maximum of 64) that can be used in a parallel plan execution. The default value of 0 uses all available processors, whereas a value of 1 suppresses parallel plan generation altogether. A number greater than 1 prevents the maximum number of processors from being used by a single query execution. If a value greater than the number of available processors is specified, however, the actual number of available processors is used. For more information, search for “max degree of parallelism option” in SQL Server Books Online. 
    
4. Query Wait—This setting indicates the time in seconds a query will wait for resources before timing out.

Please check back next week for the continuation of this article.