Security Best Practices Checklist

Article
01/28/2010

Updated : May 16, 2003

The following checklist summarizes the best practices discussed in this paper. For details, refer to the discussion above.

Administrator Checklist

Setting Up the Environment Prior to Installation
Physical security	Ensure the physical security of your server.
Firewalls	Put a firewall between your server and the Internet. Always block TCP port 1433 and UDP port 1434 on your perimeter firewall. If named instances are listening on additional ports, block those too. In a multi-tier environment, use multiple firewalls to create screened subnets.
Isolation of services	Isolate services to reduce the risk that a compromised service could be used to compromise others. Never install SQL Server on a domain controller. Run separate SQL Server services under separate Windows accounts. In a multi-tier environment, run Web logic and business logic on separate computers.
Service accounts	Create Windows accounts with the lowest possible privileges for running SQL Server services.
File System	Use NTFS. Use RAID for critical data files.

Installation
Latest version and service pack	Always install the latest service packs and security patches.
Service accounts	Run SQL Server services with the lowest possible privileges. Use Enterprise Manager to associate services with Windows accounts.
Authentication mode	Require Windows Authentication for connections to SQL Server.
Strong passwords	Always assign a strong password to the sa account, even when using Windows Authentication. Always use strong passwords for all SQL Server accounts.

Configuration Options and Settings After Installation
Delete or secure old setup files	Delete or archive the following files after installation: sqlstp.log, sqlsp.log, and setup.iss in the <systemdrive>:\Program Files\Microsoft SQL Server\MSSQL\Install folder for a default installation, and the <systemdrive>:\Program Files\Microsoft SQL Server\ MSSQL$<Instance Name>\Install folder for named instances. If the current system is an upgrade from SQL Server 7.0, delete the following files: setup.iss in the %Windir% folder, and sqlsp.log in the Windows Temp folder.
Choose static ports for named instances	Assign static ports to named instances of SQL Server.
Set login auditing level	Set login auditing level to failure or all.
Enable security auditing	Enable security auditing of Sysadmin actions, fixed role membership changes, all login related activity, and password changes. After selecting appropriate auditing options, you should script the audit, wrap it in a stored procedure,and mark that stored procedure for AutoStart.
Secure sa even in Windows Authentication Mode	Assign a strong password to the sa account, even on servers that are configured to require Windows Authentication.
Remove sample databases	Remove sample databases from production servers.

Secure Operation
Security model	Learn to work with the SQL Server security model.
Backup policy	Back up all data regularly and store copies in a secure off-site location. Test your disaster recovery system.
Surface and feature reduction	Reduce the surface area of your system that is exposed to attack by running only those services and features needed in your environment.
Administrator reduction	Restrict membership of the sysadmin fixed server role to a few trusted individuals.
Strong passwords	Ensure that you use complex passwords for all SQL Server accounts.
Cross database ownership chaining	Disable cross database ownership chaining if your system does not use it.
Xp_cmdshell	By default, only members of the sysadmin role can execute xp_cmdshell. You should not change this default. Do not grant execute permission on xp_cmdshell to users who are not members of the sysadmin role.
Encryption	Install a certificate to enable SSL connections. Certificates should use the fully-qualified DNS name of the server. Use the SQL Server service account to encrypt database files with EFS. If your application requires data encryption, consider using the products of such vendors as Protegrity and Application Security Inc.
Roles and groups	Collect users into SQL Server roles or Windows groups to simplify permissions administration.
Permissions	Never grant permissions to the public database role.
Distributed queries	When setting up SQL Server in an environment that supports distributed queries, use linked servers rather than remote servers. Allow linked server access only to those logins that need it. Disable ad hoc data access on all providers except SQL OLE DB, for all users except members of the sysadmin fixed server role. Allow ad hoc data access only on trusted providers.
Guest accounts	Do not enable the guest account.
Service accounts	If you need to change the account associated with a SQL Server service, use SQL Server Enterprise Manager. If you change multiple services, you must apply the changes to each service separately using Enterprise Manager.

