[SQL Server] SQL Server Security Check List

Document directory

• Windows SQL Server Security Check List

Windows SQL Server Security Check List

1. confirm that the latest patches for NT/2000 and SQL server have been installed. Needless to say, you should have installed them, but I think it is best to remind you here.

2. evaluate and select a network protocol that considers maximum security but does not affect functionality. Multi-Protocol is a wise choice, but it is sometimes not used in a heterogeneous environment.
3. Set strong passwords for "sa" and "probe" accounts to enhance their security. Set a strong password and store it in a safe place. Note: The probe account is used for performance analysis and distribution transmission. When used in standard security mode, setting a strong password for this account can affect the use of certain functions.
4. Use a low-privilege user as the account for querying 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 contain (but do not stop) Attacks on the server in case of compromise. Note that when you use the Enterprise Manager for the preceding settings, the files, registries, and ACLs on user rights are processed at the same time.
5. determine all SQL server data, and the system file is mounted to the NTFS partition, and appropraite ACLs is applied. If someone gets access to the system, this level of permission can prevent intruders from damaging data and avoid causing a disaster.
6. If xp_cmdshell is not used, disable it. If SQL 6.5 is used, at least use sqlexecutiecmdexec account in server options to restrict non-sa users from using xp_javasshell.
In any iSQL/osql window (or query analyzer ):
Use master
Exec sp_dropextendedproc 'xp _ export shell'
If you do not need xp_mongoshell, disable it. Remember that a system administrator can always add it back if needed. This is also not good-an intruder may find that it is not there, just add it back. Consider also removing the DLL below, but before removing it, you must test it because some DLL is used by some programs at the same time. To find out whether other programs use the same dll:
Obtain the DLL first.
Select O. Name, C. Text from DBO. syscomments C, DBO. sysobjects O where c. ID = O. ID and O. Name = 'xp _ Your shell'
Second, use the same DLL to find whether other extended storage operations 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'
You can use the same method to process other processes you want to remove in the following steps.
7. Disable object connection and embed Automated Storage programs if not needed (warning-some Enterprise Manager functions may be lost when these storage programs are disabled). These include:
Sp_oacreate
Sp_oadestroy
Sp_oageterrorinfo
Sp_oagetproperty
Sp_oamethod
Sp_oasetproperty
Sp_oastop
If you decide to stop the process, please write a script for them so that you can re-add them when you use them later. Remember, what we are doing here is locking an application's function-your development platform should be placed on another machine.
8. Disable the Registry access program you do not need. (As mentioned above) These include:
Xp_regaddmultistring
Xp_regdeletekey
Xp_regdeletevalue
Xp_regenumvalues
Xp_regremovemultistring
Note: I used to list xp_regread/xp_regwrite here. However, the removal of these programs affects the installation of some main functions including logs and SP, so their removal is not recommended.
9. Remove other system storage processes that you think pose a threat. Such processes are quite large, and they also waste some CPU time. Be careful not to do this on a configured server first. First, test on the developed machine and confirm that this will not affect any system functions. Below are some of the lists we recommend for your evaluation:
Sp_sdidebug
Xp_availablemedia
Xp_mongoshell
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 default logon under "Security Options" in Enterprise Manager. (Only SQL 6.5) when integrated security is used, unauthorized users who are not in the syslogins table are not authorized to log on to a valid database server.
11. Except for the Guest account of the database, unauthorized user data is excluded. The exception is the master and tempdb databases, because they are required for their guest accounts.
12. If not required, disable the SQL mail function completely. Its existence makes it possible for potential attackers to deliver potential Trojans, viruses, or simply implement a DoS attack.
13. Check master .. sp_helpstartup to see if there are any suspicious Trojan processes. Make sure no one has placed a secret backdoor program here. Use sp_unmakestartup to remove any suspicious processes.
14. Check master .. sp_password to see if there is any Trojan code. Compare your product scripts with the default scripts of a newly installed system and save it conveniently.
15. Record all user access information. Perform these settings from the Enterprise Manager or log in to the SA to access the following items of the query Analyzer:
Xp_instance_regwrite n 'HKEY _ LOCAL_MACHINE ', n' software/Microsoft/MSSQLServer', n' auditlevel ', REG_DWORD, 3
16. Rewrite the application to use more user-defined storage and view processes. Therefore, access to tables in general can be disabled. Here you should also see the performance improvement caused by not having to perform regular query plan operations.
17. Remove unnecessary network protocols.
18. Pay attention to the physical security of the SQL Server. Lock it in a fixed room and pay attention to key security. As long as you have the opportunity to access the server, you will always find a way to access.
19. Create a scheduled task run:
Findstr/C: "Login Failed"/MSSQL7/log /*.*'
Then redirect the output to a text file or email, so you monitor failed login attempts. This also provides a good method for system administrators to record attacks. There are also many third-party tools used to analyze nt log events. Note: you may need to change the path to your SQL installation path.
20. set an alarm for illegal access and logon Failure logs. Go to "Manager SQL Server messages" in the Enterprise Manager to search for any messages with no access permissions (starting from "Login Failed" and "Denied ). make sure all the information you are interested in is recorded in the event log. Then, an alert is set on the information to send an email or message to an operator who can respond promptly to the problem.
21. Make sure that the roles at the server and database levels are only assigned to the desired users. When SQL Server Security Model 7 has many enhancements, It also adds an additional licensing layer. We must monitor this layer to ensure that no one has been granted more than required permissions.
22. Regular inspection teams or all members of the role are determined to assign permissions to the group, so that your audit work can be simplified. Make sure that when you are there, the public group cannot select from the system table.
23. Take some time to audit requests logging in with a blank password. Use the following code to check the empty password:
Subject
Select name,
Password
From syslogins
Where password is null
Order by name
24. If possible, use integrated security policies in your organization. By using integrated security policies, you can rely on system security to simplify management work from maintaining two separate security models. This also prevents the password from approaching the connection string.
25. Check the access process and extended storage process permissions of all non-sa users. Use the following query to regularly query which process has the public storage permission. (Use "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 enterprise manager was used, integrated security policies were used. In the past, Enterprise Manager was found to store the "sa" password in the plaintext of the Registry in the standard security mode. Note: Even if you change the mode, the password will remain in the registry. Use regedit and check the key:
HKEY_CURRENT_USER/software/Microsoft/
MSSQLServer/sqlew/regedi/
Sqls 6.5
Now the data is hidden in
HKEY_USERS/{yoursid}/software/Microsoft SQL Server/80/tool/sqlew/registered server x/SQL Server Group
("SQL Server group" is the default value, but you may have created a user group to change its location accordingly)
27. Develop an audit plan and set up monthly security reports. reports available to it supervisors include any new exploit, successful attacks, backup protection, and Object Access failure statistics.
28. Do not allow users to log on to SQL Server interactively. This rule applies to any server. Once a user can interactively enter a server, the administrator privilege can be obtained.
30. Try your best to restrict the query and access operations on SQL Server. You can use the minimum permission to query many things in SQL Server. If not, do not give them a chance.