Microsoft SQL Database Asset Protection Standards:
Jump to navigation
Jump to search
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.