MSSQL 2005 Security Settings graphics and text tutorial _mssql2005

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

This is the description in SQL Help
If an instance of SQL Server is already configured to listen on a dynamic port, at startup, the instance checks the available ports in the operating system and opens an endpoint for that port. Incoming connections must specify the port number to which you want to connect. Because the port number may change each time you start SQL Server, SQL Server provides a SQL Server browser server to monitor the port and point incoming connections to the current port of the instance.
You can also listen to each IP

This is the description in SQL Help
Specifies whether SQL Server listens for all IP addresses that are bound to computer network cards. If set to no, each IP address is configured with the respective Properties dialog box for each IP address. If set to Yes, the settings for the Ipall property box apply to all IP addresses. The default value is yes.

Of course, you can also create a new alias in the SQL Native Client configuration below to specify your SQL Server and port, or you can connect to the server attribute = = Remote Server link = = Remove the hook to allow remote link to this server
Turn off remote link command behavior
EXEC sys.sp_configure n ' Remote access ', n ' 0 '

8. IP restrictions on remote network Connections, SQL Server 2005, like SQL 20,001, does not provide a secure solution for network connectivity, but the Windows 2K system provides IPSec policy. Restrict the IP of a remote network connection, only to ensure that the required IP is accessible, deny other IP port connections, and minimize security threats.

9, in the server's property security, enable login audit failure and successful landing, enable C2 audit tracking, C2 is a government security level, it ensures that the system can protect resources and have sufficient audit capacity. The C2 mode allows us to monitor all access attempts to all database entities. As shown in figure

The command to enable C2 auditing is
EXEC sys.sp_configure n ' c2 audit mode ', n ' 1 '

The above method is not very complete, because the time relationship will be studied slowly.