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.

Return to the Artful SQL Server & Access tips page