Secure Installation of Microsoft SQL Server 2000

The SQL server is a very popular Relational Database Management System, i.e. RDBMS. It owes its popularity not only to the producer’s strong marketing actions but it is also a very efficient, stable, scalable and a quite secure database platform. Unfortunately, the server manufacturers prefer concentrating on efficiency rather than paying attention to security, and if they finally do it, it will be too late. The Microsoft SQL server’s most serious opponent, the Oracle9i server was introduced to the market last year with the nickname of ‘the unbreakable’, which means ‘the unhackable’. At that time its safeguards were no more than a nice catchword [1][2], while the security problems are still being repaired [3]. The Linux PostgreSQL server, popular among the users, has recently had some serious bugs removed [4][5][6]. There were also problems with MySQL [7] and the Sybase [8] – Microsoft SQL server is no exception here [10]. Can using a database server be secure in connection with these facts? Yes, if the server is correctly safeguarded.

Introduction

I shall discuss the patches that require the correct level of security are not available for the Evaluation version of the software. Moreover, the license limitations make this version unusable in a production environment.

Other limitations are produced by the server’s OS – the SQL Server 2000 service can be installed on a Windows 2000 Server only (also Advanced and Datacenter) or Windows NT 4.0 Server (or Enterprise Edition). This latter platform is no longer officially supported by Microsoft [10] therefore this article will focus on the installation of a SQL Server 2000 on a Windows 2000 Server platform. Because of network security reasons, this server cannot be a domain server. It should belong to the entire domain as a member server to enable users to use SQL Server services with no additional authentication required (which absolutely does not mean worsening the security but will enhance it due to the lack of a little secure SQL authentication). Still for security reasons, the Service Pack 3 file for Windows 2000 and the latest patches [11] must be installed. Further, you should strenghten the server’s security by setting the GPO and/or local Security Policy options [12]. For appropriate settings to be applied to a non-domain controller server, search for the “Security Templates” application and go to the “hisecws” filename [13]. Last but not least, you should ensure that the sysadmin account password is protected by a hard-to-crack password. No matter how carefully you set up security, if an attacker can guess the system administrator password, the unique password that is not protected against brute force attacks.

Getting installed

The SQL Server installation does not commence in a typical way – firstly you have to create an account for the service. If replication is not required nor are the other mechanisms using the SQL-initiated connections to other computers, this account should be defined in the Local Account Base (i.e. SAM). Otherwise you should define it in a domain. It should belong to the Users group (in case of a domain account – to the Domain Users group). If you’re not going to use the SQL Server Agent, you could even resign from this level of permissions and place the accounts in the Guests (or Domain Guests) group. Let’s choose ‘sqlsvc’ as the account name in the following example installation process. You don’t grant any extra permission at the moment – the right of logging in as a service will be granted by the installation program. Remember to choose a long and complicated password because your data’s security depends on it.

Having prepared the account, run the installation – after inserting the installation CD-ROM a welcome screen should appear. Choose the SQL server components option and afterwards, the Install Database Driver. If your computer does not run CD-ROMs automatically (it is a recommended security action), you’ll have to run the setup.bat file in the CD-ROM’s root directory manually. The SQL server service’s installation wizard will appear. On the first screen, click the Next button, on the following one – the target location of the installation – a Local Computer. The very next screen presents you with the available installation options: creating a new installation, modifying (or deleting) the current installation, and finally the advanced options. Choose the default, top option. Then it’s time for filling in your personal details. Clicking the Next button will lead you to the license arrangement screen. Having confirmed it, choose the installation’s elements – the middle option, Server and Client Tools, that is provided to install the server’s services. Subsequently, choose the SQL server installation. You may run up to 16 instances, i.e. independently working SQL server services on one computer. You’ll usually be using only one instance at a time – otherwise you’d have to face the costs of extra licenses and the fact of efficiency limitations. That’s why you should choose the option of the default installation by leaving the Default field marked as it was from the beginning, and pass on to the next screen of the installation. This is where you choose the particular elements of the installation and its location on the hard drive. Click the Typical installation option but don’t skip to the next screen. Consider what kind of drive you should be storing the databases (the Data Files option) – storing them on a dedicated drive (or, which would be even better, in a storage array system equipped with SCSI hard drives) will allow achieving optimal efficiency and slightly enhancing the data security.

