Chapter 23: Securing SQL Server

Installation

A planned secure installation of SQL Server is the vital first step in building a protected database server. These initial steps define much of the server's underlying security.

Step 1: Authentication

Setting the server on install to use integrated Windows authentication instead of native SQL Server authentication simplifies security administration and reduces the risks of attacks from password sniffing. The authentication mode can be set during the server's installation as shown in Figure 23-1.

Figure 23-1: Setting the authentication mode.

The authentication mode can also be changed later using the SQL Server Enterprise Manager, under the Security tab of the SQL Server's properties, which is shown in Figure 23-2.

Figure 23-2: Changing the authentication mode.

The authentication mode can also be changed using the registry keys:

 HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSQLServer\LoginMode HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\[Instance]\LoginMode 

A value of 1 signifies Windows authentication; 2 denotes both native and Windows authentication.

In SQL Server 6.5, the Enterprise Manager stored the sa password in the registry in plaintext. Although SQL Server 7 and 2000 do obfuscate this information, it is the same method used to hide ODBC credentials and is easily reversible. The registry key under 6.5 is

 HKEY_CURRENT_USER\Software\Microsoft\MSSQLServer\SQLEW\RegisteredServer\SQL6.5 

In SQL Server 7 and 2000 it is

 HKEY_CURRENT_USER\Software\Microsoft\MSSQLServer\SQLEW\Registered Servers X\SQL Server Group 

Using only Windows authentication prevents this line of attack.

Step 2: Password Strength

Even when using Windows authentication a password must be set for the sa account. This is because the authentication method can be easily changed at any time using the Enterprise Manager or by setting a registry value. You should therefore ensure that a strong sa password is set on install. The sa password can be set using

 exec master..sp_password NULL, '[new password]', 'sa' 

On SQL Server 6.5 the password of the "probe" account must also be set to a sufficiently complex value.

Step 3: Operating System Lockdown

SQL Server can be installed on a number of different Windows filesystem types (NTFS, FAT, FAT32). It is important that NTFS is used for both the SQL Server system and data files because this allows individual access permissions to be set on files and directories (access control lists). NTFS is also a requirement for using Microsoft's EFS (Encrypting File System), which protects the confidentiality of data files ( http://www.msdn.microsoft.com/library/en-us/dnsecure/html/WinNETSrvr-EncryptedFileSystem.asp ).

During the installation a Windows account must be selected to run the SQL Server service. The principle of least privilege should be applied here, because this will restrict an attacker's abilities if the server is compromised. Servers not using replication should run under a local account; those that connect out to other servers should run under a domain account. The SQL Server OS account is stored in the registry:

 HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLSSERVER\ObjectName 

When using the Enterprise Manager to set the SQL Server account, several privileges are granted to enable non-system administrators to execute operating system commands using SQL Server (via CmdExec, ActiveScripting, or xp_cmdshell). It is recommended that these be revoked unless the functionality is absolutely necessary. Using the Local Security Policy tool that is found in the Windows Administrative Tools folder, remove the SQL Server account from both the Act As Part of the Operating System and Replace a Process Level Token policies, as shown in Figure 23-3.

Figure 23-3: Removing the SQL Server account.

Step 4: Post-Installation Lockdown

During an installation of SQL Server 7 using native authentication, plain-text passwords are saved to two files (%temp%\sqlsp.log and %windir%\setup.iss). This vulnerability is described in the Microsoft Security Bulletin MS00-035 ( http://www.microsoft.com/technet/security/bulletin/MS00-035.asp ). On SQL Server 2000 the password is saved to the files_sqlstp.log, sqlsp.log, and setup.iss in the install directory under Mssql\Install. The Microsoft tool killpwd.exe ( http://support.microsoft.com/default.aspx?scid=KB;en-us;263968& ) should be used to remove saved passwords from a SQL Server installation.

SQL Server also installs two sample databases ” Northwind and pubs ” and grants generous access permissions to the public role. These could be used by an attacker to store data for later retrieval. Sample databases should be removed using

 use master drop database northwind drop database pubs