Thursday, July 5, 2018

About SQL Server Engine & Agent Service Accounts


Configure Service Accounts for all SQL Server services.

Microsoft recommends creating individual accounts for all services. If you use the same service account for every server, then when somebody repeatedly mistypes the service account password during an installation, you can lock out the service account, causing widespread havoc.

Do not make the service account a local administrator. As a best practices SQL Server service should be using a minimally privileged account. We should always avoid running SQL Server services under the contexts of the local system, local administrator or domain administrator accounts.

If you're installing multiple components, the recommendation is to use separate accounts. Example you have two services to be concerned with: the main SQL Server service and the SQL Server Agent service. Even in this case it's recommended to use separate accounts.
We need to make sure that the SQL Server service account has “full control” permissions on data, log and backup directories for read and write activities so need to grant the ‘Perform Volume Maintenance Tasks’ right to the account that will be used for the SQL Server service.

We need to use SQL Server Configuration manager to do any modifications for SQL Services. Account change and Password change both should be done only via configuration manager not services.msc.  If we update/change service account details through SQL Server configuration manager, it will perform additional configurations, such as  updating the Windows local security store which protects the service master key for the Database Engine etc.

Windows Domain Account Permissions

SQL DATABASE Engine Service account should have below permissions
·         Log on as a serive permissions
·         Replace a process-level token
·         Bypass traverse checking
·         Permission to start SQL Writer
·         Permission to read the Event Log service
·         Permission to read the Remote Procedure Call service

SQL AGENT Service account should have below permissions
·         Log on as a serive permissions
·         Replace a process-level token
·         Bypass traverse checking
·         Adjust memory quotas for a process