Fig. 1: The SQL Server installation options

If you have chosen a dedicated drive, remember setting up its safeguards (of course it has to be a NTFS partition). The right permissions for a data-storage drive are:

Administrators: Full Control
SYSTEM: Full Control
sqlsvc: Read and List Folder Contents

A reminder: the SQL data must not be compressed by the NTFS file system because this may cause a serious drop in efficiency or even faults when performing the queries. If you have chosen a compressed partition, you should halt the SQL service right after the installation and decompress the data-containing directory. Leave the default software file’s location, i.e. C:\Program Files\Microsoft SQL Server as it is. This directory will occupy approximately 120 megabytes of the C: hard drive space. After clicking Next you’ll have to provide the program with the data of the earlier setup account. The SQL Server installs two services (the MSSQLServer and the SQLServerAgent), so there’s a possibility of defining separate accounts for each of them, but you won’t need it.

Fig. 2: Setting up the service’s account

The following screen allows you to choose the way of authenticating the SQL service’s users; choose the Windows Authentication Mode. Running the SQL in this mode requires that the users firstly have to log onto the operation system. This is a very important option allowing you to safeguard the SQL service with the use of the Windows authentication mechanisms

Fig. 3: The setup of the authentication mode

Subsequently, clicking the Next button will move you to a screen where you’ll be asked to fill in the information on the kind and number of purchased licenses. Afterwards the SQL Server installation program will commence the installation procedure. When it is finished, a ‘setup complete’ caption will appear on the screen. Installing the Service Pack 2 for the SQL Server is your next stage. Download the 49-megabyte SQL2KSP2.exe file from Microsoft’s website and run it on your server. An important reminder: the Service Pack 2 may only be installed on the full version of the SQL Server; the evaluation version cannot be ‘repaired’ nor secured with newer patches because this requires the SP2 to be installed. Having run the SQL2KSP2.exe file, you’ll be asked about the installation folder. Contrary to what your intuition tells you, this question is about the directory where the temporary installation files are to be decompressed, not about the SQL Server service’s directory. Choose any temporary directory, such as the C:\winnt\temp\sql2ksp2 or the default C:\sql2ksp2. When the decompression is finished, you’ll get informed that ‘The package has been delivered successfully’. At this moment you may start the proper installation of the Service Pack 2 for the SQL Server 2000. Run the setup.bat file which has been copied to the earlier specified directory. Then you’ll pass two screens of the installation wizard. In the ‘Connect to Server’ window, mark the default option of choosing the authentication on the SQL server – The Windows account information ….. – which will make you use the Windows Authentication Mode. At the moment when the Service Pack 2 for SQL Server 2000 is finished, this information will appear on the screen: ‘You should now backup your master and msdb databases since this installation has updated their content’ – this advice shouldn’t be ignored. However, if you haven’t installed any databases nor changed the manufacturer’s setup of the server, you don’t have to pay attention to it. A ‘setup complete’ window will follow the confirmation.

The Microsoft Data Access Components software package version 2.6 is one of the components of the SQL Server 2000. The Service Pack 2 updates it to the 2.6 SP2 version [12]. You should consider updating it to version 2.7 because of the MS02-065 fault.[13]. To do this you have to connect to Microsoft’s website [14] and choose the MDAC 2.7 Refresh for x86 – English option from the list and then press the Go to Download button. Having downloaded the patch, run the MDAC_TYP.EXE file. After giving your consent to the license agreement, click Next so that the program will check if installing the MDAC 2.7 is possible. What you’ll see next will be an ‘Installing the Software’ screen and quite a funny message, ‘Click Finish to begin installation’. The MDAC components will be copied to the systems after you click the Finish button. On the following ‘Setup is complete’ screen click Close. You may check the currently installed MDAC version in the HKLM\SOFTWARE\Microsoft\DataAccess registry key.

Fig. 4: Upgrading to Version 2.7

