SQL Server Security 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_dropextendedprocxp_cmdshell

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 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_w.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 NHKEY_LOCAL_MACHINE, NSOFTWARE MicrosoftMSSQLServerMSSQLServer, NAuditLevel, 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 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_USERSOFTWAREMicrosoft

MSSQLServerSQLEW Regedi

Sqls 6.5

Now the data is hidden in

HKEY_USERS {yourSID} softwareMicrosoftMicrosoft SQL server80oolSQLEWegistered 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 the monthly