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.

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.

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.

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.

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.

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.

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.

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.

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.