The last patch to be installed on your computer is the MS02-061 patch [16]. This is to update the SQL Server service files by removing major security holes that were found in Service 2. Unfortunately, in contrast to the previous patches, those for the SQL Server are not installed by default with the system and you must do it manually. Go to the Microsoft Security Bulletin [16] and navigate to the link associated with the Q316333 issue [17]. It contains information on this patch and all previous SQL Server security patches. The SQL Server patches are cumulative ones, i.e. the most recent patch for any component will contain all the fixes for that component of the product. If so, all you need is to install one – the latest patch. Go to the information about the patch: ‘October 16, 2002 Release’ and then download the 8.00.0686_enu.exe. This is a 10MB self-extracting ZIP file(more specifically, it is a WinZip Self-Extractor). Once the file is started, you will be prompted to indicate the destination directory you wish to have the patch files copied – use a temporary directory, for example C:\WINNT\TEMP\MS02-061 (a relevant sub-directory will be created automatically).

Fig. 5: Unzipping the MS02-061 patch

Once you have extracted the files, you will see the message about the number of copied files. Quit WinZip. Go to the previously indicated directory and navigate to find the”x86″ sub-directory. It contains patch files to be copied manually to appropriate directories, namely SQL Server and README.TXT files that contains the installation instructions.

Fig. 6: The MS02-061 patch files after extraction

These instructions describe the patch files and operations that are necessary for the installation:

1. Stopping the SQL Server and SQL Server Agent services. To do this, you can either use the ‘Services’ application belonging to the administrative tools of the computer, or make two operations in the command line:

net stop sqlserveragent

net stop mssqlserver

2. Making backup copies of these files that will be patch-updated (the most convenient method is to change filenames). The files are to be left in their original locations:

The SQL Server service files from the C:\Program Files\Microsoft SQL Server\MSSQL\Binn directory (if you have chosen a default location of the SQL Server service files and installed a default instance – otherwise you will have to search for an appropriate directory for each installed instance):

SQLSERVR.EXE, ODSOLE70.DLL, XPQUEUE.DLL, XPREPL.DLL, XPWEB70.DLL, XPLOG70.DLL, SSNETLIB.DLL, SQLCMDSS.DLL, SQLAGENT.DLL, SQLAGENT.EXE, XPSTAR.DLL

Service debug symbols, namely C:\Program Files\Microsoft SQL Server\MSSQL\Binn\Exe\SQLSERVR.PDB
Management components and auxiliary services. Copy the following files from the C:\Program Files\Microsoft SQL Server\Microsoft SQL Server\80\COM directory:

IMPPROV.DLL, RDISTCOM.DLL, REPLMERG.EXE, RINITCOM.DLL, LOGREAD.EXE, QRDRSVC.EXE

SQL Server service installation scripts, i.e. the files from the C:\Program Files\Microsoft SQL Server\MSSQL\install directory:

INSTDIST.SQL, REPLCOM.SQL, REPLMERG.SQL, REPLTRAN.SQL, REPLSYS.SQL

The language version resource files – for the English language version of the SQL Server take the following files from the C:\Program Files\Microsoft SQL Server\MSSQL\Binn\Resources\1033 directory:

SQLCMDSS.RLL, SQLAGENT.RLL

The DISTMDL.* files from the directory containing the SQL database data – in this case it will be F:\MSSQL\Data

3. Copying updated file versions from the directory, to which the MS02-061 patch has been downloaded

Go to the C:\Program Files\Microsoft SQL Server\MSSQL\Binn and copy the files:

SQLSERVR.EXE, ODSOLE70.DLL, XPQUEUE.DLL, XPREPL.DLL, XPWEB70.DLL, XPLOG70.DLL, SSNETLIB.DLL, SQLCMDSS.DLL, SQLAGENT.DLL, SQLAGENT.EXE, XPSTAR.DLL

Go to the C:\Program Files\Microsoft SQL Server\MSSQL\Binn\Exe and copy the SQLSERVR.PDB file from the exe. subdirectory, located within the directory containing the patch
Go to the C:\Program Files\Microsoft SQL Server\Microsoft SQL Server\80\COM and copy the files

IMPPROV.DLL, RDISTCOM.DLL, REPLMERG.EXE, RINITCOM.DLL, LOGREAD.EXE, QRDRSVC.EXE

Go to the C:\Program Files\Microsoft SQL Server\MSSQL\install and copy the files

INSTDIST.SQL, REPLCOM.SQL, REPLMERG.SQL, REPLTRAN.SQL, REPLSYS.SQL

Go to the C:\Program Files\Microsoft SQL Server\MSSQL\Binn\Resources\1033 and copy the files

SQLCMDSS.RLL, SQLAGENT.RLL

