Configuration

After a secure installation of SQL Server, the next step is to lock down the server, ensuring that any unnecessary features and services are removed.

Step 5: Configure Network Libraries

The network libraries used by SQL Server should be restricted to the minimum required by the infrastructure. Supporting unnecessary netlibs is similar to running redundant services on the server; obscure network libraries could contain vulnerabilities that put the server at risk. TCP/IP has now become the most commonly used netlib for SQL Server, and this coupled with SSL support will give a secure foundation for accessing SQL Server.

Step 6: Configure Auditing and Alerting

Well-configured auditing allows administrators to continually monitor activity on their server and minimize the damage caused by an intrusion by early detection.

SQL Server does not provide the ability to lock out accounts after multiple failed logins, used as a defense against brute-force attacks. For this reason auditing of failed logons is strongly recommended; this can be set using the Enterprise Manager or by setting the following registry value to 2 (setting it to 3 will record successful logins as well):

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\AuditLevel 

Step 7: Lock Down Privileges

By default the SQL Server install grants the guest account public role membership in all databases except for the model database. It's recommended that you disable the guest account within Windows and revoke its access to all databases except for master and tempdb, which is required in order for SQL Server to operate correctly. Database access can be removed using

 use msdb; exec sp_revokedbaccess guest; 

Public should not have access to the web task tables because these could allow table data to be made available to web clients . Privileges should be revoked :

 revoke update on mswebtasks to public revoke insert on mswebtasks to public 

DTS (Data Transformation Services) packages are sets of COM interfaces that can be used to perform many administrative tasks on a SQL Server using T-SQL, Windows scripts, and executable tools. By default, Enterprise Manager users can access the list of available DTS packages. The procedure sp_enum_dtspackages will display package names and ID numbers that can be fed into sp_get_dtspackage, which will return the package data. An attacker could then put the package into his local installation of SQL Server and view the package details, which often contain credentials for other servers. Privileges should be removed on these procedures:

 revoke execute on sp_enum_dtspackages to public revoke execute on sp_get_dtspackage to public 

The procedure sp_get_SQLAgent_properties displays the obfuscated password used by the SQL Server Agent service to connect to the database server. The obfuscation can easily be reversed using a freely available tool ( http://jimmers.narod.ru/agent_pwd.c ). Permissions should be tightened on this procedure:

 revoke execute on sp_get_SQLAgent_properties to public 

The Microsoft Data Transformation Services (DTS) are used to manipulate data from multiple sources such as OLE DB, ODBC, or text files. Connection passwords are saved in clear text in the table RTblDBMProps in the column Col11120, so they can be retrieved by anyone with select privileges. Lock down permissions on this table using

 revoke select on RTblDBMProps to public revoke update on RTblDBMProps to public revoke insert on RTblDBMProps to public revoke delete on RTblDBMProps to public 

Step 8: Remove Unnecessary Features and Services

SQL Server's remote access feature allows other SQL Servers on the network to connect and execute stored procedures remotely. If this is not needed the ability should be disabled using the Enterprise Manager, or by using

 execute sp_configure 'remote access', '0' go reconfigure with override go 

The configuration option "allow updates" defines whether database users can directly update system tables. While this may be a useful temporary ability for advanced administrators, this should be disabled for normal operation:

 execute sp_configure 'allow updates', '0' go reconfigure with override go 

The SQL Server Monitor, which listens on UDP port 1434 and provides information about the instances present on the server, should not be accessible to clients and SQL Server will run happily with it blocked. A firewall or IPSec filter should block external traffic to both TCP port 1433 and UDP port 1434.

Heterogeneous or ad-hoc queries allow database users to use local data providers to execute queries on remote servers. This feature could be abused to brute-force either remote or local access credentials, and should be disabled on all providers that it is not needed on:

 exec xp_regwrite N'HKEY_LOCAL_MACHINE',  N'SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB',  N'DisallowAdhocAccess', N'REG_DWORD', 1 

This example disables ad-hoc queries using the SQLOLEDB provider. This registry change must be made for all providers on the server, which are typically ADSDSOObject, DB2OLEDB, Microsoft.Jet.OLEDB.4.0, MSDAORA, MSDASQL, MSIDXS, MSQLImpProv, and MSSEARCHSQL.

If not required, the SQL Server Agent, Microsoft Distributed Transaction Coordinator (MSDTC), and MSSearch services should be disabled. The services can be turned off using the Enterprise Manager or by setting their Startup Type to Disabled in the Windows Services management tool. Setting registry values can also disable the services:

 exec sp_set_sqlagent_properties @auto_start=0 exec xp_regwrite N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\ Services\MSDTC', N'Start', N'REG_DWORD', 3 exec xp_regwrite N'HKEY_LOCAL_MACHINE', N'SYSTEM\CurrentControlSet\ Services\MSSEARCH', N'Start', N'REG_DWORD', 3 

After making these changes the services should be stopped manually or the server should be restarted.

Step 9: Remove Stored Procedures

Many stored procedures and extended stored procedures installed with SQL Server will be unnecessary for most configurations. Appendix B contains a full list of potentially dangerous stored procedures. The particularly unsafe ones are:

 xp_cmdshell xp_displayparamstmt xp_execresultset xp_instance_regaddmultistring xp_instance_regdeletekey xp_instance_regdeletevalue xp_instance_regenumvalues xp_instance_regread xp_instance_regremovemultistring xp_instance_regwrite xp_printstatements xp_regaddmultistring xp_regdeletekey xp_regdeletevalue xp_regenumvalues xp_regread xp_regremovemultistring xp_regwrite sp_OACreate sp_OADestroy sp_OAGetErrorInfo sp_OAGetProperty sp_OAMethod sp_OASetProperty sp_OAStop 

Stored procedures that are not required should be removed:

 exec sp_dropextendedproc 'xp_cmdshell' 

Because the SQL Server Enterprise Manager relies on some stored procedures being present, it may be preferable to tighten privileges on stored procedures instead. Privileges should be revoked from all users of the database except the sysadmin role. The public role in particular should not have permissions:

 revoke execute on xp_instance_regread to public 

Low-privileged users should not be able to manage SQL Server Agent jobs. Privileges should be revoked on sp_add_job, sp_add_jobstep, sp_add_jobserver, and sp_start_job.

Step 10: Apply Security Patches

The final, and perhaps the most important, step is to ensure that the latest service packs and patches are applied. The T-SQL command @@version will display the SQL Server's current version; SQLSecurity provides a list of SQL Server versions and service pack and patch levels ( http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=37 ).

Microsoft provides HFNetChk ( http://hfnetchk.shavlik.com ), a free tool to determine a Windows computer's patch level. This can be used both locally and remotely. The Windows Update feature ( http://windowsupdate.microsoft.com ) can also be used to determine missing SQL Server patches. Commercial patch management solutions include UpdateExpert ( http://www.updateexpert.com ) and Patchlink Update ( http://www.patchlink.com/products_services/patchlink_update.html ). Remember that security patches must be applied to every instance of SQL Server on a machine.

A final audit of the server using an automated vulnerability scanner will help to ensure that best practices have been followed. The Microsoft Baseline Security Analyzer ( http://www.microsoft.com/technet/security/tools/mbsahome.mspx ) can be used to pick up common misconfigurations locally or over a network.