Export (0) Print
Expand All

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

CheckDescription
Ff648235.z02bthcm01(en-us,PandP.10).gifUpgrade tools, debug symbols, replication support, books online, and development tools are not installed on the production server.
Ff648235.z02bthcm01(en-us,PandP.10).gifMicrosoft ® SQL Server® is not installed on a domain controller.
Ff648235.z02bthcm01(en-us,PandP.10).gifSQL Server Agent is not installed if it is not being used by any application.
Ff648235.z02bthcm01(en-us,PandP.10).gifSQL Server is installed on a dedicated database server.
Ff648235.z02bthcm01(en-us,PandP.10).gifSQL Server is installed on an NTFS partition.
Ff648235.z02bthcm01(en-us,PandP.10).gifWindows Authentication mode is selected unless SQL Server Authentication is specifically required, in which case Mixed Mode is selected.
Ff648235.z02bthcm01(en-us,PandP.10).gifA 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).gifThe database server is physically secured.

Patches and Updates

CheckDescription
Ff648235.z02bthcm01(en-us,PandP.10).gifThe 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).gifPost service-pack patches have been applied for SQL server.
(See http://www.microsoft.com/technet/security/current.asp?productid=30&servicepackid=0.)

Services

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

Protocols

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

Accounts

CheckDescription
Ff648235.z02bthcm01(en-us,PandP.10).gifSQL 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).gifUnused accounts are removed from Windows and SQL Server.
Ff648235.z02bthcm01(en-us,PandP.10).gifThe Windows guest account is disabled.
Ff648235.z02bthcm01(en-us,PandP.10).gifThe administrator account is renamed and has a strong password.
Ff648235.z02bthcm01(en-us,PandP.10).gifStrong password policy is enforced.
Ff648235.z02bthcm01(en-us,PandP.10).gifRemote logons are restricted.
Ff648235.z02bthcm01(en-us,PandP.10).gifNull sessions (anonymous logons) are restricted.
Ff648235.z02bthcm01(en-us,PandP.10).gifApproval is required for account delegation.
Ff648235.z02bthcm01(en-us,PandP.10).gifShared accounts are not used.
Ff648235.z02bthcm01(en-us,PandP.10).gifMembership of the local administrators group is restricted (ideally, no more than two administration accounts).

Files and Directories

CheckDescription
Ff648235.z02bthcm01(en-us,PandP.10).gifRestrictive permissions are configured on SQL Server installation directories (per the guide).
Ff648235.z02bthcm01(en-us,PandP.10).gifThe Everyone group does not have permission to access SQL Server installation directories.
Ff648235.z02bthcm01(en-us,PandP.10).gifSetup log files are secured.
Ff648235.z02bthcm01(en-us,PandP.10).gifTools, utilities, and SDKs are removed or secured.
Ff648235.z02bthcm01(en-us,PandP.10).gifSensitive 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

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

Ports

CheckDescription
Ff648235.z02bthcm01(en-us,PandP.10).gifRestrict 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).gifNamed instances are configured to listen on the same port.
Ff648235.z02bthcm01(en-us,PandP.10).gifPort 3389 is secured using IPSec if it is left open for remote Terminal Services administration
Ff648235.z02bthcm01(en-us,PandP.10).gifThe firewall is configured to support DTC traffic (if required by the application).
Ff648235.z02bthcm01(en-us,PandP.10).gifThe Hide server option is selected in the Server Network Utility (optional).

Registry

CheckDescription
Ff648235.z02bthcm01(en-us,PandP.10).gifSQL Server registry keys are secured with restricted permissions.
Ff648235.z02bthcm01(en-us,PandP.10).gifThe SAM is secured (standalone servers only).

Auditing and Logging

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

SQL Server Security

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

SQL Server Logins, Users, and Roles

CheckDescription
Ff648235.z02bthcm01(en-us,PandP.10).gifA strong sa password is used (for all accounts).
Ff648235.z02bthcm01(en-us,PandP.10).gifSQL Server guest user accounts are removed.
Ff648235.z02bthcm01(en-us,PandP.10).gifBUILTIN\Administrators server login is removed.
Ff648235.z02bthcm01(en-us,PandP.10).gifPermissions are not granted for the public role.
Ff648235.z02bthcm01(en-us,PandP.10).gifMembers of sysadmin fixed server role are limited (ideally, no more than two users).
Ff648235.z02bthcm01(en-us,PandP.10).gifRestricted 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).gifDefault permissions that are applied to SQL Server objects are not altered.

SQL Server Database Objects

CheckDescription
Ff648235.z02bthcm01(en-us,PandP.10).gifSample databases (including Pubs and Northwind) are removed.
Ff648235.z02bthcm01(en-us,PandP.10).gifStored procedures and extended stored procedures are secured.
Ff648235.z02bthcm01(en-us,PandP.10).gifAccess to cmdExec is restricted to members of the sysadmin role.

Additional Considerations

CheckDescription
Ff648235.z02bthcm01(en-us,PandP.10).gifA 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).gifNTLM version 2 is enabled by setting LMCompatibilityLevel to 5.

Staying Secure

CheckDescription
Ff648235.z02bthcm01(en-us,PandP.10).gifRegular backups are performed.
Ff648235.z02bthcm01(en-us,PandP.10).gifGroup membership is audited.
Ff648235.z02bthcm01(en-us,PandP.10).gifAudit logs are regularly monitored.
 Security assessments are regularly performed.
Ff648235.z02bthcm01(en-us,PandP.10).gifYou subscribe to SQL security bulletins at http://www.microsoft.com/technet/security/current.asp?productid=30&servicepackid=0.
Ff648235.z02bthcm01(en-us,PandP.10).gifYou 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.

Show:
© 2014 Microsoft