Checklist: Securing Your Database Server

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

patterns & practices Developer Center

Improving Web Application Security: Threats and Countermeasures

J.D. Meier, Alex Mackman, Michael Dunner, Srinath Vasireddy, Ray Escamilla and Anandha Murukan

Microsoft Corporation

Published: June 2003

Applies to:

  • Microsoft® SQL Server™ 2000
  • Microsoft Windows® 2000 operating system

See the "patterns & practices Security Guidance for Applications Index" for links to additional security resources.

See the Landing Page for the starting point and a complete overview of Improving Web Application Security: Threats and Countermeasures.

Contents

How to Use This Checklist Installation Considerations for Production Servers Patches and Updates Services Protocols Accounts Files and Directories Shares Ports Registry Auditing and Logging SQL Server Security SQL Server Logins, Users, and Roles SQL Server Database Objects Additional Considerations Staying Secure

How to Use This Checklist

This checklist is a companion to Chapter 18, "Securing Your Database Server." Use it to help you secure a database server and also as a snapshot of the corresponding chapter.

Installation Considerations for Production Servers

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif Upgrade tools, debug symbols, replication support, books online, and development tools are not installed on the production server.
Ff648235.z02bthcm01(en-us,PandP.10).gif Microsoft ® SQL Server® is not installed on a domain controller.
Ff648235.z02bthcm01(en-us,PandP.10).gif SQL Server Agent is not installed if it is not being used by any application.
Ff648235.z02bthcm01(en-us,PandP.10).gif SQL Server is installed on a dedicated database server.
Ff648235.z02bthcm01(en-us,PandP.10).gif SQL Server is installed on an NTFS partition.
Ff648235.z02bthcm01(en-us,PandP.10).gif Windows Authentication mode is selected unless SQL Server Authentication is specifically required, in which case Mixed Mode is selected.
Ff648235.z02bthcm01(en-us,PandP.10).gif A strong password is applied for the sa account or any other member of the sysadmin role. (Use strong passwords for all accounts.)
Ff648235.z02bthcm01(en-us,PandP.10).gif The database server is physically secured.

