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

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 1CREATE LOGIN Paul    WITH PASSWORD='P@ssw0rd1'   MUST_CHANGE,    CHECK_EXPIRATION = ON,   CHECK_POLICY = ON

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.

Authorization

After a user is successfully authenticated against SQL Server, the authorization process starts to determine which rights the user has on the database. In this area, SQL Server 2005 has two new features:

• Separation of users and schemas
• Execution context

Figure 3. User and Schema Relationship in SQL Server 2000

Separation of Users and Schemas

A schema is a container where you can logically group database objects (tables, stored procedures, views, etc.). It is the same as a namespace in the .NET Framework base class library. Because a schema in SQL Server also can have an owner, you can dictate that each object in a given schema have the same owner. SQL Server 2000 doesn’t have a good implementation of schemas: the name of the schema is the same as the name of the user. Therefore, a direct relationship exists between the schema and the owner of the database objects (see Figure 3).

The big problem in SQL Server 2000 is that objects in a given schema must be addressed with a fully qualified name (schema.objectname), but when you drop a user from the database, the fully qualified name of an object changes. Therefore, the changes you made in the database unfortunately also occurred in the queries on the client side, and such changes were sometimes costly. With SQL Server 2005, the schema is a standalone native object in the database with its own name and owner. Such a schema has no relationship to the user (see Figure 4).

Figure 4. User and Schema Separation in SQL Server 2005

Now you can delete and add new users to the database without making changes in the software (neither on the client side nor on the database side). Furthermore, you can assign rights to a schema to control access to the objects in that particular schema. Listing 2 shows schema usage in SQL Server 2005. This code is also available as a download in the file Schemas.sql.

Listing 2USE masterGO-- Create database loginsCREATE LOGIN Paul WITH PASSWORD='p@ssw0rd1'CREATE LOGIN Mary WITH PASSWORD='p@ssw0rd1'USE TestDatabaseGOCREATE USER Paul FOR LOGIN PaulCREATE USER Mary FOR LOGIN Mary-- Create a new schemaCREATE SCHEMA SalesData-- Create a new table in the schema "SalesData"CREATE TABLE SalesData.SalesPromotion(   ID int,   [Name] varchar(255))GRANT ALL ON SalesData TO PaulALTER USER Paul WITH DEFAULT SCHEMA = SalesDataGRANT SELECT ON SCHEMA::SalesData TO MARY

Execution Context

Another new feature in SQL Server 2005 allows you to change the execution context of a stored procedure, a user-defined function, or a trigger. You can control under which user these database objects execute by changing the execution context with the EXECUTE AS statement, which supports the following options:

• EXECUTE AS CALLER
• EXECUTE AS USER=’user name’
• EXECUTE AS SELF
• EXECUTE AS OWNER

EXECUTE AS CALLER is the default option, through which a stored procedure is executed with the identity of the calling user. With EXECUTE AS USER, you specify the user under which the stored procedure should run. When you use EXECUTE AS SELF, the stored procedure executes with the identity of the creator. Finally, you can use EXECUTE AS OWNER to execute the stored procedure with the identity of the object owner.

Cryptography

When you use cryptography in an application, you must manage the key (either the private key of an asymmetric algorithm or the shared key of a symmetric algorithm). SQL Server 2005 provides two options for key management:

• Manage the key yourself
• SQL Server manages your key

When you manage the key yourself, SQL Server stores the symmetric key with a given password in the database and you must keep the password in a secret place. When SQL Server does the key management for you, it uses the Service Master Key and the Database Master Key. Figure 5 illustrates the idea behind this concept.

Figure 5. Service and Database Master Keys in SQL Server 2005

As you can see, the Service Master Key lives on the server level. This key is created during the installation of SQL Server and is protected through the Data Protection API (DPAPI). Through several T-SQL statements, you can dump the Service Master Key to a file and restore it from a file.

The Database Master Key lives on the database level and must be created explicitly through an administrator. It can be encrypted through a password or through the Service Master Key. Listing 3 shows how you can create the Database Master Key:

Listing 3CREATE MASTER KEY ENCRYPTION BY PASSWORD='p@ssw0rd1'

As soon as you have created the Database Master Key, you have the following options:

• Encryption with a symmetric key
• Encryption with an asymmetric key
• Encryption with a certificate

You can create a symmetric key with the T-SQL statement CREATE SYMMETRIC KEY. The encryption is based on either a password or the Database Master Key, which is stored in the table sys.symmetric_keys. To work with a symmetric key, you must open it in the first step (this includes the decryption of the key) with the statement OPEN KEY (see Figure 6). After that, you can use the key for your own encryption purposes.

Figure 6. Encryption with a Symmetric Key

The benefit of a symmetric key is its performance, which is about 1,000-10,000 times faster than that of an asymmetric key. The drawback is that only one key is used for encryption and decryption and both parties must know this key. You create an asymmetric key with the statement CREATE ASYMMETRIC KEY. Such a key also can be encrypted with a password or the Database Master Key. Listing 4 shows how you can create an asymmetric key:

Listing 4-- Encryption with a passwordCREATE ASYMMETRIC KEY MyKeyName AUTHORIZATION User1   WITH ALGORITHM = RSA_512   ENCRYPTED BY PASSWORD = 'p@ssw0rd1'-- Encryption with the database master keyCREATE ASYMMETRIC KEY MyKeyName AUTHORIZATION User1   WITH ALGORITHM = RSA_512

An asymmetric key is always stored in the table sys.asymmetric_keys. After you have created the key, you can use the function EncryptByAsmKey to encrypt data and DecryptByAsmKey to decrypt the encrypted data. Listing 5 shows the usage of both functions:

Listing 5DECLARE @EncryptedStuff varchar(1000)SELECT @EncryptedStuff = EncryptByAsmKey(AsymKey_ID('MyKeyName'),    'My secret message')SELECT @EncrytedStuffSELECT CAST(DecryptByAsmKey(AsymKey_ID('MyKeyName'), ?EncryptedStuff)   AS VARCHAR)

Finally, you can use a certificate to encrypt data, either an existing certificate by importing it into the database server or a new one by using the T-SQL statement CREATE CERTIFICATE to create it. A certificate can also be encrypted through a password or through the Database Master Key. Listing 6 shows the two possibilities:

Listing 6-- Encryption with a passwordCREATE CERTIFICATE MyCertificateName AUTHORIZATION User1   WITH Subject 'My Subject',    EXPIRY_DATE = '12/31/2006',   ENCRYPTION_PASSWORD = 'p@ssw0rd1'-- Encryption with the database master keyCREATE CERTIFICATE MyCertificateName AUTHORIZATION User1   WITH Subject 'My Subject'

Samples related to the cryptography features are also available as a download in the file Encryption.sql.

New Security Functionality

As you have seen from the samples, SQL Server 2005 provides a lot of new functionality in the area of security. The big improvements are without any doubt the cryptography functionalities. The Service Master Key is also a very nice little feature?you don’t have to maintain your own keys because SQL Server handles them for you.