Top 10 security enhancements in SQL Server 2005

As database systems continue to be more widely used to back stores of networked applications, it is increasingly necessary to focus on security. Although much of the buzz surrounding SQL Server 2005 has been from a development perspective, Microsoft's new database management system (DBMS) includes many great new features that can help both developers and DBAs lock down the database system and protect access to sensitive data.

The following is a high-level list, in no particular order, of perhaps the 10 most significant security enhancements in SQL Server 2005. Ordering these by importance would be difficult, as every DBA has very different needs.

TABLE OF CONTENTS
   Impersonation
   Surface Area Configuration tool
   User-schema separation
   Granular permission sets
   Password policies
   Encryption
   Limited metadata visibility
   Security catalog views
   DDL triggers
   Mapping Windows user credentials

Impersonation

Return to Table of Contents

Impersonation, or context switching, refers to the process of temporarily changing a connection's security settings in order to gain access to a resource. This is especially important in systems designed from a least privilege perspective. Granting users only the minimum possible permission to resources and using impersonation to selectively elevate permissions when necessary can help to reduce overall exposure.

In SQL Server 2005, the EXECUTE AS and SETUSER statements are used to control context switching. For more information on these statements, see this excerpt from A First Look at SQL Server 2005 for Developers.

Surface Area Configuration tool

Return to Table of Contents

SQL Server 2005 is a large product -- large enough to include many new features that, potentially, can be used as vectors for security exploits. To prevent such events, it's important to be able to easily and quickly view the status of -- and configure -- each of these features. If a feature is not being used for the applications supported by the database, it should be disabled in order to prevent the possibility of misuse.

The Surface Area Configuration tool is a utility provided with SQL Server 2005 that allows DBAs to manage external resources from a single place. Features such as CLR integration, SQL Mail and Native XML Web Services can all be configured using this tool. Although each of these features could be configured using Transact-SQL (T-SQL), the Surface Area Configuration tool provides an easy-to-use graphical user interface for doing so.

For more information about this tool, see the Developer.com article Managing the "Surface Area" of SQL Server 2005.

User-schema separation

Return to Table of Contents

In SQL Server 2005, objects in a database are no longer necessarily owned by a user. Instead, they are contained within a schema, a logical container to which permissions can be granted. This separation means that users can be assigned permissions to multiple objects in one fell swoop, rather than the DBA having to assign permissions one at a time, as in previous versions of SQL Server. In addition, deleting users from a database no longer means that the objects they own must also be removed or reassigned.

For more information on schemas, see my tip Granting permissions in SQL Server 2005.

Granular permission sets

Return to Table of Contents

In previous versions of SQL Server, DBAs had very few choices when it came to granting access to server resources. Pretty much the entire permissions scheme boiled down to a set of rigid "fixed server roles," which never seemed to fit any situation perfectly. For instance, it was impossible to give a developer access to run a Profiler trace without giving the same person access to every other resource on the server!

Luckily, these fixed roles are no longer necessary in SQL Server 2005, thanks to the new granular permission sets feature. It's now possible to assign permissions to virtually every server resource, one at a time. Users can be given access to any combination of resources -- or only specific resources. The system is as flexible as a DBA needs it to be.

For more information on granular permissions, again see my tip Granting Permissions in SQL Server 2005.

Password policies

Return to Table of Contents

When installed on Windows Server 2003, SQL Server 2005 can exploit the operating system's password policies for SQL Server logins. This translates into more secure passwords and fewer opportunities for brute-force attacks to occur on the database server. SQL Server 2005 supports both password complexity rules (ensuring minimum length, enforcing combinations of alphabetical and numeric characters, and so on) and password expiration policies (ensuring that old passwords must be changed).

To use these features, DBAs can set the CHECK_POLICY and CHECK_EXPIRATION options using the ALTER LOGIN statement.

Encryption

Return to Table of Contents

Most databases that store sensitive data use encryption techniques to secure that data. Until now, such techniques could only be realized in the application tier, using technologies like the Microsoft Base Cryptographic Provider.

SQL Server 2005 reduces the need for application-based cryptography by natively exposing encryption technologies. DBAs and developers can now set up a certificate store, create symmetric or asymmetric keys, and use a variety of algorithms, including triple DES (Data Encryption Standard), RC4 and AES (Advanced Encryption Standard).

Encryption is a huge topic. For more information on what SQL Server 2005 provides in this area, see the Database Journal article SQL Server 2005 Security - Part 3 Encryption.

Limited metadata visibility

Return to Table of Contents

In earlier versions of SQL Server, metadata was not secured based on access control mechanisms. A user may not have been able to select data from a certain table, but he or she could still see that the table existed using a system table such as sysobjects or an INFORMATION_SCHEMA view, such as INFORMATION_SCHEMA.TABLES.

SQL Server 2005 DBAs now have one less thing to worry about: The database system's catalog views (including the compatibility views and the INFORMATION_SCHEMA views) are now secured so users will only see objects they have access to. This is helpful both to DBAs worried about securing data and to users who might not want to see resources they are unable to access

For more information on SQL Server 2005's catalog views, see my tip Say goodbye to system tables in SQL Server 2005.

Security catalog views

Return to Table of Contents

In addition to the security of catalog views that contain information about database objects (described in the previous item), SQL Server 2005 includes a series of comprehensive views that allow DBAs to quickly get information about access rights.

The monolithic sys.database_permissions view contains information about all permissions granted in a given database. This view exposes data with the same level of granularity at which permissions can be applied, making it easy for DBAs to determine who has access to what. A similar view, sys.server_permissions, contains information about server-level grants.

Other security views expose information about principles, roles and encryption-related data. See SQL Server 2005's Books Online for more information about these views.

DDL triggers

Return to Table of Contents

SQL Server DBAs have long exploited the power of triggers to embed event-based logic in data manipulation operations. So-called DML (Data Manipulation Language) triggers have existed in the product for several years.

Now this same power can be extended to DDL (Data Definition Language) statements. DBAs can write server or database-level triggers that fire on such events as creating or dropping a table, adding a server login or altering a database. From a security and auditing standpoint, these triggers are invaluable. DBAs can now very easily program automatic logging triggers to capture events as they occur.

For more information on SQL Server 2005 DDL triggers, see the Developer.com article Using DDL Triggers to Manage SQL Server 2005.

Mapping Windows user credentials

Return to Table of Contents

In SQL Server 2005, logins used to access external resources are no longer bound by the access rights of the SQL Server service account. DBAs can use a new statement, CREATE CREDENTIAL, to create an access point that can be mapped to one or more logins.

This new method will allow for greater flexibility when working with SQLCLR modules or SQL Server Agent tasks that require access to external resources. For instance, if a SQL Server Agent task requires access to a remote file for a nightly data import, the SQL Server service account will no longer need access to browse the remote server to retrieve the file.

Conclusion

By exploiting the new security features in SQL Server 2005, DBAs and developers will be able to create database applications that are more flexible and less prone to attack. As security continues to become increasingly important in our information-obsessed society, database professionals will need more tools to fight against threats. SQL Server 2005 meets this challenge with these and other security enhancements.

About the author: Adam Machanic is a database-focused software engineer, writer and speaker based in Boston, Mass. He has implemented SQL Server for a variety of high-availability OLTP and large-scale data warehouse applications, and also specializes in .NET data access layer performance optimization. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified Professional. Machanic is co-author of Pro SQL Server 2005, published by Apress.

More information from SearchSQLServer.com