How SQL Server uses Windows Virtual Accounts and local groups

Managed Service Accounts (MSAs) and Virtual Accounts were introduced in Windows 7 / Windows 2008R2 (link). Installations of SQL Server 2012 and newer will utilize the Virtual Accounts. SQL Server 2008 / 2008R2 installations use a combination of the local groups and Virtual Accounts, and SQL Server 2005 installations use just local groups.

On my Windows 7 laptop, I have SQL Server versions 2005-2014 installed. Looking at Computer Management for the groups, we can see the groups that were created by the various SQL Server installations:

If we drill in to one of these groups, we can see that the SQL Server service account for the database engine is assigned to this group:

Just to prove that this is the SQL Server service account:

SQL Server will use these groups in many places so that permissions are granted to the group, instead of the actual service account. This simplifies things greatly if you change the service account – SQL Server Configuration Manager will just change the member of this group instead of having to hunt down and change everywhere that it knows that permissions are needed for the service account. Using these groups instead of the service account will simplify your life also if you ever change the service account – all those specific permissions that you granted on local resources (paths, registry, etc.) would have to be changed. Using the group, it will still have the same permissions.

When installing on Windows 7 / 2008R2, Virtual Accounts are used instead. The virtual account used is “NT SERVICE\”. For a default SQL Server instance, the name will be “NT SERVICE\MSSQLSERVER”, and for a named SQL Server instance it will be “NT SERVICE\MSSQL$”. Essentially, this Virtual Account takes the place of a group. One of the places where this is used is in Local Security Policies. Here is a screen shot from one of those:

Notice that this policy has both Virtual Accounts and local groups assigned to it, depending on the version of SQL.

Does this work?

I have had people insist that this only works when using a local windows account, and that it doesn’t work with a domain account. So, let’s test it out.

Previously, I had created a lab environment with a network for testing out Availability Groups. I installed an instance of SQL Server 2016 using a domain account for the service account:

The SQL Server 2016 installation specified to set the “Perform Volume Maintenance Tasks” policy. The following screen shot shows that is set to use “NT SERVICE\MSSQLSERVER”:

When the SQL Server error log is examined, we can see that Instant File Initialization (IFI) is enabled. This can only be enabled if the service is running with the “Perform Volume Maintenance Task” security privilege assigned to the service:

Just to prove that IFI is actually working, let’s build a database using the example shown at https://blogs.msdn.microsoft.com/sql_pfe_blog/2009/12/22/how-and-why-to-enable-instant-file-initialization/:

DROP DATABASE IF EXISTS TestFileZero;
GO
DBCC TRACEON(3004,3605,-1);
GO
CREATE DATABASE TestFileZero;
GO
EXEC sp_readerrorlog;
GO
DROP DATABASE TestFileZero;
GO
DBCC TRACEOFF(3004,3605,-1); 
GO

Running these statements produces the following results from the error log:

Here we can see that only the log file was zeroed out – the data file wasn’t. Proving that IFI is actually working, and that the virtual account properly utilizes the “Perform Volume Maintenance Tasks” privilege for the service account for use by SQL Server.

Summary

In summary, the Virtual Account is essentially an automatic group for the service. It can be used on the local machine, even if the service account is a domain account. It can simplify things if you ever need to change the service account. You should use it.

Note that if you need to grant the service account permissions outside of the local server (network file share, another server, etc.) then you will still need to use the domain service account.

The post Windows Virtual Accounts and SQL Server appeared first on Wayne Sheffield.