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