SQL Server 2008 security settings

Server Authentication

MSSQL Server 2008 has two authentication modes: Windows authentication mode and Windows authentication mode (hybrid mode ). For most database servers, SQL Server Authentication is enough. Unfortunately, this option is not available in the current Server authentication mode, therefore, we can only select a mode with both SQL Server and Windows Authentication (mixed mode ). However, there are two problems:
 
1. The hybrid mode includes the mode we don't need for Windows authentication, that is, the redundancy of settings. Program Security is inversely proportional to redundancy.
 
2. For Windows Authentication, you can log on to MSSQL Server by using the logon creden。 of the current Windows Administrator account (usually Administrator. Using Windows Authentication increases the risk of Administrator password theft.
 
To solve the preceding two problems, we need to restrict Windows Authentication in hybrid mode. The method is as follows:
 
Open Microsoft SQL Server Management Studio, Click Security-> logon name, and delete the logon name corresponding to Administrator.
 
SQL Server service running identity
 
By default, the SQL Server Service runs as a local system. That is to say, the SQL Server service process has all operation permissions on the system, which is very insecure. Therefore, we need to change the SQL Server service running identity to a common user:

1. Create a common user, such as mssqluser, and add the user to the following two groups:
SQLServerMSSQLUser
SQLServerSQLAgentUser
2. Grant the read permission of the mssqluser on drive C, the modification permission of mssqluser on other partitions such as D and E, and the mssql installation directory (such as D:/Program Files/Microsoft SQL Server ), full control permissions of mssqluser in the database file storage directory (such as D:/mssqldata.
3. Change the logon of the SQL Full-text Filter Daemon Launcher (MSSQLSERVER) SQL Server (MSSQLSERVER) service to the mssqluser user, and then restart the two services.

Sa password security
 
Many database server administrators will find that someone is always happy to crack the sa password. Therefore, BT sa password becomes an indispensable step. In addition, we can rename sa to users such as sa1, sa2, sa3. so as to further improve the security of the sa password. After modification, do not forget to update the management program with the sa password.

SQL Server installation is undoubtedly the most important. It not only causes severe SQL injection on the website, but also threatens server control.
The following describes common security settings for users who have installed SQL software.

Modify SA user name:

1: Open the query analyzer and log on to it (haha, whatever account you use, but you must have the db_owner permission in the master database).

The code is as follows:	Copy code
Sp_configure 'allow updates', 1 Go RECONFIGURE WITH OVERRIDE Go Then run the following code: Update sysxlogins set name = 'the name you want to change to 'where sid = 0x01 Update sysxlogins set sid = 0xE765555BD44F054F89CD0076A06EA823 where name = 'name you want to change to name' Next: Sp_configure 'allow updates', 0 Go RECONFIGURE WITH OVERRIDE Go

Then, check the role of SQL SERVER and you will find that the SA user name has been changed to your new name.

1. Modify Port 1433 and how to modify the SQL Port
 

Click Start-program-Microsoft SQL Server-Server network utility"
Select "TCP/IP" in "enabled protocol" and click "properties ":
Enter the new default port number and click "OK ":
Click "OK ".
 

Modify and enter localhost in the database connection IP address of the website to connect to the database.

2. External enterprise manager or other tools are not allowed to connect to Port 1433.

Set in the local security policy to prohibit non-server IP addresses from connecting to the SQL database. Import the rules in the attachment to the local security policy and assign them.
This security policy is a required policy for virtual host space service providers. It has been tested. (after modification, Port 1433 does not work for SQL blocking, but it can greatly increase server security)

3. Uninstalling insecure SQL components

Xp_mongoshell (this must be the first, needless to say), Xp_regaddmultistring, Xp_regdeletekey, Xp_regdeletevalue, Xp_regenumvalues, Xp_regread, Xp_regwrite, Xp_regremovemultistring
Run the command "uninstall: delete xp_dirtree" in the SQL queryer. The command is sp_dropextendedproc 'XP _ dirtree'
 

4. Permission settings

First, create a common user in the users group in computer management to run MSSQL.
Second, the database should be separately installed on a non-C disk, non-website directory disk (assuming that your data is installed on E:/Program Files/Microsoft SQL Server /)
The root directory (E:/) of the edisk requires the following permissions:
All permissions for "administrators"
All permissions of "System"
The "SQL running user sets" read "and" Read and run "permissions, and in the advanced settings (only apply to the current folder)
The following permissions are required for the database installation directory (E:/Program Files/Microsoft SQL Server:
All permissions for "administrators"
All permissions of "System"
All permissions of "SQL running user"

How to run databases with common permissions

Enterprise Manager-local-right-click to view "properties" and switch to "security"
 

After the preceding settings, the SQL statements can be kept in a good security status. In addition, the website security must be well done. Otherwise, intrusion through other channels will also be wasted.

It is recommended that users on their own servers install or the Western Digital Security assistant to facilitate website security.

Finally, I will add some security knowledge to you.

1. Use a secure password policy

We put the password policy in the first step of all security settings. Please note that the passwords of many database accounts are too simple, which is the same as the system password. Do not write the sa account password in an application or script. Robust passwords are the first step to security!

When installing SQL Server in hybrid mode, you need to enter the sa password unless you confirm that you must use a blank password. This is better than the previous version.
At the same time, develop a good habit of regularly changing passwords. The database administrator should regularly check whether there are accounts that do not meet the password requirements. For example, use the following SQL statement:

The code is as follows:	Copy code
Use master Select name, Password from syslogins where password is null

2. Use a secure account policy.

Because SQL Server cannot change the sa user name or delete this ultimate user, we must provide the strongest protection for this account, including using a very strong password, it is best not to use the sa account in database applications. SAS is used only when no other method is used to log on to the SQL Server instance (for example, when other system administrators are unavailable or forget the password. It is recommended that the database administrator create an ultimate user with the same permissions as sa to manage the database. Secure account policies also include preventing account flooding with administrator permissions.

The authentication modes of SQL Server include Windows Authentication and hybrid authentication. If the database administrator does not want the operating system administrator to log on to the database through the operating system, the system account "BUILTIN \ Administrators" can be deleted in account management. However, the result is that once the sa account forgets its password, there is no way to restore it.

A large number of hosts use database applications for simple functions such as query and modification. Please assign accounts as needed and grant only the permissions that can meet application requirements and needs. For example, as long as the query function is available, a simple public account can be used to select.

3. Strengthen the logging of database logs.

Review "failed and successful" of database logon events, select "security" in instance properties, and select all audit levels. In this way, in the database system and operating system logs, the logon events of all accounts are recorded in detail.

Check SQL Server logs regularly to check for any suspicious logon events or use the DOS command.

The code is as follows:	Copy code
Findstr/C: "login" d: \ Microsoft SQL Server \ MSSQL \ LOG \*.*

4. Manage extended stored procedures

Perform major operations on the stored procedure, and exercise caution when calling the account to extend the stored procedure. In fact, most applications do not use much system stored procedures. SQL Server's multi-system stored procedures are only used to meet the needs of users. Therefore, delete unnecessary stored procedures, because the stored procedures of some systems can be easily exploited to escalate permissions or perform damages.

If you do not need to extend the stored procedure xp_mongoshell, remove it. Use this SQL statement:

The code is as follows:	Copy code
Use master Sp_dropextendedproc 'XP _ export shell'

Xp_mongoshell is the best way to enter the operating system and a large backdoor left by the database to the operating system. If you need this stored procedure, use this statement to restore it.

The code is as follows:	Copy code
Sp_addextendedproc 'XP _ external shell', 'sqlsql70. dll'

If you do not need to discard the OLE automatic stored procedure (which may cause some features in the manager to be unavailable), these procedures include:

The code is as follows:	Copy code
Sp_OACreate Sp_OADestroy Sp_OAGetErrorInfo Sp_OAGetProperty Sp_OAMethod Sp_OASetProperty Sp_OAStop

Remove unnecessary stored procedures for registry access. The registry stored procedures can even read the password of the operating system administrator as follows:

The code is as follows:	Copy code
Xp_regaddmultistring Xp_regdeletekey Xp_regdeletevalue Xp_regenumvalues Xp_regread Xp_regremovemultistring Xp_regwrite

There are some other extended stored procedures that you can best check.

When processing the stored procedure, check to avoid damage to the database or application.

5. Protocol encryption

SQL Server 2000 uses the Tabular Data Stream protocol for network Data exchange. Without encryption, all network transmission is in plain text, including passwords and database content, this is a very big security threat. Attackers can intercept what they need on the Internet, including database accounts and passwords. Therefore, when conditions permit, it is best to use SSL to encrypt the protocol. Of course, you need a certificate to support it.

6. Do not randomly detect your TCP/IP port

By default, SQL Server uses port 1433 for listening. Many people say that this port should be changed when SQL Server is set, so that others cannot easily know which port to use. It is a pity that the TCP/IP port used by SQL Server can be easily known through the UDP detection of port 1434 that Microsoft has not published.

However, Microsoft still considered this issue. After all, open and open ports will cause unnecessary troubles. Select the TCP/IP protocol attribute from the instance attributes. Select to hide the SQL Server instance. If the SQL Server instance is hidden, it is prohibited to respond to broadcasts from clients that attempt to enumerate existing SQL Server instances on the network. In this way, no one else can use 1434 to detect your TCP/IP Port (unless using Port Scan ).

7. Modify the port used by TCP/IP

Modify the default port 1433 based on the settings in the previous step. In instance properties, select the TCP/IP protocol attribute in network settings to change the default port used by TCP/IP to another port.

8. Reject detection from port 1434

Because there is no limit on port 1434 detection, some database information can be detected by others, and DOS attacks may increase the CPU load on the database server. Therefore, for Windows 2000 operating system, use IPSec to filter and reject UDP communication at port 1434 to hide your SQL Server as much as possible.

9. Restrict IP addresses for network connections

The SQL Server 2000 database system itself does not provide a security solution for network connections, but Windows system 2000 provides such a security mechanism. Using the operating system's own IPSec can achieve the security of IP packets. Restrict IP connections to ensure that only the IP address can be accessed, and deny port connections from other IP addresses to effectively control security threats on the network.