MS SQL 2005 Security Settings

Safety

Get MS SQL 2005 not yet a day, overall, MS SQL 2005 is more secure than SQL 2000, but the default settings are the same as the original vulnerability, let's modify the default settings.

1, the installation of MSSQL using mixed mode, of course, the SA password best can not be empty, in the SQL2005, the SA can be modified or deleted super User name.
Use master
Alter LOGIN [SA] with NAME=[ZXS]/* Modify SA Account * *
Sp_password ' 111111 ', ' 123456 ', ' sa '/* Modify sa Password * *
Use the above command to modify the SA account number or to make a graphical change
Use a secure account policy. The strongest protection for SA or equivalent users, including, of course, the use of a very strong password, as shown below

Here you can see the increase in SQL 2005 over SQL 2000.

2, the SQL certification has Windows identity authentication and mixed identity authentication. In 2005, login SQL can use indows identity authentication and mixed identity authentication convenience, if you do not want the system administrator to contact the database, you can in security = login name is the SYSTEM account "BUILTIN\Administrators" deleted. Of course, there are other redundant accounts can also be prohibited, only need to be in the account = = Attribute = = state whether to allow the connection to the database engine to reject, and login to disable can be, but the disadvantage is that when you forget the sa password, I can not help you.

You can also use the command sp_change_users_login ' the ' "to search for a separate account and delete

3. Managing extended Stored Procedures
Remove unnecessary stored procedures, because some stored procedures can easily be exploited to elevate permissions or destroy them.
If you do not need to extend the stored procedure xp_cmdshell please remove it. xp_cmdshell is simply a big back door.

If you do not need to discard OLE automatic stored procedures (which can cause certain features in the manager to not be used), these procedures include the following:
sp_OACreate sp_OADestroy sp_OAGetErrorInfo sp_OAGetProperty
sp_OAMethod sp_OASetProperty sp_OAStop
Removing unwanted registry access stored procedures, the registry stored procedure can even read out the password of the operating system administrator as follows:
Xp_regaddmultistring Xp_regdeletekey Xp_regdeletevalue xp_regenumvalues
Xp_regread xp_regremovemultistring xp_regwrite
There are other extended stored procedures that you might want to check.
When dealing with stored procedures, be sure to avoid causing damage to the database or the application. Run the following SQL statement to remove all the dangerous SPS.

DROP PROCEDURE sp_makewebtask
EXEC master.. Sp_dropextendedproc xp_cmdshell/* Command line *
EXEC master.. Sp_dropextendedproc xp_dirtree/* Can expand the directory you need to know, get all directory depth * *
EXEC master.. Sp_dropextendedproc xp_fileexist/* is used to determine whether a file exists/*
EXEC master.. Sp_dropextendedproc xp_getnetname/* can get server name * *
EXEC master.. Sp_dropextendedproc xp_terminate_process
EXEC master.. Sp_dropextendedproc sp_OAMethod
EXEC master.. Sp_dropextendedproc sp_OACreate
EXEC master.. Sp_dropextendedproc xp_regaddmultistring
EXEC master.. Sp_dropextendedproc Xp_regdeletekey
EXEC master.. Sp_dropextendedproc Xp_regdeletevalue
EXEC master.. Sp_dropextendedproc Xp_regenumkeys
EXEC master.. Sp_dropextendedproc xp_regenumvalues
EXEC master.. Sp_dropextendedproc Sp_add_job
EXEC master.. Sp_dropextendedproc sp_addtask
EXEC master.. Sp_dropextendedproc Xp_regread
EXEC master.. Sp_dropextendedproc xp_regwrite
EXEC master.. Sp_dropextendedproc Xp_readwebtask
EXEC master.. Sp_dropextendedproc Xp_makewebtask
EXEC master.. Sp_dropextendedproc xp_regremovemultistring
EXEC master.. Sp_dropextendedproc sp_OACreate
DROP PROCEDURE Sp_addextendedproc

5, SQL Server 2005 itself has encryption capabilities, fully integrated a key management architecture. However, it is best to use SSL to encrypt the protocol when remote network connections, which requires a certificate to support it. Search here will have a lot of instructions in this regard, do not say more.

6. Use IPSec policy to block all addresses from accessing native TCP1433 and UDP1434 ports, or modify TCP1433 ports, but in SQL2005, TCP dynamic ports can be used (in SQL Server Configuration Manager's SQL 2005 network configuration) is shown in the following figure

[1] [2] Next page