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 security measures such as SQL Server TCP / IP ..., but many DBAs ignore the startup account of SQL Server service, which is also one Very worthwhile questions. Especially SQL 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 SQL Server to perform these extended stored procedures. The extended stored procedures provided by SQL Server allow you to call some internal function logic of the dynamic link library like T-SQL, and these extended stored procedures can 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 SQL Server service launch account. So if this SQL Server service startup account is the user of the Administrators group, we can make things that we want to do through these extended stored procedures: delete system information, destroy registry, and more. If we limit the permissions of the SQL Server service to start the account, even if "hackers" or malicious developers get the administrator privileges of the database, they will not have a lot of 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 for safer, we hope that the permissions of the SQL Server service startup account is, the better.
As a service of the system, the user account that launches SQL 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 started the 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 is unable to start. Serving, 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 / write permissions; l Start the right to serve the local service; l Read the registry authority;
3. Give the SQL Server user SQL directory read and write permissions; because my SQL Server is installed in the D disk, I am assigned D: / Progrm file / Microsoft SQL Server / MSSQL read and write permissions in 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 has logged into, open the Services management tool, and configure the user to start the SQL Server service;
This way we can control the SQL Server 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, SQL Server has provided such a tool to configure the startup account, you can configure it through the Enterprise Manager of SQL Server:
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:
