How do I assign and enforce a service account for the SQL Server Services with Active Directory Group Policy?

The answer to this question requires that we step outside the DBA comfort zone since it requires the use of Active Directory and Group Policy Objects.  There are many different ways that Active Directory can impact your SQL Servers, and GPOs certainly have the largest surface area.  The impact can be good or bad and using GPOs to assign service accounts to our SQL Services can help ensure consistency and standards in our enterprise.

Prior to Microsoft coming out with Group Policy Preferences (we’ll come back to that) we didn’t have much control over system services with GPOs.  In fact the only things you could change were the startup type and edit the permissions.  I only mention this because these settings still exist and if they are configured will override anything set in Group Policy Preferences.  You can find this setting by running GPMC.msc and looking under Computer Configuration…Policies…Windows Settings…Security Settings…System Services.  The following screen shot shows the options available when you select and open the SQL Server service.

GPO SQL Server Service Settings

Sometimes we need some history especially if it does things like override stuff, but now we can get on with the answer to our question.  If we go back to GPMC.msc and navigate to Computer Configuration…Preferences…Control Panel Settings…Services we can find and configure our services.  We can add settings for any service we desire so if you need to configure not only the SQL Server service but also SSRS, SSIS, SSAS, SQL Agent, and SQL Browser you can.

Simply click in the empty space and select New…Service.  We now get a box where we can set the startup mode, select what service we want, and define an account for it to run under.  In the Service Name selection we can type in the name of a service or click the elipsis and select it.  If you type it in, be absolutely sure you use the service name and not the friendly name.  The friendly name is what you see in the list of Services.msc on your machine, but you have to look on the general tab of the properties of the service to get the real name.  You’ll be less error prone by hitting that elipsis and selecting from the list, but you might not find the service you need in that list.  If you don’t it’s because that service is not installed on that machine.  You will have to run GPMC.msc from a machine that has the desired service installed.

Here is what mine looks like and once you’re done you just have to apply the new policy to your machine.

GPO Preference Service Settings