Chapter 1 Securing Your Server and Network (1): Select SQL Server Run account

Source: http://blog.csdn.net/dba_huangzj/article/details/37924127, Special catalogue: http://blog.csdn.net/dba_huangzj/article/ details/37906349

No person shall, without the consent of the author, be published in the form of "original" or used for commercial purposes, and I am not responsible for any legal liability.

Objective:

SQL Server is a Windows service that runs on a Windows operating system with a Windows user or system user right. Choosing the right account to run SQL Server is very important, and this series of articles focuses only on security aspects.

One reason to choose the right account is that if the permissions are inappropriate, users (clients) can use SQL Server to make unintended uses of Windows OS or other resources.

Realize:

The first time you select an account occurs during the installation process, but can be changed after installation, how to install SQL Server beyond the scope of this article, so skip the installation process and select the Account section. After the installation is complete, you can follow the steps below.

Implementation steps:

1. In the command line input: Services.msc Open Service Manager. Locate the SQL Server service,

2. Right-click the service, select "Properties" and view the current running account:

3. Open SQL Server Configuration Manager, find the SQL Server corresponding instance name option, I have two instances on the machine, one is 2008r2, one is named instance of 2012,2012, so select SQL Server (SQL2012) This service, and right-click Select " Properties,

4. After opening the Properties page, select the login page,

5. Select "Built-in Account", the drop-down box has three options, the following sections will describe these accounts:

6. When the account is modified, click the "OK" button, you will be prompted to restart the SQL Server service, click "Yes", and then restart the service, as the modified running account must restart the service, so if in a formal environment, need to be cautious, and planned to modify.

7. At this point, we demonstrated how to modify the running account for SQL Server. The following will introduce some principles and considerations.

Principle:

The SQL Server service inherits the permissions of the Windows account on the underlying operating system (that is, Windows OS). It does not necessarily require administrator privileges on the machine. Only the data file/transaction log file, the error log file, the directory where the backup file resides, and a small amount of system permissions are required.

If you modify the service account after you install SQL Server, it is strongly recommended that you use SQL Server Configuration Manager implementation instead of the Windows Service Control Manager, which does not perform well with permission controls.

In Windows Server R2, the virtual account is used by default during the installation of SQL Server (which is described in subsequent articles) as the startup account. If you selected "built-in account" in step 5, you do not need to provide a password, which is managed and preset by the operating system. Here's a brief look at the two types of accounts in step 5:

• Local System: This is a Windows system account that has administrator privileges on the computer that appears in the network (<Domain>\<Machine>) Form, if the machine exists in a domain environment, This type of account can be granted access to network resources.
• Network Service: This account has many native permissions restrictions relative to the local system, but can access network resources as well as the local system.

You can select a Windows or domain account that you have already created and run the account as a full name (<Domain>\<Account>), but make sure that the account is not affected by the password expiration policy on Windows. The entire SQL Server service may be stopped after the system has been running for a period of time because of a password expiration.

As a practice, it is recommended to use the actual Windows account instead of the built-in account, because the built-in account is shared by multiple services and the rights control is inferior to the actual Windows account. For example, an attacker could log on to SQL Server with administrator privileges and use external stored procedures such as xp_cmdshell for operating system-level attacks. Using an actual Windows account can reduce the chance of this happening.

More information:

To allow a Windows account to run a service (not all accounts can run the service), you need to grant "Log on as a service right" (Chinese as "trusted computers and user accounts can perform delegation") permissions, as follows:

1. On this computer, open the Management tool, and select "Local Security Policy", the Chinese is the "Win8", the system can control Panel → "System and security" to find "Log on as a service right" (Chinese as " Trusted computers and user accounts can perform delegation "):

2. Add the required account number,

If you are using the Windows Server Core version, because there is no GUI to modify, it is also possible that you cannot log in directly to the target server with GUI operation (non-core version), you can implement the configuration on some other machines:

Steps:

1. Open the Computer Manager (Compmgmt.msc) right-click root directory, select "Connect to another Computer", enter the server address,

After a successful connection becomes, note that "Computer Management (local)" has become "Computer Management (SQL-A)":

2. In the Services and Applications node, you can find the SQL Server Configuration Manager, and then you can make the configuration described earlier.

Create a domain user as a service account:

If you are in a domain environment, you can use the Active Directory Management Center (Active Directory server) on administrative The Active Directory Users and Computers (Active Directory user and Computers) tool adds users to the machine on the domain environment.

At the time of creation, the user option is only checked, except for special needs, it is not recommended to tick the "user next login must change password":

If you want the password time-out for your service account, we recommend that you use the Managed service account (managed service accounts) that appears in Windows Server 2008, which is described later in this article.

Extended reading:

Configure Windows service accounts and permissions (http://msdn.microsoft.com/zh-cn/library/ms143504.aspx)
Filed under: http://blog.csdn.net/dba_huangzj/article/details/37927319