It is time to gather remaining pieces of information dealing to the SQL
Server 2000 Security, concluding at the same time our series dedicated to this
subject. We will review them in the context of pre- and post- SQL Server 2000
installation tasks. Some of the topics discussed here are related to the issues
presented earlier, which should give you a complete picture of all issues that
you need to be aware of in order to securely configure and administer your
database environment.
Let’s start by looking at the recommendations regarding preparations for SQL
Server installation. They can be categorized according to the following list:
- physical isolation – Protect servers containing your
databases against environmental threats (such as flood or fire) and
unauthorized access, by placing them in an appropriately equipped and secured
facility, with monitored entry points and electronic surveillance system. - logical isolation – Do not install SQL Server on domain
controllers or Web servers and place operating system and SQL Server
installation and database files on separate partitions. - protection from non-secure networks – Locate your SQL
server computers on corporate intranet, separated from public networks by
firewalls. Typically, firewalls should filter out external packets addressed to
the TCP port 1433 (for data sessions) and UDP port 1434 (for pre-session
handshake), but in cases where named instances of SQL Server installation are
used, the number of vulnerable ports is larger. By default, such instances are
assigned listening ports dynamically at the time when SQL Server service is
started. You can, however, associate them with specific ports from the General
tab of SQL Server Network Utility – this is done by selecting the TCP/IP entry
from the list appearing in the “Enabled protocols” listbox and
displaying up its Properties dialog box (in order for this change to take
effect, SQL Server service will need to be restarted). Note that turning on the
“Hide server” option (from the TCP/IP Properties dialog box of SQL
Server network Utility), switches the listening port to TCP 2433.In addition, your firewalls need to be able to
support the authentication mechanism you selected. In the Active Directory
domain environment, you might need to ensure that ports used by Kerberos and
NTLM (if you have legacy clients) are allowed (assuming that your clients are
on the other side of firewall and they log on to the same or trusted domain).
Other considerations regarding firewall configuration apply in cases where
remote SQL Servers function as replication partners or participate in
distributed transactions. (For more information on this subject, refer to the
Microsoft Knowledge Base article 250367) - backup and restore procedures, with off-site long-term storage
– Consider protecting your backup data with a password. - virus protection via up-to-date antivirus software running on
SQL Server computers – Remember to exclude database and transaction log
files from scans. - recoverability and high availability – Develop and
implement disaster recovery solutions, such as clustered or standby servers
synchronized through log shipping or replication. - vulnerability detection and remediation mechanisms – This can
be accomplished using a wide variety of patch management solutions available on
the market. One of the more common one is Microsoft Baseline Security Analyzer
(MBSA), available free of charge for download from the
Microsoft Web site. MBSA can be used to determine the level of
vulnerability of the operating system and its core components, as well as SQL
Server 2000 and MSDE 2000 installations, including not only missing patches but
also common misconfiguration issues (e.g. excessive number of sysadmins,
non-default permissions to run CmdExec, presence of blank or easy to break
passwords used for SQL Server authentication, incorrect permissions set on SQL
Server-specific folders and registry entries, etc.). For more information about
MBSA, refer to our article on the ServerWatch Web site. - file system of partitions hosting SQL Server installation
– It is highly recommended that such partitions are formatted with NTFS, which
allows access control over program and database files as well as encrypting
them with Encrypted File System (on Windows 2000 or later operating systems).
As long as the target partition has NTFS at the time of installation of SQL
Server 2000, appropriate permissions are set automatically. - encryption of database files and data – The former can be
easily accomplished by taking advantage of Encrypted File System (mentioned
above). In order to protect your databases using NTFS-based encryption in
Windows 2000 or XP, you need to logon with the account used to run SQL Server
service, stop the SQL Server service, and encrypt the database and transaction
log files from Windows Explorer interface or from the Command Prompt with
CIPHER.EXE utility. By default, local Administrator will be able to decrypt
each of these files (in addition to SQL Server service account), since this
account is automatically designated as the Data Recovery agent. You can specify
other Data Recovery agents, if needed, by applying local or domain group policies.
When changing SQL Server service account on a system where database files are
encrypted, you need to decrypt them first (at this point, you need to be logged
on with either local Administrator or SQL Server service account). This is not
a requirement in the case of Windows 2003 Server, where you can grant ability
to decrypt the same files to multiple user accounts (independently of Data
Recovery agent functionality).If you decide to encrypt the content of specific
tables (rather than NTFS files hosting databases), one common way to accomplish
this is by employing the symmetric encryption algorithm. The symmetric
encryption algorithm uses the same key for encryption and decryption, then
encrypting the key itself (for example, programmatically, through the use of
Crypto API or Data Protection API), and storing it in a secure location (such
as a file residing on an NTFS partition or a Windows Registry key accessible to
privileged accounts only) . There are also third party utilities, such as, for
example, DbEncrypt from Application Security
Inc., which offers this functionality in the ready-to-use form.Be aware that SQL Server built-in encryption used
for securing content of stored procedures (in the syscomments table) can be
relatively easily broken. There are a number of tools on the Internet (such as sql2k_spcrypto
script at http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26
and dSQLSVRD from www.geocities.com/d0mn4r/dSQLSRVD.html)
that provide this functionality. - data protection during communication – This can be
accomplished with IPSec (which encrypts and ensures integrity on the OSI
Network layer) or with Secure Sockets Layer (operating on the OSI Application
layer). While both introduce processing overhead, their functionality is
independent of SQL Server and, therefore, both can be configured in such way
that they do not impact SQL Server operations negatively. In the first case,
you can offload encryption and decryption tasks to hardware security modules,
(you can find out more about them in one of our ServerWatch
articles). With both IPSec and SSL, you can introduce intermediary tier
(typical solution for Web-based applications which access databases on the
back-end), which provides security and integrity for direct communication with
clients. If you intend to secure communication implementing SSL directly on SQL
Server, refer to our
first article of this series for details.
Following SQL Server 2000 installation, you should consider the following
recommendations in order to enhance its security further:
- avoid having Guest account in user databases – While this
is the default setting for new databases (since the model database does not
include it), you might encounter some existing ones which contain it. Remember
that Guest account is required in master and tempdb databases. - prevent direct access to system tables – you can control
this setting with the sp_configure stored procedure (using “allow
updates” option) or with “Allow modifications to be made directly to
the system catalogs” option on the Server Settings tab of SQL Server
Properties dialog box in the SQL Server Enterprise Manager. - remove sample databases, which are installed by default (Northwind
and pubs) – since they sometimes serve as a launching area for exploits. - review and delete (or otherwise secure) setup files (such as
SQLSTP.LOG, SQLSP.LOG, or SETUP.ISS) residing in the Program FilesMicrosoft
SQL ServerMSSQLInstall folder (or Program FilesMicrosoft SQL
ServerMSSQL$Install folder for named instances) – Such files might reveal
sensitive information about the server configuration to potential attackers. - evaluate whether you require SQL Mail functionality – If not,
disable it by dropping SQL Mail related extended stored procedures, such as xp_startmail,
xp_readmail, xp_sendmail, xp_deletemail, and xp_stopmail. - Disable the ability to login from remote servers, as long as
you do not need to execute remote stored procedures. – This can be
accomplished by running sp_configure with the “remote access” option
or by the “Allow other SQL Servers to connect remotely to this SQL Server
using RPC” option on the Connections tab of the SQL Server Properties
dialog box in the SQL Server Enterprise Manager.
This article concludes our series dedicated to SQL Server 2000 Security. We
are hoping that information we have provided will help you protect your
database environment and practice safe computing.
