SQL Server security issues have always been a problem that plagues DBA. As developers and users want their permissions, the better, preferably SA, and as DBA I hope all user privileges, this is always a pair of contradictions . In general, we will consider using Windows verification mode, establish secure user rights, change SQL Server TCP / IP's default port ... and other security measures, but many DBA still ignore the startup account of the MSSQL Server service, which is also one Very worthwhile questions. In particular, MSSQL Server provides many operating systems and registry extended stored procedures, such as xp_cmdshell, xp_regdeleteKey, XP_regDeletevalue, and more.
Let's take a look at the steps of MSSQL Server to perform these extended stored procedures. The Extended stored procedures provided by MS SQL Server allow you to call some internal function logic of the dynamic link library like T-SQL, and these extended stored procedures may include most of WIN32 and COM.
When the relational database engine determines that the Transact-SQL statement references the expansion stored procedure:
The relational database engine passes the extended stored procedure request to the open data service layer.
The Open Data Service will contain the DLL of the extended stored procedure function to the SQL Server 2000 address space (if not loaded).
Open data services are passed to the extended stored procedure.
Open data service passes the operational result to the database engine.
From the above figure we can clearly see the SQL Server 2000 database engine interacts by extending stored procedures and Windows Resources. The key to extending the stored procedure can complete the processing of the operating system task is to have a self, this SID is from the MSSQL Server service launch account. So if this MSSQL Server service startup account is the user of the Administrators group, we can make things to do through these extended stored procedures: deleting system information, destroying registry, and more. If we limit the permissions of the MSSQL Server service to start the account, even if "hackers" or malicious developers get the administrator privilege of the database, it will not have a big impact on the operating system. As long as there is a database backup we can quickly recover the database instead of reinstall the system. So in order to protect our system more secure, we hope that the permissions of the MSSQL Server service startup account is, the better.
As a service of the system, the user account that starts the MSSQL Server 2000 service also requires some necessary permissions. Let's explain these privileges through a specific instance (this instance only for member servers, if it is DC and launched an active directory ACTIVE Directory also requires other configurations):
1. Create a local user SQLServer, password: 123456 by local user management
2. If we open the Services configuration now, the system will report an error:
Source: Service Control ManageRevent ID: 7000Description: The% Service% Service Failed to Start Due to the Following Error: The Service Did Not Start Due to a logon failure.no data will be available.
This is because as a normal user cannot start the service, we need to assign the necessary permissions to SQLServer users.
SQL Server service startup account must have 3 basic permissions:
l Database local directory read and write permission;
l Start the authority of the local service;
l Read the permissions of the registry; 3. Give the read and write permissions to the SQLServer user MSSQL directory;
Because my SQL Server is installed in the D disk, I give the D: / Progrm File / Microsoft SQL Server / MSSQL read and write permissions to the SQL Server user in the permission management.
4. Assign the SQLServer user to start the right to serve the local service;
This is more complicated, I only exemplifies the situation as a member server.
l Start the "Local Security Setting" MMC management unit.
l Expand Local Policy and click User Rights Assignment.
l In the right pane, right-click the Log ON AS Service to add the user to the policy, and then click OK.
l In the right pane, right-click the log on as a batch job, add the user to the policy, then click OK
l In the right pane, right-click Locks Pages in Memory to add the user to the policy, then click OK
l In the right pane, right-click ACT AS Part of The Operating Systme, add the user to the policy, then click OK
l In the right pane, right-click ByPass Traverse Checking, add the user to the policy, then click OK
l In the right pane, right-click Replace A Process Level token, add the user to the policy, then click OK
l Close the "Local Security Setting" MMC management unit.
Figure:
5. Restart the system and log in to the system with SQLServer users;
6. Restart the system, the Administrator user will log in, open the Services management tool, and configure the user to start the MSSQLServer service;
This way we can control the SQLServer extension stored procedures by limiting the permissions of SQL Server users. Now SQL Server users have written permissions to D: / Progrm file / Microsoft SQL Server / MSSQL directory, which reduces the risk of deleting system files through xp_cmdshell.
The configuration is more cumbersome by the acquisition. Fortunately, MS SQLServer has provided such a tool to configure the startup account, you can configure it through the Enterprise Manager of SQLServer, in 00:
Such SQL Server Enterprise Manager will automatically help you configure all necessary conditions. Including access to the directory, the permissions of the launch service, access to the registry, etc. So our correct configuration order is:
1. Establish a user;
2. Configure the user started in the SQL Server Enterprise Manager;
3. Assign other respective permissions (if you need a copy operation);
Remarks:
The service started with the SQL Server Enterprise Manager will add a lot of information in Registry, even if you change the user, you will not delete it, so do not change frequently in changing the service startup account, which will increase the capacity of Registry. At the same time, pay attention to that only users who belong to the sysadmin role can configure the startup account for the SQL Server service. to sum up:
Building a safe and efficient SQL Server is multifaceted, and understanding the operation mechanism of SQL Server is based. We must not only consider the security of database users, but also consider the security of SQLServer services.
