Document 219644
Your Question Article: 00083 Question: How to Secure SQL Server 2000? Net Report Answer Introduction This Article explains ten tasks you can do today to improve the security of your SQL Server installation: Task 1: Installing the Most Recent Service Pack. Task 2: Assessing Your Servers Security with Microsoft Baseline Security Analyzer (MBSA). Task 3: Using Windows Authentication Mode. Task 4: Isolating Your Server and Backing It Up Regularly. Task 5: Assigning a Strong sa Password. Task 6: Limiting the Privilege Level of SQL Server Services. Task 7: Disabling SQL Server Ports on Your Firewall. Task 8: Using the Most Secure File System. Task 9: Deleting or Securing Old Setup Files. Task 10: Auditing Connections to SQL Server. Appendix A: Ongoing Security Maintenance 1 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Table of Contents Page Table of Contents ..................................................................................... 2 Task 1: Installing the Most Recent Service Pack. ..................................... 3 1.1 Downloading SQL Server 2000 Service Pack 3a (SP3a) ................................................. 3 1.2 Installing Security Patches ........................................................................................ 3 Task 2: Assessing Your Servers Security with MBSA ................................ 4 2.1 Introducing MBSA.................................................................................................... 4 2.2 Introducing MBSA Tests on SQL Server Installations ..................................................... 4 Task 3: Using Windows Authentication Mode........................................... 5 3.1 Introducing Windows Authentication Mode .................................................................. 5 3.2 Setting up Windows Authentication Mode Security........................................................ 5 3.3 Finding More Information on Windows Authentication Mode ........................................... 5 Task 4: Isolating Your Server and Backing It Up Regularly...................... 6 Task 5: Assigning a Strong sa Password. ................................................. 7 5.1 Introducing the sa Account ....................................................................................... 7 5.2 Assigning an sa Password ......................................................................................... 7 5.3 Finding More Information on System Administrator Login .............................................. 7 Task 6: Limiting the Privilege Level of SQL Server Services. .................... 8 6.1 Introducing Privilege ................................................................................................ 8 6.2 Introducing the Recommended Settings...................................................................... 8 6.3 Changing the Account Associated with an SQL Server Service ........................................ 9 Task Task Task Task 7: Disabling SQL Server Ports on Your Firewall. ............................ 10 8: Using the Most Secure File System. ........................................... 11 9: Deleting or Securing Old Setup Files.......................................... 12 10: Auditing Connections to SQL Server. ....................................... 13 10.1 Introducing SQL Server Event Information Logging................................................... 13 10.2 Enabling the Auditing of Failed Connections with the Enterprise Manager in SQL Server . 13 10.3 Finding More Information ...................................................................................... 13 Appendix A: Ongoing Security Maintenance........................................... 15 A1 A2 A3 A4 Introducing Security Maintenance ............................................................................. 15 Scanning and Updating SQL Server Security............................................................... 15 Introducing Additional Security Resources.................................................................. 15 Further Information ................................................................................................ 15 2 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Task 1: Installing the Most Recent Service Pack. This section covers the following topics: Downloading SQL Server 2000 Service Pack 3a (SP3a) Installing Security Patches. 1.1 Downloading SQL Server 2000 Service Pack 3a (SP3a) To improve the security of your server upgrade it to SQL Server 2000 Service Pack 3a (SP3a). To download SP3a, visit the SQL Server 2000 SP3a page at: http://www.microsoft.com/sql/downloads/2000/sp3.asp 1.2 Installing Security Patches Install all security patches as they are released. To sign up to be notified by e-mail of new security patches, please visit the Product Security Notification page at: http://www.microsoft.com/technet/security/bulletin/notify.mspx 3 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Task 2: Assessing Your Servers Security with MBSA This section covers the following topics: 2.1 Introducing MBSA. 2.2 Introducing MBSA Tests on SQL Server Installations. 2.1 Introducing MBSA Microsoft Baseline Security Analyzer (MBSA) is a tool that scans for common insecure configurations in several Microsoft products including SQL Server and Microsoft SQL Server 2000 Desktop Engine (MSDE 2000). It can be run locally or over a network. 2.2 Introducing MBSA Tests on SQL Server Installations MBSA tests SQL Server installations for problems such as: • • • • • • • • • • • • • Too many members of the sysadmin fixed server role. Granting of right to create CmdExec jobs to roles other than sysadmin. Blank or trivial passwords. Weak authentication mode. Excessive rights granted to the Administrators group. Incorrect access control lists (ACLs) on SQL Server data directories. Plaintext sa password in setup files. Excessive rights granted to the guest account. SQL Server running on a system that is also a domain controller. Improper configuration of the Everyone group, providing access to certain registry keys. Improper configuration of SQL Server service accounts. Missing service packs and security updates. Microsoft distributes MBSA as a free download. For complete documentation and the latest version of MBSA, visit the MBSA page at: http://www.microsoft.com/technet/security/tools/mbsahome.mspx 4 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Task 3: Using Windows Authentication Mode. This section covers the following topics: 3.1 Introducing Windows Authentication Mode. 3.2 Setting up Windows Authentication Mode Security. 3.3 Finding More Information on Windows Authentication Mode. 3.1 Introducing Windows Authentication Mode Whenever possible, you should require Windows Authentication Mode for connections to SQL Server. This will shield your SQL Server installation from most Internet-based attacks by restricting connections to Microsoft Windows user and domain user accounts. Your server will also benefit from Windows security enforcement mechanisms such as stronger authentication protocols and mandatory password complexity and expiration. Also, credentials delegation (the ability to bridge credentials across multiple servers) is only available in Windows Authentication Mode. On the client side, Windows Authentication Mode eliminates the need to store passwords, which is a major vulnerability in applications that use standard SQL Server logins. 3.2 Setting up Windows Authentication Mode Security To set up Windows Authentication Mode security with Enterprise Manager in SQL Server please follow the steps below: Steps 1. Expand a server group. 2. Right-click a server and then click Properties. 3. Click Windows only on the Security tab, under Authentication. 3.3 Finding More Information on Windows Authentication Mode For more information, please see the "Authentication Mode" topic in SQL Server Books Online or on MSDN at: http://msdn.microsoft.com/library/en-us/instsql/in_runsetup_6p9v.asp 5 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Task 4: Isolating Your Server and Backing It Up Regularly. Physical and logical isolation make up the foundation of SQL Server security. Machines hosting a database should be in a physically protected location, ideally a locked machine room with monitored flood detection and fire detection/suppression systems. Databases should be installed in the secure zone of your corporate intranet and never directly connected to the Internet. Back up all data regularly and store copies in a secure off-site location. 6 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Task 5: Assigning a Strong sa Password. This section covers the following topics: 5.1 Introducing the sa Account. 5.2 Assigning an sa Password. 5.3 Finding More Information on System Administrator Login 5.1 Introducing the sa Account The sa (System Administrator) account should always have a strong password, even on servers that are configured to require Windows Authentication. This will ensure that a blank or weak sa password is not exposed in the future if the server is reconfigured for Mixed Mode Authentication. 5.2 Assigning an sa Password To assign the sa password please follow the steps below: Steps 1. Expand a server group, and then expand a server. 2. Expand Security, and then click Logins. 3. Right-click SA in the details pane, and then click Properties. 4. Type the new password in the Password box,. 5.3 Finding More Information on System Administrator Login For more information, see the "System Administrator (sa) Login" topic in SQL Server Books Online or on MSDN at: http://msdn.microsoft.com/library/en-us/adminsql/ad_security_8soe.asp 7 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Task 6: Limiting the Privilege Level of SQL Server Services. This section covers the following topics: 6.1 Introducing Privileges. 6.2 Introducing the Recommended Settings 6.3 Changing the Account Associated with an SQL Server Service. 6.1 Introducing Privilege SQL Server 2000 and SQL Server Agent run as Windows services. Each service must be associated with a Windows account, from which it derives its security context. SQL Server allows users of the sa login, and in some cases other users, to access operating system features. These operating system calls are made with the security context of the account that owns the server process. If the server is cracked, these operating system calls may be used to extend the attack to any other resource to which the owning process (the SQL Server service account) has access. For this reason, it is important to grant only necessary privileges to SQL Server services. 6.2 Introducing the Recommended Settings The following settings are recommended: • SQL Server Engine/MSSQLServer If there are named instances, they will be named MSSQL$InstanceName. Run as a Windows domain user account with regular user privileges. Do not run as a local system, local administrator, or domain administrator accounts. • SQL Server Agent Service/SQLServerAgent Disable if not required in your environment; otherwise run as a Windows domain user account with regular user privileges. Do not run as local system, local administrator, or domain administrator accounts. Important: SQL Server Agent will need local Windows administrator privileges if one of the following is true: • SQL Server Agent connects to SQL Server using standard SQL Server Authentication (not recommended). • SQL Server Agent uses a multiserver administration master server (MSX) account that connects using standard SQL Server Authentication. • SQL Server Agent runs Microsoft ActiveX script or CmdExec jobs owned by users who are not members of the sysadmin fixed server role. 8 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net 6.3 Changing the Account Associated with an SQL Server Service If you need to change the account associated with a SQL Server service, use SQL Server Enterprise Manager. Enterprise Manager will set appropriate permissions on the files and registry keys used by SQL Server. Never use the Services applet of Microsoft Management Console (in Control Panel) to change these accounts, because this requires manual adjustment of dozens of registry and NTFS file system permissions and Microsoft Windows user rights. For more information, see the Microsoft Knowledge Base article Change the SQL Server Service Account Without Using SQL Enterprise Manager in SQL Server 2000 at: http://support.microsoft.com/default.aspx?scid=kb;en-us;283811&sd=tech Note: Changes to account information will take effect the next time the service is started. If you need to change the account associated with SQL Server and SQL Server Agent, you must apply the change to both services separately using Enterprise Manager. 9 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Task 7: Disabling SQL Server Ports on Your Firewall. Default installations of SQL Server monitor TCP port 1433 and UDP port 1434. Configure your firewall to filter out packets addressed to these ports. Additional ports associated with named instances should also be blocked at the firewall. 10 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Task 8: Using the Most Secure File System. NTFS is the preferred file system for installations of SQL Server. It is more stable and recoverable than FAT file systems, and enables security options such as file and directory ACLs and file encryption (EFS). During installation, SQL Server will set appropriate ACLs on registry keys and files if it detects NTFS. These permissions should not be changed. With EFS, database files are encrypted under the identity of the account running SQL Server. Only this account can decrypt the files. If you need to change the account that runs SQL Server, you should first decrypt the files under the old account, then re-encrypt them under the new account. 11 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Task 9: Deleting or Securing Old Setup Files. SQL Server setup files may contain plain-text or weakly encrypted credentials and other sensitive configuration information that has been logged during installation. The location of these log files varies depending on which version of SQL Server has been installed. In SQL Server 2000, the following files may be affected: 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 version 7.0 installations, the following files should be checked as well: setup.iss in the %Windir% folder, and sqlsp.log in the Windows Temp folder. Microsoft distributes a free utility, Killpwd, which will locate and remove these passwords from your system. To learn more about this free download, see the Microsoft Knowledge Base article Service Pack Installation May Save Standard Security Password in File at: http://support.microsoft.com/default.aspx?scid=kb;en-us;263968&sd=tech 12 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Task 10: Auditing Connections to SQL Server. This section covers the following topics: 10.1 Introducing SQL Server Event Information Logging. 10.2 Enabling the Auditing of Failed Connections with the Enterprise Manager in SQL Server. 10.3 Finding More Information. 10.1 Introducing SQL Server Event Information Logging SQL Server can log event information for review by the system administrator. At a minimum, you should log failed connection attempts to SQL Server and review the log regularly. When possible, save these logs to a different hard drive than the one on which data files are stored. 10.2 Enabling the Auditing of Failed Connections with the Enterprise Manager in SQL Server To enable auditing of failed connections with the Enterprise Manager in SQL Server, please follow the steps below: Steps 1. Expand a server group. 2. Right-click a server, and then click Properties. 3. Click Failure on the Security tab, under Audit Level. You must stop and restart the server for this setting to take effect. 10.3 Finding More Information For more information, see "SQL Server 2000 Auditing" on Microsoft TechNet: http://go.microsoft.com/fwlink/?linkid=14579 See the "Using Audit Logs" topic in SQL Server Books Online: http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp 13 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net See on MSDN at: http://msdn.microsoft.com/library/en-us/adminsql/ad_security_1yr7.asp Note: For information on protecting your server against the Slammer worm, visit the Finding and Fixing Slammer Vulnerabilities page at: http://www.microsoft.com/sql/techinfo/administration/2000/security/slammer.asp For detailed information about security features and best practices for SQL Server 2000 Service Pack 3 (SP3), read the article "Microsoft SQL Server 2000 SP3 Security Features and Best Practices" at: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec01.mspx 14 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net Appendix A: Ongoing Security Maintenance This section covers the following topics: A1 A2 A3 A4 Introducing Security Maintenance. Scanning and Updating SQL Server Security. Introducing Additional Security Resources. Further Information. A1 Introducing Security Maintenance Without ongoing maintenance, your system can become vulnerable to new forms of attacks. Further, the security of your system will degrade over time due to human error of administrators managing the system. A2 Scanning and Updating SQL Server Security Follow these recommended steps on a regular basis: 1. Use the Baseline Security Analyzer regularly to scan and evaluate the security of SQL Server. See: http://www.microsoft.com/technet/security/tools/mbsaqa.mspx 2. As new security fixes become available, it is important to apply these new fixes. A3 Introducing Additional Security Resources You can find additional information about keeping your SQL Server 2000 servers secure in the following sources: • • • • Microsoft SQL Server 2000 SP3 Security Features and Best Practices Security Tools Security Checklists SQL Server Security Center on TechNet SQL Server 2000 C2 Administrator's and User's Security Guide http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlc2.mspx A4 Further Information Please see our Net Report Knowledge Base at: http://www.netreport.fr/us/support/sup_knowledgebase.asp 15 Copyright © 2005 Net Report. All rights reserved. http://www.net-report.net
© Copyright 2024