Which account is the right one? Set up and check Instant File Initialization on Microsoft SQL Server correctly

Since version 2016 of SQL Server, the “Perform Volume Maintenance Tasks” right can be assigned during the installation of SQL Server in order to activate the “Instant File Initialization” functionality. But which account receives the right?

We took a closer look at the topic in a customer project and share the results here. So let's start from the beginning …

In most cases, using the local virtual service account “NT SERVICE\<service name>” (following: service account) is the best option. However, if high availability functions such as Always On are to be used, it is necessary to use an Active Directory account (following: AD account). Our recommendation: In these cases, use group-managed service accounts. Further information can be found in the Microsoft documentation and our blog article “I don't want to know the password - using group Managed Service Accounts for Microsoft SQL Server”.

Installation with an AD account

However, the customer's initial situation is as follows. SQL Server version 2022 was installed with a group-managed service account. During the installation, the “Perform Volume Maintenance Tasks” right was granted directly.

After the successful installation, the SQL Server log reported that the “Instant File Initialization” functionality is activated. We therefore assumed that the AD account used had been granted the “Perform Volume Maintenance Tasks” right. However, a check revealed that the right was assigned to the service account instead.

The concept is good

This illustrates the Microsoft concept. Each service always has an associated service account in the system. This service account can be assigned rights that the program started by the service should receive. In our case, the SQL Server therefore always has the “Perform Volume Maintenance Tasks” right. This means that the account used for starting can be changed without requiring changes to the authorizations.

The Configuration Manager view

But what do we now see in the interface provided by Microsoft for managing the service? In our case, the Configuration Manager shows a clear “No”, so the SQL server supposedly does not have this right at all.

Why? Because the Configuration Manager probably uses the name of the account used for startup, in our case the AD account, for verification. If we now make a “correction” here by selecting “Yes”, then the AD account used for startup is additionally authorized. This is not necessary, but at least the Configuration Manager is now telling the truth.

In order to get to the bottom of the matter, we took a look at the documentation and looked at other tools that we also use at the customer.

What does the documentation say?

The documentation refers to the “SQL Server service start account” or “the account that runs the SQL Server service”. We read this as if the AD account is meant. This corresponds to the behavior of the Configuration Manager, but not the behavior of the “setup.exe” installation program. We currently assume that the installation program implements the behavior desired by Microsoft. In addition to the Configuration Manager, the documentation would also have to be adapted.

[Update 18.06.2024: We have proposed a change to the documentation, which is publicly available here. We hope that this will provide us with feedback from Microsoft on this topic.]

[Update 26.07.2024: The change we suggested to the documentation has now been adopted - at least in the English version. The text now reads "add the SQL Server service account"].

dbatools

[Update 29.05.2024: The following section has been adapted as our code changes have now been adopted].

With version 2.1.16, the Install-DbaInstance command of the PowerShell module dbatools has been adapted. The parameter "PerformVolumeMaintenanceTasks" used for configuration has been given the two aliases "InstantFileInitialization" and "IFI". On the other hand, the "SQLSVCINSTANTFILEINIT" parameter of setup.exe is now used directly when installing an SQL Server (from version 2016) - just like in the interactive installation.
The Set-DbaPrivilege command, which can be used to subsequently set the "Perform Volume Maintenance Tasks" right, currently still works like the Configuration Manager and authorizes the start account of the service, in our case the AD account.

We will shortly be proposing a further change to the code in order to adapt the behavior of Set-DbaPrivilege.

First Responder Kit

The procedure sp_Blitz from the First Responder Kit by Brent Ozar uses the query “select instant_file_initialization_enabled from sys.dm_server_services” for checking. This queries the SQL server directly and therefore always returns the correct result.

Conclusion

Basically, every service has a service account, regardless of which account is used to start the service. Even if the service account is used to start the service in many cases, there are sometimes reasons to use an AD account, especially with SQL Server. If an alternative account is used, the “Instant File Initialization” still works, as the service account is authorized. A general realization should be that not every GUI, in this case the Configuration Manager, should be trusted directly. Have you ever found a display not quite right?