SQL Server Security Checklist Reposted from: http://www.sqlsecurity.com 1. Verify that NT / 2000 and SQL Server's latest patch is installed, don't say everyone should have installed, but I think it is best Here it is reminded here.
2. Assess and select a network protocol that taking into account the biggest security but does not affect the function. Multi-protocols are wise choices, but it sometimes cannot be used in a heterogeneous environment.
3. Set a strong password to the "SA" and "ProBe" account to strengthen its security. Set a strong password and save it in a safe place. Note: The ProBe account is used to perform performance analysis and distribution. When used in standard security modes, set high-intensity passwords to this account to affect certain functions.
4. Use a low privileged user as a query account for SQL Server Services, do not use localsystem or sa. This account should have the minimum right (note that the right to run as a service is required) and should include (but not stop) attacks in the server in the case of compromise. Note that when using the Enterprise Manager to do more, the ACLs on the file, the registry, and the user rights are handled simultaneously.
5. Determine all SQL server data, and the system file is the device in the NTFS partition, and the Appropraite ACLS is applied. If someone gets access to the system, this level of permissions can prevent intruders from damaging data to avoid a major disaster.
6. If you do not use xp_cmdshell to turn off. If you use SQL 6.5, at least the SQLExecutieCmdexec account operation in Server Options limits non-SA users using XP_cmdshell. In any ISQL / OSQL window (or query analyzer):
Use master exec sp_dropextendedProc'XP_cmdshell '
For details on SQLEXECUTIVECMDEXEC, please see the following article:
Http://support.microsoft.com/support/kb/article/q159/2/21.
If you don't need xp_cmdshell, please stop it. Remember that a system administrator can always increase it back if needed. This is also very good - a invasive person may find that it is not, just add him back. Considering that it is also removed from the DLL below but must be tested before some DLL is used by some programs. To find any other programs use the same DLL:
First get the DLL.
Select O.Name, C.Text from dbo.syscomments c, dbo.sysobjects o where c.id = o.id and o.name = 'xp_cmdshell'
Second, use the same DLL to find other extended storage operations to use the DLL.
Select O.Name, C.Text from dbo.syscomments c, dbo.sysobjects o where c.id = o.id and c.text = 'xplog70.dll'
Users can use the same approach to process the other you want to remove in the following steps.
7. If you don't need to deactivate an object connection and embedding automation storage program (Warning - When these storage programs are derecianted, some enterprise manager features may be lost). These include:
Sp_oacreate
Sp_oadestroy
SP_OAGETERRORINFOFO
SP_OAGETPROPERTY
Sp_oamethod
Sp_oasetproperty
SP_OASTOP
If you decide to stop the process, please write a script. You can re-add them back when you use them. Remember, what we are doing here is the function of locking an app - Your development platform should be placed on other machines.
8. Disable the registry access program you don't need. (With the above warning), these include:
XP_regaddMultiTRINGXP_REGDELETEKEY
XP_Regdeletevalue
XP_RegenumValues
XP_REGREMOVEMULTINTRING
Note: I have previously listed XP_REGREAD / XP_REGWRITE here but the removal of these programs has some main features including logs and sp. The installation is not recommended.
9. Remove other systems that you think will cause threats. This process is quite a lot, and they will also waste some CPU time. Be careful not to do so on a configuration server. First test on the developed machine, confirm that this does not affect any system function. Here is some of the lists we recommend to be assessed:
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_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
10. Disable the default login under "Security Options" in Enterprise Manager. (Only SQL 6.5) When using the integrated security, this makes unrecognizable not in the syslogins table to log in to a valid database server. 11. Remove the Guest account of the database to put unrecognized users. Exceptions are Master and Tempdb databases because they are required for their GUEST account. 12. If it is not necessary, please disable the SQL mail functionality. Its presence makes potential attackers to deliver potential Trojans, viruses or simple implementation of a DOS attack
13. Check Master..np_helpstartup to see if there is a terrible Trojan process. Determine no one is already placing the secret back door program. Use sp_unmakestartup to remove any suspicious process.
14. Check if master..np_password has a Trojan code. Compare your product Scripts and a newly installed system default scripts and convenient save.
15. Record all users access access. Do these settings from Enterprise Manager or by entering the query analyzer with SA: xp_instance_regwrite n'hkey_local_machine ', N'Software / Microsoft / MSSQLServer / MSSQLSERVER', N'Auditlevel ', Reg_dword, 3
16. Rewinding applications Use more user-defined storage and viewing processes So the general pair access can be disabled. Here you should also see how performance boosts do not have to regular query planning operations. 17. Remove unwanted network protocols. 18. Pay attention to the physical security of the SQL server. Lock it in a fixed room and pay attention to the safety of the key. As long as there is a chance to go to the server, you will always find a method to enter. 19. Create a planned task run: FindSTR / C: "Login Failed" / MSSQL7 / log /*.* "and then redirect output to a text file or email, so you monitor the failed login attempt. This also provides a good way to record attacks for system administrators. There are also many third-party tools to analyze NT log events. Note: You may need to change the path to the path you installing SQL.
20. Set illegal access and login failure log alerts. To the "Manager SQL Server Messages" in the Enterprise Manager search for any messages that have no right to access (starting from finding "login failed" and "Denied"). Make sure all of your interested information is recorded to the event log. Then set an alert on this information, send an email or information to an operator that can respond to the problem.
21. Make sure the roles at the server and database level are only given to the needs of users. When the SQL Server Security Model 7 has many enhancements, it also adds additional licensing layers, we must monitor this layer, determine that no one is awarded to exceed the required permissions.
22. Regularly check all members of the group or role and determine the distribution permission of the group so that your audit work can be simplified. Determine when you are, the public group cannot perform the selection operation from the system table. 23. Take some time to audit the request to log in with a empty password. Use the following code to check: Use the main body selection name, Password from syslogins where password is null order by name
24. If possible, use the integrated security policy in your organization. By using integrated security policies, you can rely on system security, maximizing management work from maintaining two separate security models. This also does not allow the password close to the connection string. 25. Check all non-SA users' access processes and privileges to expand storage processes. Use the following query to periodically query which process has public storage privileges. (Using the "type" instead of "xtype" in SQL Server): Use master select sysobjects.name from sysobjects, sysprotects where sysprotects.uid = 0 AND xtype in ( 'X', 'P') AND sysobjects.id = sysprotects .id Order by Name
26. When using the Enterprise Manager, use the integrated security policy. In the past, enterprise manager was found in the standard security mode to store the "SA" password in the registry of PlainText. Note: Even if you change the modal, the password will remain in the registry. Use regedit and check the key: hkey_current_user / Software / Microsoft / MSSQLServer / SQLEW / RegeDi / SQL 6.5
Now the data is hidden
HKEY_USERS / {Yoursid} / Software / Microsoft / Microsoft SQL Server / 80 / Tool / Sqlew / Registered Server X / SQL Server Group
("SQL Server Group" is the default value but you may have established a user group so it changes its location accordingly.)
