SQL Server Security Check list

Source: Internet
Author: User
Tags add include sql server mssqlserver sql query access strong password
server| Security
Turn from: http://www.sqlsecurity.com

1. Confirm that you have installed the latest patches for nt/2000 and SQL Server, needless to say everyone should have been installed, but I think it is best to remind you here.

2. Evaluate and select a network protocol that takes into account maximum security but does not affect functionality. Multi-Protocol is a wise choice, but it can sometimes not be used in heterogeneous environments.

3. Establish strong passwords for the "SA" and "probe" accounts to enhance their security. Set a strong password and keep it in a safe place. Note: The probe account is used for profiling and distributing transmissions. When used in standard safe mode, setting a high strength password for this account can affect the use of certain functions.

4. Use a low privileged user as a query operation account for the SQL Server service and do not use LocalSystem or SA. This account should have the smallest right (note that the right to run as a service is required) and should contain (but not stop) the attack on the server in the case of compromise. Note that when you use Enterprise Manager to do this, the file, registry, and user rights ACLs are processed at the same time.

5. Determine all SQL server data, and system files are installed on NTFS partitions, and Appropraite ACLs is applied. If someone has access to the system, this layer of permissions can prevent intruders from destroying the data and avoiding a major catastrophe.

6. Turn it off if you don't use xp_cmdshell. If you use SQL 6.5, at least use the Sqlexecutiecmdexec account operation in the server options to restrict non-SA users from using xp_cmdshell.
In any Isql/osql window (or Query Analyzer):

Use master
exec sp_dropextendedproc ' xp_cmdshell '

For more information on SQLExecutiveCmdExec, see the following articles:

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

If you don't need xp_cmdshell, please deactivate it. Keep in mind that a system administrator can always add it back if needed. It's not good either-an intruder may find it not, just add him back. Consider also removing the following DLLs but must be tested before removing them because some DLLs are used by some programs at the same time. To find out if another program uses the same DLL:

Get the DLL first.

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 discover 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 '

Users can use the same approach to the other processes you want to remove in the following steps.



7. Deactivate an object connection and embed an automated store program (warning-When these stored programs are deactivated, some Enterprise Manager features may be lost) if you do not need to. These include:

sp_OACreate

sp_OADestroy

sp_OAGetErrorInfo

sp_OAGetProperty

sp_OAMethod

sp_OASetProperty

sp_OAStop

If you decide to deactivate the process, write them a script so you can add them back when you use them later. Remember, what we're doing here is locking in the functionality of an application-your development platform should be on other machines.



8. Disable registry accessors that you do not need. (with the above warning) These include:

Xp_regaddmultistring

Xp_regdeletekey

Xp_regdeletevalue

Xp_regenumvalues

Xp_regremovemultistring

Note: I used to list xp_regread/xp_regwrite here, but the removal of these programs affects some of the main features including logging and SP installation, so their removal is not recommended.



9. Remove other system storage processes that you believe will pose a threat. This process is 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 to make sure that this does not affect any system functionality. Below are some of the lists we recommend for your evaluation:

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 consolidated security, this allows unauthorized users in the syslogins table to log on to a valid database server.
11. The Guest account for the removal of the database excludes unauthorized users. Exceptions are the master and tempdb databases, because they are required for their Guest account.
12. Disable the SQL Mail feature completely if not necessary. Its presence allows potential attackers to deliver potential trojans, viruses, or simply implement a Dos attack.

13. Check Master. Sp_helpstartup See there is no suspicious Trojan process. Make sure no one has placed a secret backdoor in here. Use Sp_unmakestartup to remove any suspicious processes.

14. Check Master. Sp_password See if there is any Trojan code. Compare your product scripts with the default scripts for a newly installed system and save it conveniently.

15. Record all user access information. Make these settings from the Enterprise Manager or enter the following on the Query Analyzer by using SA login:
Xp_instance_regwrite n ' HKEY_LOCAL_MACHINE ', n ' software\ microsoft\mssqlserver\mssqlserver ', n ' AuditLevel ', REG_ dword,3

16. Overriding the application uses more user-defined storage and viewing processes so general access to the table can be disabled. Here you should also see performance improvements due to the lack of frequent query planning operations.
17. Remove unwanted network protocols.
18. Note The physical security of the SQL Server. Lock it in a fixed room and pay attention to the safety of the key. Whenever you have a chance to go to the server, you will always find a way to get in.
19. Establish a scheduled task to 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 way for the system administrator to record attacks. There are also a number of third-party tools used to analyze NT log events. Note: You may need to change the path to the path where you installed the SQL.

20. Set illegal access and login failure log alerts. Go to manager SQL Server Messages in Enterprise Manager to search for any messages that are not authorized to access (starting with "Login failed" and "denied"). Make sure all information you are interested in is logged to the event log. Then set up alerts on the information and send an email or message to an operator who can respond to the problem in a timely manner.

21. Determine that roles at the server and database levels are granted only to the users who need them. When SQL Server security Model 7 has many enhancements, it also adds additional licensing layers, and we must monitor the layer to make sure no one has been granted more than the required permissions.

22. The regular inspection Unit or the role of the entire membership and determine the use of groups to assign permissions, so that your audit can be simplified. Make sure that when you are there, the public group cannot perform a select operation from the system table.
23. Spend some time auditing a request to log in with a blank password. Use the following code for an empty password check:
Use Principal
Choose a name,
Password
From syslogins
where password is null
Order BY name


24. If possible, take advantage of the consolidated security policy in your organization. By using a consolidated security policy, you can rely on the security of the system, and the maximum simplification of management is decoupled from maintaining two separate security models. This also does not allow the password to approach the connection string.
25. Check the permissions of all non-SA users for the access process and the extended storage process. Use the following query to periodically query which process has public storage permissions. (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. Use the integrated security policy when using Enterprise Manager. In the past, Enterprise Manager was found to store the "sa" password in the plaintext of the registry in a standard safe mode. Note: Even if you change the mode, the password will remain in the registry. Use regedit and check keys:
HKEY_CURRENT_USER Oftware\microsoft\
MSSQLServer qlew\ regedi\
SQL 6.5

Now the data is hidden in

HKEY_USERS\{YOURSID} oftware\microsoft\microsoft SQL Server\80\tool qlew\registered server X QL server group

("SQL Server Group" is the default value but you may have established a user group and therefore changed its position accordingly)

27. Develop an audit plan and provide monthly security reports, and reports available to it executives 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 is able to interactively enter a server, there is access privileges that can be used to gain administrator privileges.
30. Try to limit queries and access to SQL Server. Users can query for a lot of things in SQL Server with minimal permissions. Don't give them a chance unless you have to.


Related Article

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.