How to establish a secure startup account for MSSQL server

server| Security
SQL Server security issues have always been a challenge for DBAs, as developers and users want their permissions to be as large as possible, preferably SA, and as a DBA, it's always a contradiction to expect all user rights to be as small as possible. In general, we will consider using Windows Authentication mode, establish secure user rights, and change the default port for SQL SERVER TCP/IP ... Security, but many DBAs ignore the startup account for SQL Server services, which is a very important issue to focus on. In particular, SQL Server provides a number of operating system and registry extended stored procedures, such as: xp_cmdshell, Xp_regdeletekey, Xp_regdeletevalue, and so on.

Let's review the steps that SQL Server performs with these extended stored procedures. The extended stored procedures provided by SQL Server enable you to call the internal function logic of some dynamic-link libraries like T-SQL, and these extended stored procedures can include most of the functionality of WIN32 and COM.



When the relational database engine determines that a Transact-SQL statement references an extended stored procedure:










The relational database engine passes extended stored procedure requests to the Open Data Services layer.

The Open Data service then mounts the DLL that contains the extended stored procedure function to the SQL Server 2000 address space (if it is not yet mounted).

Open Data Services pass requests to an extended stored procedure.

Open Data Services deliver the results of operations to the database engine.
















From the image above we can see clearly that SQL Server 2000 's database engine interacts through extended stored procedures and Windows resources. The key to an extended stored procedure to handle operating system tasks is to have a SID of its own identity, which comes from the SQL Server service startup account. So if this SQL Server service startup account is a user of the Administrators group, we can do whatever we want with these extended stored procedures: Delete System Information, destroy the registry, and so on. If we limit the permissions of the SQL Server service startup account, it will not have a significant impact on the operating system even if the "hacker" or the malicious developer has access to the database's administrator privileges. As long as there is a database backup we can easily restore the database, and do not reinstall the system. So in order to protect our system more securely, we want the SQL Server service to start the account lower the better.











As a service to the system, the user account that starts the SQL SERVER 2000 service also requires some necessary permissions, and we explain these permissions through a specific instance (this instance is only for member servers, if DC and Active Directory Active is started) Directory also requires additional configuration):











1. Create a local user, SQL Server, via local user management, password: 123456;

2. If we open the services configuration through that user now, the system will report an error:

Source:service control managerevent id:7000description:the%service% Service failed to start due to the following E service did not start due to a logon failure. No Data would be available.











This is because the service cannot be started as a normal user and we need to assign the necessary permissions to the SQL Server user.

The SQL Server service startup account must have 3 basic permissions:

L Read and write access to the local directory of the database;

• The right to start a local service;

L read the registry permissions;













3. Give SQL Server users the ability to read and write to a directory;

Because my SQL Server is installed in D disk, I assign D:\PROGRMAM file\microsoft SQL Server\MSSQL Read and write permissions to the server user in Rights management.

4. Assign SQL Server users permission to start the local service;

This is more complicated, I only cite the case as a member server.

• Start the local security Setting MMC snap-in.











Expand the local Policy, and then click User Rights Assignment.











L In the right pane, right-click the log on as Service, add the user to the policy, and click OK.











L In the right pane, right-click Log on as a batch job, add users to the policy, and click OK











L In the right pane, right-click Locks pages in memory, add users to the policy, and then click OK











L In the right pane, right-click the act as part of the operating systme, add the user to the policy, and then click OK











L In the right pane, right-click Bypass traverse checking, add the user to the policy, and then click OK











L In the right pane, right-click the Replace a process level token, add the user to the policy, and then click OK











L Close the Local security Setting MMC snap-in.











As shown in figure:



5. Reboot the system with SQL Server user login system;

6. Restart the system, the administrator user logged in, open the Services management tool, configure the user to start the SQL Server service;















This allows us to control the permissions of SQL Server extended stored procedures by restricting the permissions of the SQL Server user. SQL Server users now have write permissions only for D:\PROGRMAM file\microsoft SQL server\mssql directories, which reduces the risk of deleting system files through xp_cmdshell.













It's cumbersome to configure through acquisitions, and fortunately SQL Server has provided such tools to configure startup startup accounts, which you can configure with SQL Server's Enterprise Manager, as shown in the following figure:























This way, SQL Server Enterprise Manager automatically configures all the necessary requirements for you. Includes access rights to the directory, permissions to start the service, access to the registry, and so on. So our correct configuration order is:











1. Establish users;











2. Configure the user to start in SQL Server Enterprise Manager;











3. Assigning other appropriate permissions (if a copy operation is required);











Note:











The increase in the service startup account through SQL Server Enterprise Manager will add a lot of information to the registry, even if you change the user will not be deleted, so changing the service startup account do not frequent replacement, which will increase the capacity of registry. Also note that only users who belong to the sysadmin role can configure the startup account of the SQL Server service.











Summarize:











Building a secure and efficient SQL Server is multifaceted, and understanding the running mechanism of SQL Server is the foundation. We need to consider not only the security of database users, but also the security of SQL Server services.