Summary: SQL Server 2005 is the first version of this server software released since Microsoft developed and implemented its Trustworthy Computing initiative. The software is now secure by design, default, and deployment. Microsoft is committed to communicating information about threats, countermeasures, and security enhancements as necessary to keep your data as secure as possible. This paper covers some of the most important new security features in SQL Server 2005. It tells you how, as an administrator, you can install SQL Server securely and keep it that way even as applications and users make use of the data stored within.

Copyright

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

 2007 Microsoft Corporation. All rights reserved.

Microsoft, Windows, Windows NT, and Windows Serverare either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Table of Contents

Introduction 1

Secure Installation and Configuration 1

Configuration tools 3

SQL Server Surface Area Configuration Tool 4

Authentication 7

Password policy enforcement 7

Endpoint authentication 8

Authorization 10

Granular permissions 10

Principals and securables 10

Roles and permissions 11

Metadata security 13

SQL Agent proxies 13

Execution context 16

User/schema separation 17

Database Security 19

Data encryption 20

Code module signing 21

DDL triggers 22

Conclusion 23

Introduction

Security is becoming increasingly important as more networks are connected together. Your organization’s assets must be protected, particularly its databases, which contain your company’s valuable information. Security is one of the critical features of a database engine, protecting the enterprise against myriad threats. The new security features of Microsoft® SQL Server™ 2005 are designed to make it more secure and to make security more approachable and understandable to those who are responsible for data protection.

During the past few years, the world has developed a far more mature understanding of what a secure, computer-based system must be. Microsoft has been in the forefront of this development, and SQL Server is one of the first server products that fully implements that understanding. It enables the important principle of least privilege so you do not have to grant users more permissions than are necessary for them to do their jobs. It provides in-depth tools for defense so that you can implement measures to frustrate even the most skillful attackers.

Much has been written and discussed about Microsoft's Trustworthy Computing initiative that guides all software development at the company. For more information, see the Trustworthy Computing Web site (http://www.microsoft.com/mscorp/twc/default.mspx).

The four essential components of this initiative are:

Secure by design. Software requires a secure design as a foundation for repelling attackers and protecting data.

Secure by default. System administrators should not have to work to make a fresh installation secure; it should be that way by default.

Secure in deployment. Software should help to keep itself updated with the latest security patches and assist in maintenance.

Communications. Communicate best practices and evolving threat information so that administrators can proactively protect their systems.

These guiding principles are evident throughout SQL Server 2005, which provides all the tools you need to secure your databases.

This paper explores the most important new security features for system and database administrators. It starts with a look at how SQL Server 2005 is much easier to install and configure securely. It explores new authentication and authorization features that control access to the server and determine what a user can do once authenticated. It finishes with a look at the database security features an administrator needs to understand in order to provide a secure environment for databases and the applications that access those databases.