RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The 'Secure by Default' Features of SQL Server 2005

SQL Server 2005 offers improvements in authentication and authorization, as well as native support for cryptography—features that enable more secure database application design and implementation than previous releases.

oftware security is a very important topic, because not a single day passes without the discovery of some new exploit. Windows 2003 Server was the first product Microsoft shipped under the motto "secure by default." The whole server is locked down by default, and you have to activate each service you want to use separately. Therefore, attackers have to hard work to gain access to the system.

Figure 1. The SQL Server Surface Area Configuration Tool

The same policy applies to SQL Server 2005: the complete database server is also locked down by default and each service and feature must be activated explicitly. For this reason, SQL Server ships with the SQL Server Surface Area Configuration tool (see Figure 1), with which you can define which services and features are activated during a SQL Server installation.


Authentication is the first process you must successfully complete in order to gain access to a SQL Server instance. Figure 2 illustrates the model behind this security concept.

Like its previous versions, SQL Server 2005 supports the Windows and Mixed authentication modes. Microsoft suggests using the Windows authentication mode for security reasons. In this mode, the security checks are performed against Active Directory. Its drawback is that users and the database server must reside in the same Active Directory domain.

Figure 2. The Security Concept of SQL Server

When you use a SQL Server build in Mixed authentication mode, SQL Server handles the login credentials. This configuration makes sense in some scenarios, but the downside is you can't use a secure infrastructure like the one Active Directory provides.

A new functionality of SQL Server 2005 is the ability to manage password and logout policies for the authentication process when you use the Windows authentication mode. You can manage account restrictions, such as strong passwords or the expiration dates. However, you can use these features only when SQL Server 2005 is installed on a Windows 2003 Server. The API for these functionalities isn't available on other Windows platforms.

The following restrictions are available when you set the password of an account:

  • The length of the password must be at least six characters. (SQL Server supports password lengths from 1 to 128 characters.)
  • Passwords must use different kinds of characters (uppercase, lowercase, digits, special signs, etc.).
  • A password can't contain phrases like "Admin", "Administrator", "Password", "sa", or "sysadmin".
  • Besides these restrictions, a password also can't be an empty field.

When you create a new login, you can use the extensions CHECK_EXPIRATION and CHECK_POLICY with the T-SQL statement CREATE LOGIN. CHECK_EXPIRATION controls the expiration date of the login, and CHECK_POLICY activates the above described password policy mechanism. The new option MUST_CHANGE makes the user change his or her password during the first login. (Beta 2 of SQL Server 2005 currently does not support this feature.) Listing 1 shows some code for these new options:

Listing 1
   WITH PASSWORD='P@ssw0rd1'

SQL Server 2005 also supports endpoint authentication to assure secure communication when SQL Server exposes XML Web services through http.sys on the Windows Server 2003 platform.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date