Recommended Periodic Administrative Procedures
Microsoft Baseline Security Analyzer	Add MBSA to your weekly maintenance schedule, and follow up on any security recommendations that it makes.
Scanning logins	Periodically scan for accounts with NULL passwords and remove them or assign them strong passwords. Delete unused accounts.
Enumerate fixed role membership	Periodically scan fixed server and database roles to ensure that membership is only granted to trusted individuals.
Start-up procedures	Verify the safety of stored procedures that have been marked for AutoStart.
Login-to-user mapping	Ensure that the mapping between database users and logins at the server level is correct. Run sp_change_users_login with the report option regularly to ensure that the mapping is as expected.
Direct catalog updates	Do not allow direct catalog updates.
Cross database ownership chaining	Use sp_dboption to enumerate and validate databases for which cross database ownership chaining has been enabled.

Best Practices for Patching Instances
Instance detection and enumeration	Keep an inventory of all versions, editions, and languages of SQL Server for which you are responsible. Include instances of MSDE in your inventory. Use SQL Scan and SQL Check, available from the Microsoft Web site, to scan for instances of SQL Server within your domain.
Bulletins	Subscribe to Microsoft security bulletins.
Patch application	Maintain test systems that match the configuration of you production systems, and are readily available for testing new patches. Test patches carefully before applying them to production systems. Consider patching development systems with relatively little testing.

Developer Checklist

In addition to all of the items above, the following should be considered best practices for developers.

General
Use ownership chaining effectively	Use ownership chaining within a single database to simplify permissions management. Avoid using cross database ownership chaining when possible. If you must use cross database ownership chaining, ensure that the two databases are always deployed as a single administrative unit.
Use roles to simplify permission management and ownership	Assign permissions to roles rather than directly to users. Objects may be owned by roles, rather than directly by users, if you want to avoid application changes when the owning user is dropped.
Turn on encryption (SSL or IPSEC)	Enable encrypted connections to your server, and consider allowing only encrypted connections. When allowing SQL Server Authentication, you are strongly urged to encrypt either the network layer with IPSec or the session with SSL.
Do not propagate SQL Server errors back to user	Your application should not return SQL Server errors to the end user. Log them instead, or transmit them to the system administrator.
Prevent SQL injection	Defend against SQL injection by validating all user input before transmitting it to the server. Limit the scope of possible damage by permitting only minimally privileged accounts to send user input to the server. Run SQL Server itself with the least necessary privileges.

Multi-tier Options
Same/trusted domain (complete Windows Authentication)	If the application server and the database server are within the same domain, or within trusted domains, you should use Windows Authentication and configure for "full provisioning" in which all client contexts are tunneled to SQL Server. This makes it possible to audit all users who access SQL Server, enables Windows security policy enforcement, and makes it unnecessary to store credentials in the middle tier. In this scenario, the client connects to the application server, which in turn impersonates the client and connects to SQL Server. Every user on the application server must have a valid Windows login on the database server and delegation must be enabled. All systems interacting in this scenario, including the Domain Controller, must run Windows 2000 or higher. The account the application is running under must be trusted for delegation (that is, the Active Directory option Account is trusted for delegation must be turned on for this account). The client account must be able to be delegated (ensure that the Active Directory user account option Account is trusted and cannot be delegated is unchecked). The application service must have a valid Service Principal Name (SPN). Note: Full provisioning is not recommended in cross-enterprise or Internet-scale installations, when your security plan calls for minimizing user access to the database server, or in enterprises with policies prohibiting delegation.
Mixed scenario (partial Windows Authentication)	When the Internet-facing tier does not have an individual Windows domain account for every possible user, the recommended scenario is to divide authentication into stages. The outer tier (which authenticates users) should use SSL to encrypt at least credentials, if not the entire session. It should connect to the database server using Windows Authentication, forwarding transaction information under a separate security context that is low privileged, with only the permissions necessary to perform its function. This effectively uses the middle tier as an additional layer of defense between your server and the Internet. Note: Using SQL Server Authentication between the middle tier and SQL Server is not recommended, because of the need to store credentials. If you must use SQL Server Authentication between the middle tier and SQL Server, you should create several accounts, with different levels of privileges corresponding to different classes of users. This requires that you add logic to the middle tier to allocate connections according to the desired privilege level.
Different non-trusted domains or no domains (no Windows Authentication)	When Windows Authentication between tiers is not possible, you should require SSL encryption of the login sequence. Encrypting the entire session is preferable. You should also use DPAPI to encrypt credentials that must be stored. You should store encrypted credentials in a registry key protected with an ACL.

