4 Minuten Lesezeit (846 Worte)

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.

[Fig. 1]: We have recreated the situation at the customer's premises in our laboratory. Here are the settings during installation.

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.

[Fig. 2]: A check with "secpol.msc" shows: The right is assigned to the service account.

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.

[Fig. 3]: The Configuration Manager shows that the "Instant File Initialization" functionality is supposedly not active, i.e. the "Perform Volume Maintenance Tasks" right has not been assigned 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.

dbatools

Unfortunately, the current version of the PowerShell module dbatools also works like the Configuration Manager. During installation, the “Perform Volume Maintenance Tasks” right is assigned to the account used to start the service. In our case, the AD account would therefore be authorized instead of the service account. This does not have any negative effects, but if the account used to start the service is changed, the new account would also have to be authorized again.

We will shortly be proposing a corresponding change to the code in order to adapt the behavior to the installation program.

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. When using dbatools, everything initially works as desired, but we believe it makes sense to adapt the code. 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?

Senior Consultant bei ORDIX.

 

Kommentare

Derzeit gibt es keine Kommentare. Schreibe den ersten Kommentar!
Montag, 27. Mai 2024

Sicherheitscode (Captcha)

×
Informiert bleiben!

Bei Updates im Blog, informieren wir per E-Mail.

Weitere Artikel in der Kategorie