Microsoft SQL Database Asset Protection Standards:

SQL 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

• 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

• Add Microsoft Baseline Security Analyzer 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.

Recommended Tools, Papers, and Presentations

• 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.

References

• Microsoft Security website.