Same/trusted domain (complete Windows Authentication)

If the application server and the database server are within the same domain, or within trusted domains, you should use Windows Authentication and configure for "full provisioning" in which all client contexts are tunneled to SQL Server. This makes it possible to audit all users who access SQL Server, enables Windows security policy enforcement, and makes it unnecessary to store credentials in the middle tier. In this scenario, the client connects to the application server, which in turn impersonates the client and connects to SQL Server.

Every user on the application server must have a valid Windows login on the database server and delegation must be enabled.
All systems interacting in this scenario, including the Domain Controller, must run Windows 2000 or higher.
The account the application is running under must be trusted for delegation (that is, the Active Directory option Account is trusted for delegation must be turned on for this account).
The client account must be able to be delegated (ensure that the Active Directory user account option Account is trusted and cannot be delegated is unchecked).
The application service must have a valid Service Principal Name (SPN).

Note: Full provisioning is not recommended in cross-enterprise or Internet-scale installations, when your security plan calls for minimizing user access to the database server, or in enterprises with policies prohibiting delegation.

Mixed scenario (partial Windows Authentication)

When the Internet-facing tier does not have an individual Windows domain account for every possible user, the recommended scenario is to divide authentication into stages. The outer tier (which authenticates users) should use SSL to encrypt at least credentials, if not the entire session. It should connect to the database server using Windows Authentication, forwarding transaction information under a separate security context that is low privileged, with only the permissions necessary to perform its function. This effectively uses the middle tier as an additional layer of defense between your server and the Internet.

Note: Using SQL Server Authentication between the middle tier and SQL Server is not recommended, because of the need to store credentials. If you must use SQL Server Authentication between the middle tier and SQL Server, you should create several accounts, with different levels of privileges corresponding to different classes of users. This requires that you add logic to the middle tier to allocate connections according to the desired privilege level.

Different non-trusted domains or no domains (no Windows Authentication)

When Windows Authentication between tiers is not possible, you should require SSL encryption of the login sequence. Encrypting the entire session is preferable.

You should also use DPAPI to encrypt credentials that must be stored.
You should store encrypted credentials in a registry key protected with an ACL.

Software Vendor Checklist

In addition to all of the items above, the following security development practices have proven useful in increasing the quality and security of code in various development environments.

Security Processes
Understanding various security issues	Ensure that members of your development team understand major security issues: current threats, security trends, changing security environments, and attack scenarios. Require relevant security training for all developers and testers. Increase the awareness of issues like cross-site scripting, buffer overflows, SQL injection, and dangerous APIs. Identify specific categories of threats that apply to your product — for example, denial of service, escalation of privileges, spoofing, data tampering, information disclosure and repudiation. Analyze security threats to your product, component-by-component. Create a security threat checklist based on your product. Add security reviews to all stages (from design to testing) of your product development cycle.
MSDE installations	If you distribute MSDE with your application, the following additional guidance applies: Install MSDE using "Windows security mode" as the default. Never install a blank sa password. When distributing MSDE to your customers, you should use the Microsoft-supplied installer rather than merge modules. When installing an instance of MSDE that will operate only as a local data store, you should disable the Server Net-Libraries. If your product includes MSDE, you should make this known to your customers. In the future, they may need to install or accept MSDE-specific software updates. MSDE installs SQL Server Agent by default, but leaves the Service startup type to "Manual." If your application does not use SQL Server Agent, you should change this to "Disabled." Include security best practice information in your product documentation.

