|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$
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: :
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
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:
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.
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.