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 //blogs.msdn.microsoft.com/sql_pfe_blog/2009/12/22/how-and-why-to-enable-instant-file-initialization/:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
In the past, I was told IFI can’t be done on log files as if the log file was created using the same blocks on disk as an older, deleted, log file, it could potentially cause issues and therefore every block had to be zeroed out forcing writes to the whole log file at creation and every growth.
In your example, the create database statement will use the model database which has a very tiny 3 or 8 MB log file which may take less than 1 second to write to. I wonder if you created a database with 10 GB data file and 3 MB log file and compare the time to a 3 MB data file and 10 GB log file.
If IFI was true for both data and log files, the db will be create in the same amount of time.
See the Redgate post for other ways to check if IFI is turned on
//www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/instant-file-initialization/
SELECT servicename, instant_file_initialization_enabled
FROM sys.dm_server_services
WHERE servicename like ‘SQL Server (%’;
Reply
Hi Tim,
Thanks for your comment.
First, IFI is not used at all when creating log files.
However, starting with SQL Server 2022, instant file initialization can benefit transaction log growth events up to 64 MB. The default auto growth size increment for new databases is 64 MB. Transaction log file autogrowth events larger than 64 MB cannot benefit from instant file initialization. Prior to SQL 2022, growth events would not be able to use IFI on the log files either.
Note that your query for identifying if IFI is enabled works for starting with SQL 2012. There’s always older, non-supported versions out there, and examining the SQL Server log is how it needs to be done for those versions.
Reply