SQL Server 2005 Security

Microsoft's dedication to trustworthy computing is clearly visible in SQL Server 2005. SQL Server 2005 is secure by design, secure by default, and secure by deployment.

To make sure that SQL Server 2005 is secure by design, every team member on the SQL Server 2005 product attended the security training, and each component/feature, such as Service Broker and SQLCLR, has gone through threat analysis and testing. Code reviews, penetration testing, use of automated code tools, scenario-based testing, and low privileged testing are some other techniques that the SQL Server team used to ensure that the product is secure by design. All existing features have been reevaluated to make them more secure, and every new feature has undergone thorough scrutiny to make sure it meets the security standards.

Secure by default means that when an administrator first installs SQL Server 2005, the data environment is in its most locked-down state, with secure settings for all configuration options. SQL Server 2005 setup installs only the necessary components, features such as SQLCLR are not enabled by default, services such as Agent and Full-Text Search are set to manual start, and Database Mail is not configured by default. These types of changes minimize the attack surface, making the system secure by default.

Secure by deployment means providing administrators with good tools for security assessment, auditing and reducing the attack surface, assisting in automating monitoring, and following the principle of least privilege. The SQL Server Management Studio and Surface Area Configuration tools, DDL triggers, the new auditing capabilities, and departure from the sysadmin requirement to run tools such as Profiler are certainly steps toward making SQL Server solutions secure by deployment.

Let's now briefly look at some of the new security-related features introduced in SQL Server 2005.

Password Policy Enforcement and Strengthening of SQL Authentication

Windows Authentication is still the recommended authentication mechanism in SQL Server 2005. However, if you have to use SQL authentication, you should know that two big improvements have been made to enhance SQL authentication.

SQL Server 2005 makes sure that you specify a strong password when creating a SQL login. For instance, the CREATE LOGIN statement will fail if you specify a blank password, specify a password that is the same as the login name, or use words such as "Admin" or "Administrator." None of the following statements will succeed:

CREATE LOGIN TEST1 WITH PASSWORD = ''; GO CREATE LOGIN TEST1 WITH PASSWORD = 'TEST1'; GO CREATE LOGIN TEST1 WITH PASSWORD = 'Admin'; GO CREATE LOGIN TEST1 WITH PASSWORD = 'SA'; GO CREATE LOGIN TEST1 WITH PASSWORD = 'Administrator'; GO

All these statements fail, with the "Password validation failed" error.

In addition, if you are running on Windows Server 2003, which is, by the way, the recommended platform for SQL Server 2005, the same complexity and expiration policies used in Windows Server 2003 can be used for SQL authenticated logins.

The second enhancement is that SQL Server 2005 encrypts the login packets. In other words, unlike in previous releases, SQL Server 2005 never sends SQL authentication details in clear text. If SSL is available, SQL Server uses it; otherwise, it generates a 512-bit certificate and uses it for encryption.

UserSchema Separation

According to the ANSI SQL-92 standard, the sole purpose of a schema is to act as a namespace, to group related objects under one umbrella. However, SQL Server 2000 and previous releases did not differentiate between a schema and a user. A user was essentially treated as a schema. If an administrator wanted to delete a user, he or she needed to delete all the objects owned by that user or change the owner of all the objects owned by the user being deleted. Changing the object owner very often required changing the application code.

SQL Server 2005 breaks the userschema tight coupling, treating schemas as per the ANSI SQL-92 standardas meaningful names that can be used to group related objects. For example, in the AdventureWorks sample database, objects are grouped under schemas named Sales, HumanResources, Person, and so on.

Schemas are owned by users, such as dbo or some user UserX. Now, if an administrator wanted to drop the user UserX, all the administrator would have to do is change the schema owner and delete the user. As long as that user does not own any schemas and is not used for the EXECUTE AS context (discussed later in this chapter), the administrator should be able to delete the user. The applications still continue to access the objects by using schema names, such as Sales.Store or HumanResources.Employee.

With SQL Server 2005, every user has a default schema, and if the user has appropriate permissions, he or she can create and own multiple schemas. Each schema can then contain zero or more objects. While resolving the object name, SQL Server follows a simple rule to look for the object under the user's default schema, and if it is not found, SQL Server uses dbo as the schema name.

Note that many users can have the same schema as their default schema. No two objects in a schema can have the same name. However, it is possible to have tables with the same name in different schemas. When you create a new database, SQL Server 2005 creates several schemas in the database, such as schemas named dbo, INFORMATION_SCHEMA, sys, and guest; and also a schema is created for every database role. All the catalog views, dynamic management objects, and system-stored procedures reside in the sys schema. Userschema separation is discussed in more detail, with examples, in Chapter 7.

Encryption Support

SQL Server 2005 provides native support for symmetric keys, asymmetric keys, and certificates that can be used to encrypt and decrypt data. Keys and certificates can be stored inside the database and later used for various purposes, such as authentication and encryption/decryption. SQL Server 2005 provides functions such as EncryptByKey, EncryptByPassPhrase, EncryptByAsmKey, EncryptByCert, and their decryption counterparts to encrypt and decrypt data by using keys, certificates, or pass phrases.

Refer to Chapter 7 for more details on keys, certificates, and encryption support in SQL Server 2005.

Module Execution Context and Impersonation

With previous SQL Server releases, if a user had EXECUTE permission on a module, SQL Server did not check permission on the objects accessed in the module, as long as the referred objects were owned by the same user as the module owner. This concept is known as ownership chaining. As soon as a module refers to an object that is owned by a different user than the module owner, the ownership chain breaks, and SQL Server checks whether the user executing the module has permission on the object being accessed.

SQL Server 2005 introduces a new T-SQL construct, EXECUTE AS, that can be used in defining the modules (stored procedures, functions, triggers, and queues) to specify the execution context that the module should run under. SQL Server 2005 uses the specified context to impersonate and run the module. The default EXECUTE AS context is CALLER. This ensures that the engine behaves as in the previous release, supporting the ownership chain concept. However, the module creator can specify whether the module should run under the context of SELF, OWNER, or a specific user.

In addition to using EXECUTE AS while defining the modules, you can also use EXECUTE AS USER = '<username>' within T-SQL batches to change the execution context, and you can use REVERT to change the execution context back to the user used to connect to the instance.

SQL Server 2005 introduces several other security-related features, such as granular permissions control, metadata or catalog security, and SQLCLR security. Chapter 7 is dedicated to SQL Server 2005 security and auditing.

In summary, the enhanced security model, granular permission control, secure-by-default deployment, password policy enforcement, and data encryption make SQL Server 2005 a secure and reliable platform for database and analytical applications.