Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 25, 2004

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

By Marcin Policht

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 Files\Microsoft SQL Server\MSSQL\Install folder (or Program Files\Microsoft SQL Server\MSSQL$\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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM