Microsoft SQL Database Asset Protection Standards:: Difference between revisions
Jump to navigation
Jump to search
(4 intermediate revisions by the same user not shown) | |||
Line 71: | Line 71: | ||
===Administrator reduction=== | ===Administrator reduction=== | ||
* Restrict membership of the sysadmin fixed server role to a few trusted individuals. | * 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== | |||
* [http://www.microsoft.com/technet/security/tools/mbsahome.mspx 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== | |||
* [http://www.microsoft.com/security/default.mspx Microsoft Security website.] |
Latest revision as of 13:53, 14 May 2007
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.