Local Windows Groups for Service Accounts (almost) gone in SQL Server 2012

When I installed CTP3 of SQL Server 2012 (on Windows Server 2008 R2 OS), I noticed that the "Service SID account" (known as the Managed Service Account) was directly available in the setup dropdown box, selected it, wrote a blog entry mentioning it, and went on. Lately, I've been looking at the local Windows groups (or lack of them).

Remember when, starting in SQL Server 2005, SQL Server would create local Windows Groups for service account and plunk the appropriate user into the group? And you assign permissions to the group? Well, in SQL Server 2012, it (almost) never does that any more.

I've done a few sample setups of various services to check, and the only local Windows Groups for service account that are even created are the Windows Groups for SQL Browser service and Analysis Services. No other services (and I've installed Database Engine, FDHost, Reporting Services, VSS Writer) use the "local Windows group created at startup" any more. And, thanks to the fact that SQL Server 2012 only runs on OSes where Service SIDs are allowed, the SSAS and Browser groups contain only the Service SID.

As far as I can see (I've not tried every configuration possible) when running on OSes where Service SIDs are available (e.g. Windows Server 2008 SP2/Windows Vista SP2 and above), SQL Server has used (enabled) them and plunked them into the Windows Group rather than using the group membership of the service account user you select at startup. It's only in Windows Server 2008 R2 and Windows7 w/SQL Server 2012 that Managed Service Accounts can be specified as the service account in SQL Server. And SQL Server 2012 can use Virtual Accounts as service accounts too. This is all doc'd here in the SQL Server 2012 Books Online.

And, we're almost rid of those local groups. Check here for the well-known service SIDs to assign permissions to.


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.