SQL Server 2000 security best practices & CheckList

SQL Server security best practices
Here is an ideal implementation of security in a Windows NT/2000 environment with SQL Server 7.0/2000 database server:

• Configure SQL Server to use Windows authentication mode
• Depending upon the data access needs of your domain users, group them into different global groups in the domain
• Consolidate these global groups from all the trusted domains into the Windows NT/2000 local groups in your SQL Server computer
• The Windows NT/2000 local groups are then granted access to log into the SQL Server
  Add these Windows NT/2000 local groups to the required fixed server roles in SQL Server
• Associate these local group logins with individual user accounts in the databases and grant them the required permissions using the database roles
• Create custom database roles if required, for finer control over permissions

Here is a security checklist and some standard security practices and tips:

1. Restrict physical access to the SQL Server computer. Always lock the server while not in use.
2. Make sure, all the file and disk shares on the SQL Server computer are read-only. In case you have read-write shares, make sure only the right people have access to those shares.
3. Use the NTFS file system as it provides advanced security and recovery features
4. Enable login auditing at the Operating System and SQL Server level. Examine the audit for login failure events and look for trends to detect any possible intrusion
5. Keep yourself up-to-date with the information on latest service packs and security patches released by Microsoft. Carefully evaluate the service packs and patches before applying them on the production SQL Server. Bookmark this page for the latest in the security area from Microsoft: http://www.microsoft.com/security/
6. Rename the Windows NT/2000 Administrator account on the SQL Server computer to discourage hackers from guessing the administrator password
7. If it is appropriate for your environment, hide the SQL Server service from appearing in the server enumeration box in Query Analyzer, using the /HIDDEN:YES switch of NET CONFIG SERVER command
8. If your databases contain sensitive information, consider encrypting the sensitive pieces (like credit card numbers and Social Security Numbers (SSN)). There are undocumented encryption functions in SQL Server, but I wouldn't recommend those. If you have the right skills available in your organization, develop your own encryption/decryption modules using Crypto API or other encryption libraries.
9. Secure your registry by restricting access to the SQL Server specific registry keys like HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
10. SQL Server error logs can reveal a great deal of information about your server. So, secure your error logs by using NTFS permissions
11. Carefully choose the members of the sysadmin role, as the members of the sysadmin role can do anything in the SQL Server. Note that, by default, the Windows NT/2000 local administrators group is a part of the sysadmin fixed server role
12. Discourage applications from executing dynamic SQL statements. To execute a dynamic SQL statement, users need explicit permissions on the underlying tables. This defeats the purpose of restricting access to base tables using stored procedures and views
13. Disable guest user account of Windows. Drop guest user from production databases using sp_dropuser
14. Prevent unauthorized access to linked servers by deleting the linked server entries that are no longer needed. Pay special attention to the login mapping between the local and remote servers. Use logins with the bare minimum privileges for configuring linked servers
15. When using mixed mode authentication, consider customizing the system stored procedure sp_password, to prevent users from using simple and easy-to-guess passwords
16. Do not save passwords in your .udf files, as the password gets stored in clear text
17. If your database code is proprietary, encrypt the definition of stored procedures, triggers, views and user defined functions using the WITH ENCRYPTION clause. dbLockdown is a tool that automates the insertion of the WITH ENCRYPTION clause and handles all the archiving of encrypted database objects so that they can be restored again in a single click.
18. Store the data files generated by DTS or BCP in a secure folder/share and delete these files once you are done
19. SQL Server 2000 allows you to specify a password for backups. If a backup is created with a password, you must provide that password to restore from that backup. This discourages unauthorized access to backup files
20. Windows 2000 introduced Encrypted File System (EFS) that allows you to encrypt individual files and folders on an NTFS partition. Use this feature to encrypt your SQL Server database files. You must encrypt the files using the service account of SQL Server. When you want to change the service account of SQL Server, you must decrypt the files, change the service account and encrypt the files again with the new service account