Checklist for hardening your database server

from the Artful SQL Server & Access Tips List

A checklist for what needs to be done to protect your database before it becomes available to the world of its users:

1. Physically secure the server machine.

2. Apply all current service packs and Hotfixes to both Windows O/S and SQL server. Precisely how you do this depends upon your organization’s dicta, and whether you have automated this step using Windows Update, or whether you use SMS to image the disk, and so on.

3. Ensure that all required SQL patches follow your firm’s installation standards and procedures. Ensure that the SQL servers have HFNetChkPro agents installed.

4. Ensure that all SQL data files and system files are installed on an NTFS partition, and that appropriate permissions are defined for the files.

5. Ensure that the SQL Server service has a low-privilege account rather then LocalSystem or Administrator.

6. Delete all setup files including sql-stp.log, sqlsp.log and setup.iss in the MSSQL/Install (or MSSQL$\Install). Use Microsoft killpwd to locate and remove these passwords. Nobody gets in without expressly-stated permissions!

7. Secure the sa account with a new strong password (a strong password that would be very difficult to break would be comprised of two concatenated unique identifiers).

8. Check for any account/UID with a null password and remove all such occurrences: :

SELECT name, password FROM syslogins WHERE password IS NULL
9. Remove the 'guest' user from all databases, especially master and tempdb.Create a scheduled procedure that inspects role and group memberships periodically, and ensure that somebody reviews its results.

10. Ensure only Windows authentication is used, not mixed authentication.

11. Remove all network libraries expect TCP/IP. Enforce Netlib Support for Default SQL Server Instances with TCP only. Since this is a hardened server, all netlibs can be removed until external connectivity requirements are identified. Connections to the local server are still possible using the Shared Memory netlib, which is always in effect by specifiying ‘(local)’ or (a period) as the server name.Change the default SQL Port number from the default TCP/1433. Examine your front-end application(s) to ensure that their references to the port number are not hard-coded .

12. Rename the Administrator account ‘toastadmin’ or something similar. Ensure that access to extended stored procedures is restricted to Administrator accounts only.

13. Severely limit the stored procedures available to PUBLIC. The ideal number is zero. This may not always be possible, but the closer to zero the number, the better off you are.

14. Disable SQL mail. Depending upon the requirements of your application, this may not be possible; but if it is possible, then obey the rule. If it is not possible, think about the implications very carefully, and plan accordingly.

15. Remove MSSEARCH from the system.

16. On first SQL bootstrap and periodically thereafter, check for Trojans i.e. no weird calls in master..sp_helpstartup and master..Sp_password through a Group Policy Object.

17. Enable SQL Auditing for Successful and Failed Logins. This is critical: you need to know about every successful and failed login. Someone must be tasked with this reviewing process.

18. Remove the Pubs and Northwind sample databases.

19. Ensure that the permissions on jobs prevent low-privilege users from submitting or managing jobs via SQL Agent service. Assuming that someone did penetrate the layers of insulation described above, failure to perform this step could be your Achilles’ hell (purposely misspelled).Revoke guest access to msdb. This will keep non-system administrators from accessing the database without explicit permissions.

20. Turn off 'Allow Remote Access' to keep other SQL Servers from connecting to this server via RPC. This may not always be possible, since some configurations use a collection of servers, with different functionality residing upon each, and assume that these servers can talk to each other. In that case, ensure that no other server than those named can access any of the servers in the family.Ensure that access to system tables is disabled. This applies across the board, except for the developers of the database, who obviously need table-access. No other users should be permitted to visit a table directly. That is the purpose of Views. Use them.

21. Disable Third-Party Tool Bands and Browser Helper Objects.

22. Monitor failed logins daily. An easy way to accomplish this is to create a scheduled task that runs: findstr /C:"Login Failed" \your_sql_path\log\*.*'.

23. Use Integrated Security when accessing Enterprise Manager or Management Studio.

24. Store all data files generated by DTS or BCP in a secure folder/share.

25. Tighten permissions on the DTS package or SSSI connection table so malicious users cannot affect DTS packages. Specifically, remove permissions from the following procedures:

  sp_add_dtspackage     When permissions are removed, standard 
                        users cannot add DTS packages.
  sp_enum_dtspackages   When permissions are removed, 
                        standard users cannot even see existing DTS packages.
  sp_get_dtspackage     When permissions are removed, standard users cannot 
                        open or execute existing DTS packages.
26. Ensure that the PUBLIC role is denied SP/XP EXECUTE permissions. Failure to do this effectively enables any logged-in users to execute any SP/XSPs). Instead, create a separate role such as MyCompany Trusted Users. Drop roles of OS-level procedures, or change them to the role created.
  sp_OACreate         sp_OADestroy        sp_OAGetErrorInfo         sp_OAMethod
  sp_OASetProperty    sp_OAStop           sp_regaddmultistring      sp_regdeletekey
  sp_regdeletevalue   sp_regenumvalues    sp_regremovemultistring   sp_sdidebug
  xp_availablemedia   xp_cmdshell         xp_deletemail             xp_dirtree
  xp_dropwebtask      xp_dsninfo          xp_enumdsn                xp_enumerrorlogs
  xp_enumgroups       xp_enumqueuedtasks  xp_eventlog               xp_findnextmsg
  xp_fixeddrives      xp_getfiledetails   xp_getnetname             xp_grantlogin
  xp_logevent         xp_loginconfig      xp_logininfo              xp_makewebtask
  xp_msver            xp_regread          xp_perfend                xp_perfmonitor
  xp_perfsample       xp_perfstart        xp_readerrorlog           xp_readmail
  xp_revokelogin      xp_runwebtask       xp_schedulersignal        xp_sendmail
  xp_servicecontrol   xp_snmp_getstate    xp_snmp_raisetrap         xp_sprintf
  xp_sqlinventory     xp_sqlregister      xp_sqltrace               xp_sscanf
  xp_startmail        xp_stopmail         xp_subdirs                xp_unc_to_drive
  xp_dirtree          xp_availablemedia   xp_cmdshell               xp_deletemail
  xp_dropwebtask      xp_dsninfo          xp_enumdsn                xp_enumerrorlogs
  xp_enumgroups       xp_enumqueuedtasks  xp_eventlog               xp_findnextmsg
  xp_fixeddrives      xp_getfiledetails   xp_getnetname             xp_grantlogin
  xp_logevent         xp_loginconfig      xp_logininfo              xp_makewebtask
  xp_msver xp_regread 
27. Encrypt all stored procedures, triggers, views and user defined functions. Granted, it is very easy to break such encryption if you know how, but most people don’t. If you want to go all the way with this, there are third-party tools available that provide much stronger encryption than is provided by the WITH ENCRYPTION clause.Ensure that the permissions on jobs are tightened all the way, in case the SQL Agent service is activated. This will prevent low-privilege users from submitting or managing jobs.

28. Secure the SQL registry, restricting access to the SQL Server specific registry keys such as HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.

Last updated 22 May 2009

Return to the Artful SQL Server & Access Tips page