Sql server 2005 Security Best Practices Operational and Administrative Tasks
|
|  Yüklə 274 Kb.
|
SQL Server 2005 Security Best Practices - Operational and Administrative TasksSQL 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
CopyrightThe 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 ContentsIntroduction 4Surface Area Reduction 4Service Account Selection and Management 6Authentication Mode 8Network Connectivity 9Lockdown of System Stored Procedures 12Password Policy 13Administrator Privileges 14Database Ownership and Trust 15Schemas 16Authorization 17Catalog Security 18Remote Data Source Execution 19Execution Context 20Encryption 21Auditing 23Microsoft Baseline Security Analyzer and SQL Server Best Practices Analyzer 24Patching 25Conclusion 25IntroductionThis 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 ReductionSQL Server 2005 installation minimizes the "attack surface" because by default, optional features are not installed. During installation the administrator can choose to install:
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:
The features enabled for viewing are:
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 GO -- Update the currently configured value for advanced options. RECONFIGURE GO -- Disable the feature. EXEC sp_configure 'xp_cmdshell', 0 GO -- Update the currently configured value for this feature. RECONFIGURE GO
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
|