Go to the directory containing the databases (in our example F:\MSSQL\Data) and copy

DISTMDL.LDF, DISTMDL.MDF

4. Start the SQL Server and SQL Server Agent. You can use either the “Services” application or execute the commands:

net start mssqlserver

net start sqlserveragent

5. Run the command line (that is the CMD.EXE program – if you have not started it already) and go to the directory containing the unzipped patch files (use the “cd C:\WINNT\TEMP\MS02-061\x86” command) – here there are located additional files that are necessary to complete the installation of the patch.

6. Run the SQL Qfe356326.sql and SecurityHotfix.sql scripts one after another. To do this, use the osql.exe utility (its starting options are displayed after typing the osql.exe -? command). Run relevant scripts using the following commands (observe the sequence!):

osql -E -n -iQfe356326.sql

osql -E -n -iSecurityHotfix.sql

Fig. 7: Installing the SQL scripts to apply the patchset …

7. Run the servpriv.exe utility. It is designed to prevent the SQL Server accessing the service configuration data contained in the HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer registry key. Any excessive default permissions will allow to escalate the user account rights related to the service (in our example this is “sqlsvc”) to the level of LocalSystem privilege. The script call argument is the instance name – in our example the default instance name is MSSQLServer. To do this, use the command :

servpriv MSSQLServer

Fig. 8: … and establish a limited range of service privileges within the registry

Now you have completed the installation of the SQL Server and all necessary and current patches. In order to complete the operation, you must modify the service settings for better security. First, use the ‘Enterprise Manager’ application. This is the MMC (Microsoft Management Console)application, the shortcut to it is included in the ‘Microsoft SQL Server’ program suite. Run this application and wait until it contacts a locally installed SQL server. After a certain time you will see the ‘SQL Server Enterprise Manager’ window displayed. Expand the management tree located in the left and navigate to reach the ‘(local) Windows NT’ server name (that stands for: local installation of the SQL based on Windows authentication). Click your right mouse button on (local), and from the expanded menu, select Properties. The application will establish communication with the SQL server (after the connection is established, the arrow symbol next to the name (local) will change its color) and then you will see the ‘SQL Server Properties (Configure) – (local) dialog box’. Go to the Security tab and select the All database login option. Then, click on the OK button and restart the service to execute operations provided in the server popup menu: Stop and afterwards Start. From now on, the service will login every connection.

Fig. 9: Logging connections to the SQL Server

The next patch is associated with the privilege restrictions within the directory containing the service files that is C:\Program Files\Microsoft SQL Server\MSSQL. By default, the SQL Server users have full access to this directory and its sub-directories and files. This is unnecessary – restrict the ‘sqlsvc’ related user accounts to the Read & Execute permissions, List Folder Content and Read. You can also check the permissions related to the directory that contains the SQL service data – this is the \MSSQL sub-directory belonging to the directory (or disk) selected during the installation phase – in our example this is F:\MSSQL. The default permissions mean:

Administrators : Full Control
sqlsvc : Full Control

A user having the right to read the database files (in our example, the files contained in the F:\MSSQL\Data directory) is allowed to read the data included in this directory irrespective of the privileges defined in the SQL Server. Therefore, the best practice is to provide a minimum access – preferably by leaving the default settings.

The last operation involves the change in the most privileged SQL Server access account password – the SA privilege. Although the ‘Windows Authentication Mode’ efficiently only allows a user to login/connect to SQL Server with Windows authentication, but an unintentional switching to the ‘Mixed Mode’ (named ‘SQL Server and Windows’ in the settings available in the ‘Enterprise Manager’) may cause that the ‘SA’ account will be accessible with no password required. In order to modify the passwords, use the osql.exe utility and then call the sp_password procedure to impose any additional limits on the supplied password that you desire. On starting the osql.exe, type the -E argument (the Windows authentication that is required for connectivity with the local SQL server). Setting the sp_password procedure requires the following parameters:

account name @loginame=’sa’ and
new password @new=’anynewpassword’ (of course, type your own passwords, preferably more sophisticated).

The SQL procedure parameter names are always prefixed with ‘@’ followed by the equals sign and the value (names are to be put in apostrophes, parameters are to be separated with commas). After entering the procedure, run it using the ‘go’ command and then exit the osql program using the ‘quit’ command. Fig. 10 illustrates the password changing procedure.