SQL Server Service Account Permissions and Security Policy Setup

SQL Server Service Account

On some occasions where the SQL Server Service Account has been modified after the initial installation to use a different account for starting up the SQL Services, users will experience slowness and poor performance within the ProjectDox application due to insufficient permissions and security policy settings being granted to the new SQL Service Account.  Please modify the account's permissions as indicated below.

Windows security policy and permissions

The account assigned to start SQL service needs the Start, stop and pause permission for the service. The SQL Server Setup program automatically assigns this.

SQL Setup account adds the SQL service account as part of below security policies:

• Log on as a service
• Replace a process-level token
• Bypass traverse checking
• Adjust memory quotas for a process
• Permission to start SQL Writer
• Permission to read the Event Log service
• Permission to read the Remote Procedure Call service

If SQL Service account need to be changed, always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings. To change Reporting Services options, use the Reporting Services Configuration Tool.

SQL Server Setup does not open ports in the Windows firewall. Connections from other computers may not be possible until the Database Engine is configured to listen on a TCP port, and the appropriate port is opened for connections in the Windows firewall. To access an instance of the SQL Server through a firewall, configure appropriate inbound and outbound firewall rules on the SQL Server machine.

Lock Pages In memory

Lock pages in memory privilege to SQL is recommended in Windows Server 2008 or above only when there are signs of paging. Locking pages in memory may boost performance when paging memory to disk is expected.

To enable the lock pages in memory option, add the SQL Service account to "Lock pages in memory" security policy and restart the SQL database engine service.

Before enabling LPIM for SQL server, review the Performance monitor data for other applications running on the server and check if check working set memory of SQL is trimmed and accordingly setup LPIM.

 

File System Permissions Granted to SQL Server service SID's or Local Windows Groups and registry permissions are documented here: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions