Step 30 check the 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 delivery. 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 a service is required) and should include (but do not stop) Attacks against the server in the case of compromise. Note that when you use the Enterprise Manager for the above settings,
Files, registries, and user rights are simultaneously processed.

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 sqlexecutiecmdexec account operation restrictions in server options are used. Non-sa users use xp_javasshell.

In any iSQL/osql window (or query analyzer ):

Use master

Exec sp_dropextendedproc 'xp _ export shell'

For details about sqlexecutivecmdexec, refer to the following articles:

Http://support.microsoft.com/support/kb/article/Q159/2/21.

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 attacker may find that the attacker is not present and only needs to add the attacker 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 ).
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 the function of an application-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.

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 microsoftmssqlservermssqlserver', 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" mssql7log *.*'

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 Enterprise Manager
"Search for any messages that do not have access permissions (start with" 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_usersoftwaremicrosoft

Mssqlserversqlew regedi

Sqls 6.5

Now the data is hidden in

HKEY_USERS/softwaremicrosoftmicrosoft SQL servertoolsqlewregistered server XSQL 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.