SQL Server 2000 Security – Part 14 – Conclusion – Pre- and Post- Installation Recommendations

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.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles