SQL Server 2005 Security Best Practices - Operational and Administrative Tasks
SQL Server Technical Article
Writers: Bob Beauchemin, SQLskills
Technical Reviewers: Laurentiu Cristofor, Al Comeau, Sameer Tejani, Devendra Tiwari, Rob Walters, Niraj Nagrani
Published: March 2007
Applies To: SQL Server 2005 SP2
Summary: Security is a crucial part of any mission-critical application. This paper describes best practices for setting up and maintaining security in SQL Server 2005.
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.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, email address, logo, person, place or event is intended or should be inferred.
2007 Microsoft Corporation. All rights reserved.
Microsoft, Active Directory, Windows, Windows Server, and Windows Vista are 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
Surface Area Reduction 4
Service Account Selection and Management 6
Authentication Mode 8
Network Connectivity 9
Lockdown of System Stored Procedures 12
Password Policy 13
Administrator Privileges 14
Database Ownership and Trust 15
Catalog Security 18
Remote Data Source Execution 19
Execution Context 20
Microsoft Baseline Security Analyzer and SQL Server Best Practices Analyzer 24
This white paper covers some of the operational and administrative tasks associated with Microsoft® SQL Server™ 2005 security and enumerates best practices and operational and administrative tasks that will result in a more secure SQL Server system. Each topic describes a feature and best practices. For additional information on the specifics of utilities, features, and DDL statements referenced in this white paper, see SQL Server 2005 Books Online. Features and options that are new or defaults that are changed for SQL Server 2005 are identified. Coding examples for operational tasks use Transact-SQL, so understanding Transact-SQL is required for you to get the most out of this paper.
Surface Area Reduction
SQL Server 2005 installation minimizes the "attack surface" because by default, optional features are not installed. During installation the administrator can choose to install:
Analysis Services Engine
Documentation and Samples
It is a good practice to review which product features you actually need and install only those features. Later, install additional features only as needed. SQL Server 2005 includes sample databases for OLTP, data warehousing, and Analysis Services. Install sample databases on test servers only; they are not installed by default when you install the corresponding engine feature. SQL Server 2005 includes sample code covering every feature of the product. These samples are not installed by default and should be installed only on a development server, not on a production server. Each item of sample code has undergone a review to ensure that the code follows best practices for security. Each sample uses Microsoft Windows® security principals and illustrates the principal of least privilege.
SQL Server has always been a feature-rich database and the number of new features in SQL Server 2005 can be overwhelming. One way to make a system more secure is to limit the number of optional features that are installed and enabled by default. It is easier to enable features when they are needed than it is to enable everything by default and then turn off features that you do not need. This is the installation policy of SQL Server 2005, known as "off by default, enable when needed." One way to ensure that security policies are followed is to make secure settings the default and make them easy to use.
SQL Server 2005 provides a "one-stop" utility that can be used to enable optional features on a per-service and per-instance basis as needed. Although there are other utilities (such as Services in Control Panel), server configuration commands (such as sp_configure), and APIs such as WMI (Windows Management Instrumentation) that you can use, the SQL Server Surface Area Configuration tool combines this functionality into a single utility program. This program can be used either from the command line or via a graphic user interface.
SQL Server Service Area Configuration divides configuration into two subsets: services and connections, and features. Use the Surface Area Configuration for Services and Connections tool to view the installed components of SQL Server and the client network interfaces for each engine component. The startup type for each service (Automatic, Manual, or Disabled) and the client network interfaces that are available can be configured on a per-instance basis. Use the Surface Area Configuration for Features tool to view and configure instance-level features.
The features enabled for configuration are:
Remote use of a dedicated administrator connection
OLE Automation system procedures
System procedures for Database Mail and SQL Mail
Ad hoc remote queries (the OPENROWSET and OPENDATASOURCE functions)
SQL Server Web Assistant
The features enabled for viewing are:
Service Broker endpoint
The SQL Server Surface Area Configuration command-line interface, sac.exe, permits you to import and export settings. This enables you to standardize the configuration of a group of SQL Server 2005 instances. You can import and export settings on a per-instance basis and also on a per-service basis by using command-line parameters. For a list of command-line parameters, use the -? command-line option. You must have sysadmin privilege to use this utility. The following code is an example of exporting all settings from the default instance of SQL Server on server1 and importing them into server2:
sac out server1.out –S server1 –U admin –I MSSQLSERVER
sac in server1.out –S server2
When you upgrade an instance of SQL Server to SQL Server 2005 by performing an in-place upgrade, the configuration options of the instance are unchanged. Use SQL Server Surface Area Configuration to review feature usage and turn off features that are not needed. You can turn off the features in SQL Server Surface Area Configuration or by using the system stored procedure, sp_configure. Here is an example of using sp_configure to disallow the execution of xp_cmdshell on a SQL Server instance:
-- Allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
-- Update the currently configured value for advanced options.
-- Disable the feature.
EXEC sp_configure 'xp_cmdshell', 0
-- Update the currently configured value for this feature.
In SQL Server 2005, SQL Server Browser functionality has been factored into its own service and is no longer part of the core database engine. Additional functions are also factored into separate services. Services that are not a part of the core database engine and can be enabled or disabled separately include:
SQL Server Active Directory Helper
SQL Server Agent
SQL Server FullText Search
SQL Server Browser
SQL Server VSS Writer
The SQL Server Browser service needs to be running only to connect to named SQL Server instances that use TCP/IP dynamic port assignments. It is not necessary to connect to default instances of SQL Server 2005 and named instances that use static TCP/IP ports. For a more secure configuration, always use static TCP/IP port assignments and disable the SQL Server Browser service. The VSS Writer allows backup and restore using the Volume Shadow Copy framework. This service is disabled by default. If you do not use Volume Shadow Copy, disable this service. If you are running SQL Server outside of an Active Directory® directory service, disable the Active Directory Helper.
Best practices for surface area reduction
Install only those components that you will immediately use. Additional components can always be installed as needed.
Enable only the optional features that you will immediately use.
Review optional feature usage before doing an in-place upgrade and disable unneeded features either before or after the upgrade.
Develop a policy with respect to permitted network connectivity choices. Use SQL Server Surface Area Configuration to standardize this policy.
Develop a policy for the usage of optional features. Use SQL Server Surface Area Configuration to standardize optional feature enabling. Document any exceptions to the policy on a per-instance basis.
Turn off unneeded services by setting the service to either Manual startup or Disabled.