SQL Server 2005 reduces the number of potential points of attack at install time by adhering to the principle of "secure by default," part of Microsoft's companywide Trustworthy Computing initiative. In addition, the release addresses a number of security deficiencies in previous SQL Server releases, helping SQL Server 2005 administrators more easily manage database security. However, some new features may cause compatibility problems with existing applications or procedures.

Secure by Default

According to the secure-by-default principle, software must install in its most secure configuration and remain in that state until the user or administrator actively changes the configuration. For example, if a product is secure by default, only a minimal set of features are available on installation, and any additional features require administrator authentication before they can be installed or activated. This makes the product considerably less vulnerable to attack.

Many SQL Server 2005 services and features, such as Analysis Services and Integration Services, require administrator action before they will run. SQL Server 2005 includes a new utility (called the Surface Area Configuration Tool) to help administrators configure servers. The tool provides a graphical user interface that allows administrators to enable or disable SQL Server services, features, and communications protocols.

Improved Security Management

In combination with Windows Server 2003, SQL Server 2005 database servers are more resistant to unauthorized or improper access than their predecessors. For example, SQL Server 2005 administrators can take advantage of security features in Windows Server 2003 to enforce password policies (such as password length and required characters) on SQL Server account passwords. Previously, administrators could enforce such policies on Windows accounts used for SQL Server access but not on passwords stored in SQL Server.

In addition, SQL Server 2005 introduces several new features that ease the management of database security:

Separation of database user and database objects. SQL Server 2005 addresses a design quirk in previous versions of the product: the names of database objects (such as tables and stored procedures) used to contain the name of a specific database user (for example, the user who created the object). Removing a user also implied that all objects owned by that user either needed to be deleted or reassigned to another user, and thus renamed. However, renaming database objects has ripple effects—applications that reference those objects must be rewritten if the object's name changes. SQL Server 2005 no longer ties database objects to specific users, alleviating this potential administrative and developer headache.

Better control over permissions. SQL Server 2005 introduces new permissions that allow administrators to more tightly control and specify the type of database access granted to users. Administrators will be better able to align database access rights with users' job functions rather than granting broad access to data that are nonessential to that function. For example, a new permission called "view definition" could be used to give developers the ability to examine a database's table structure without letting them change the table's structure or its constituent data; similarly, "view server state" lets a user view server performance data without allowing the user to significantly alter the database.

Built-in support for data encryption. SQL Server 2005 provides support for data encryption, which allows developers to store critical or proprietary data (such as credit card numbers) in the database without exposing that data to unauthorized users (such as database administrators) or applications. For instance, it offers built-in facilities for managing security keys (character strings used by special algorithms to encrypt or decrypt data) and certificates (which validate the authenticity of keys).

Applications May Need Update

SQL Server 2005 makes important strides in database security but some new features may require changes to existing applications, procedures, or scripts.

For example, SQL Server 2005's new scheme for naming database objects changes the behavior of certain SQL Server commands and database system views, which provide information about the structure of SQL Server databases and database objects. Developers and administrators should note that applications, procedures, or scripts that use such commands or views may need to be updated.

Resources

SQL Server 2005 security is detailed at www.microsoft.com/sql/technologies/security/default.mspx.

Microsoft's Trustworthy Computing home page is www.microsoft.com/mscorp/twc/default.mspx.

Trustworthy Computing is described in the Apr. 2004 Research Report, "Trustworthy Computing: Making Software More Secure."