Understanding various security issues

Ensure that members of your development team understand major security issues: current threats, security trends, changing security environments, and attack scenarios.
Require relevant security training for all developers and testers.
Increase the awareness of issues like cross-site scripting, buffer overflows, SQL injection, and dangerous APIs.
Identify specific categories of threats that apply to your product — for example, denial of service, escalation of privileges, spoofing, data tampering, information disclosure and repudiation.
Analyze security threats to your product, component-by-component.
Create a security threat checklist based on your product.
Add security reviews to all stages (from design to testing) of your product development cycle.

MSDE installations

If you distribute MSDE with your application, the following additional guidance applies:

Install MSDE using "Windows security mode" as the default.
Never install a blank sa password.
When distributing MSDE to your customers, you should use the Microsoft-supplied installer rather than merge modules.
When installing an instance of MSDE that will operate only as a local data store, you should disable the Server Net-Libraries.
If your product includes MSDE, you should make this known to your customers. In the future, they may need to install or accept MSDE-specific software updates.
MSDE installs SQL Server Agent by default, but leaves the Service startup type to "Manual." If your application does not use SQL Server Agent, you should change this to "Disabled." Include security best practice information in your product documentation.

Appendix: Finding More Information

Recommended Books:

Writing Secure Code, Second Edition, by Michael Howard and David LeBlanc.

This book makes a useful training resource, reviewing the most common security flaws in designing/coding and testing components/applications. It contains security best practices and checklists. It also covers strategies in designing secure applications, writing robust code that can withstand repeated attacks, and testing applications for security flaws.

Hacking Exposed Windows 2000, by Joel Scambray and Stuart McClure.

This book adopts the approach of a hacker. It goes into details of how to break into Windows 2000 machines and thereby increases awareness of how to prevent such attacks. It also has an entire section dedicated to SQL Server and how it can be used to crack the system as a whole.

Designing Secure Web-Based Applications for Microsoft® Windows® 2000, by Michael Howard.

This book gives you a solid foundation in Microsoft Windows 2000, Internet Explorer, Internet Information Services, SQL Server, and COM+ security concepts. It explains the key software design considerations for various categories and levels of security and shows how isolated security "islands" interact. It also explains core security issues such as risk analysis, threats, authentication, authorization, and privacy, and it shows how you can mitigate risks by applying the appropriate security to your environment and applications. Managers, developers, and testers can all use the knowledge in this book to look at components from a security perspective, perform threat analysis and take appropriate responses by enhancing the security of code/design and enhancing test suites appropriately.

Building Secure Microsoft ASP.NET Applications, by J.D.Meier (among others).

This guide presents a practical, scenario-driven approach to designing and building secure ASP.NET applications for Windows 2000 and version 1.0 of the .NET Framework. It focuses on the key elements of authentication, authorization, and secure communication within and across the tiers of distributed .NET Web applications.

Firewalls and Internet Security, second edition, by William R. Cheswick, Steven M. Bellovin, and Aviel D. Rubin. Addison-Wesley, 2003.

This is the standard introduction to Internet security. Highly recommended.

Recommended Tools, Papers, and Presentations

The Microsoft Baseline Security Analyzer Tool. This tool helps analyze the security of the system. It is recommended that administrators run this tool on a periodic basis. This tool performs some SQL Server specific checks.

The SQL Server Critical Update Wizard.

Microsoft SQL Server and Security Sites

The area of the Microsoft Web site dedicated to security.

SQL Server product site with links to download locations for the latest service packs, latest bulletins, and other resources.

TechNet SQL Server Security Center with information regarding Troubleshoot and Maintain, Set Up, Configure, and Administer, Security Topics

MSDN SQL Server resource site

White Papers

SQL Server Developer Center. Contains recent technical white papers and downloads.

SQL Server 2000 Security White Paper This white paper was written when SQL Server 2000 was first released, and forms the basis for parts of this paper.