Patches and Updates

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif The latest service packs and patches have been applied for SQL Server.
(See http://support.microsoft.com/default.aspx?scid=kb;EN-US;290211.)
Ff648235.z02bthcm01(en-us,PandP.10).gif Post service-pack patches have been applied for SQL server.
(See http://www.microsoft.com/technet/security/current.asp?productid=30&servicepackid=0.)

Services

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif Unnecessary Microsoft Windows® services are disabled on the database server.
Ff648235.z02bthcm01(en-us,PandP.10).gif All optional services, including Microsoft Search Service, MSSQLServerADHelper, and SQLServerAgent, are disabled if not used by any applications.
Ff648235.z02bthcm01(en-us,PandP.10).gif The Microsoft Distributed Transaction Coordinator (MS DTC) is disabled if it is not being used by any applications.
Ff648235.z02bthcm01(en-us,PandP.10).gif A least-privileged local/domain account is used to run the various SQL Server services, for example, back up and replication.

Protocols

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif All protocols except TCP/IP are disabled within SQL Server. Check this using the Server Network Utility.
Ff648235.z02bthcm01(en-us,PandP.10).gif The TCP/IP stack is hardened on the database server.

Accounts

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif SQL Server is running using a least-privileged local account (or optionally, a least-privileged domain account if network services are required).
Ff648235.z02bthcm01(en-us,PandP.10).gif Unused accounts are removed from Windows and SQL Server.
Ff648235.z02bthcm01(en-us,PandP.10).gif The Windows guest account is disabled.
Ff648235.z02bthcm01(en-us,PandP.10).gif The administrator account is renamed and has a strong password.
Ff648235.z02bthcm01(en-us,PandP.10).gif Strong password policy is enforced.
Ff648235.z02bthcm01(en-us,PandP.10).gif Remote logons are restricted.
Ff648235.z02bthcm01(en-us,PandP.10).gif Null sessions (anonymous logons) are restricted.
Ff648235.z02bthcm01(en-us,PandP.10).gif Approval is required for account delegation.
Ff648235.z02bthcm01(en-us,PandP.10).gif Shared accounts are not used.
Ff648235.z02bthcm01(en-us,PandP.10).gif Membership of the local administrators group is restricted (ideally, no more than two administration accounts).

Files and Directories

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif Restrictive permissions are configured on SQL Server installation directories (per the guide).
Ff648235.z02bthcm01(en-us,PandP.10).gif The Everyone group does not have permission to access SQL Server installation directories.
Ff648235.z02bthcm01(en-us,PandP.10).gif Setup log files are secured.
Ff648235.z02bthcm01(en-us,PandP.10).gif Tools, utilities, and SDKs are removed or secured.
Ff648235.z02bthcm01(en-us,PandP.10).gif Sensitive data files are encrypted using EFS (This is an optional step. If implemented, use EFS only to encrypt MDF files, not LDF log files).

Shares

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif All unnecessary shares are removed from the server.
Ff648235.z02bthcm01(en-us,PandP.10).gif Access to required shares is restricted (the Everyone group doesn't have access).
Ff648235.z02bthcm01(en-us,PandP.10).gif Administrative shares (C$ and Admin$) are removed if they are not required (Microsoft Management Server (SMS) and Microsoft Operations Manager (MOM) require these shares).

Ports

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif Restrict access to all ports on the server except the ports configured for SQL Server and database instances (TCP 1433 and UDP 1434 by default).
Ff648235.z02bthcm01(en-us,PandP.10).gif Named instances are configured to listen on the same port.
Ff648235.z02bthcm01(en-us,PandP.10).gif Port 3389 is secured using IPSec if it is left open for remote Terminal Services administration
Ff648235.z02bthcm01(en-us,PandP.10).gif The firewall is configured to support DTC traffic (if required by the application).
Ff648235.z02bthcm01(en-us,PandP.10).gif The Hide server option is selected in the Server Network Utility (optional).

Registry

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif SQL Server registry keys are secured with restricted permissions.
Ff648235.z02bthcm01(en-us,PandP.10).gif The SAM is secured (standalone servers only).

Auditing and Logging

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif All failed Windows login attempts are logged.
Ff648235.z02bthcm01(en-us,PandP.10).gif All failed actions are logged across the file system.
Ff648235.z02bthcm01(en-us,PandP.10).gif SQL Server login auditing is enabled.
Ff648235.z02bthcm01(en-us,PandP.10).gif Log files are relocated from the default location and secured with access control lists.
Ff648235.z02bthcm01(en-us,PandP.10).gif Log files are configured with an appropriate size depending on the application security requirement.
Ff648235.z02bthcm01(en-us,PandP.10).gif Where the database contents are highly sensitive or vital, Windows is set to Shut Down mode on overflow of the security logs.

SQL Server Security

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif SQL Server authentication is set to Windows only (if supported by the application).
Ff648235.z02bthcm01(en-us,PandP.10).gif The SQL Server audit level is set to Failure or All.
Ff648235.z02bthcm01(en-us,PandP.10).gif SQL Server runs using a least-privileged account.

SQL Server Logins, Users, and Roles

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif A strong sa password is used (for all accounts).
Ff648235.z02bthcm01(en-us,PandP.10).gif SQL Server guest user accounts are removed.
Ff648235.z02bthcm01(en-us,PandP.10).gif BUILTIN\Administrators server login is removed.
Ff648235.z02bthcm01(en-us,PandP.10).gif Permissions are not granted for the public role.
Ff648235.z02bthcm01(en-us,PandP.10).gif Members of sysadmin fixed server role are limited (ideally, no more than two users).
Ff648235.z02bthcm01(en-us,PandP.10).gif Restricted database permissions are granted. Use of built-in roles, such as db_datareader and db_datawriter, are avoided because they provide limited authorization granularity.
Ff648235.z02bthcm01(en-us,PandP.10).gif Default permissions that are applied to SQL Server objects are not altered.

SQL Server Database Objects

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif Sample databases (including Pubs and Northwind) are removed.
Ff648235.z02bthcm01(en-us,PandP.10).gif Stored procedures and extended stored procedures are secured.
Ff648235.z02bthcm01(en-us,PandP.10).gif Access to cmdExec is restricted to members of the sysadmin role.

Additional Considerations

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif A certificate is installed on the database server to support SSL communication and the automatic encryption of SQL account credentials (optional).
Ff648235.z02bthcm01(en-us,PandP.10).gif NTLM version 2 is enabled by setting LMCompatibilityLevel to 5.

Staying Secure

Check Description
Ff648235.z02bthcm01(en-us,PandP.10).gif Regular backups are performed.
Ff648235.z02bthcm01(en-us,PandP.10).gif Group membership is audited.
Ff648235.z02bthcm01(en-us,PandP.10).gif Audit logs are regularly monitored.
Security assessments are regularly performed.
Ff648235.z02bthcm01(en-us,PandP.10).gif You subscribe to SQL security bulletins at http://www.microsoft.com/technet/security/current.asp?productid=30&servicepackid=0.
Ff648235.z02bthcm01(en-us,PandP.10).gif You subscribe to the Microsoft Security Notification Service at http://www.microsoft.com/technet/security/bulletin/notify.asp.

patterns & practices Developer Center

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

© Microsoft Corporation. All rights reserved.