Perform volume maintenance tasks security policy

Introduction

You may see it more commonly referenced as Database Instance File Initialization (DIFI). If you are not familiar with the file initialization, this is the process SQL Server has to go through when it is creating the data files for a given database, and also during an expansion event (either manually or from auto growth) for a database. It only pertains to the data file(s) of the database, as log files are not affected by this security policy. SQL Server will “zero out” the file, basically fill it up with a bunch of zeros to allocate the amount of space requested. If you are a new DBA, this configuration actually goes all the way back to SQL Server 2005.

Now, one thing to note is that the local Administrator group on a given Windows Server automatically gets this permission. So as you should know, or be told now, you should never give your SQL Server service account local Administrator permission, it is not a good security posture for your environment. If your organization has any security standards to follow (HIPPA, SOX, PCI, etc.) it is an auditing point to verify that your SQL Server services do not have full administrator privileges on the local server.

SQL Server service account

I am not going to go into detail of what type of service account you should use with SQL Server. Suffice to say, it is not a good thing to be running as the Local System account. In the best practice of the security world, I would suggest you use one of the following supported options:

Domain User Account [Best option for domain environments]
Local User Account
Managed Service Account
Virtual Account [default on Server 2008 R2+]

If you are working on Window Server 2008 the default service account during SQL Server installation will be the Network Service or Local System, depending on the particular service. If you are on Window Server 2008 R2 or higher the default is the Virtual Account. The Virtual Account takes naming format of “NT SERVICE\MSSQLSERVER” on a default instance. If you are on a new OS version, this is perfectly fine, and a secure method to use for the service account.

One special thing that you might not be aware of either, is that DIFI also effects your SQL Server Analysis Services (AS) databases. AS will zero out the file as well for those databases. So you should remember to add that service account into this setup as well if you have that in your environment.

Problem signs

There are actually a few messages you will find on your SQL Server instance(s) that will show DIFI could help or be needed. The message IDs 5144 and 5145 can be found in your SQL Server error logs when DIFI is not enabled on an instance. Now that does not mean DIFI will prevent this message from occurring at all, if you have auto growth set to an outrageous amount that your storage cannot keep up with, that is something different. You can review KB 2091024 to see the messages and information about the solution.

Verify first

Before you go hollering at your administrator(s) about needing this permission, you can first verify that your SQL Server service account (or AS service account) actually has it or not.

Get the service account name for a SQL Server instance, which the PowerShell way is:
gwmi win32_service | where {$_.DisplayName -match “SQL”} | select name, startname
Another option is to type “gpedit.msc” into a run prompt
Go to the following path:
Find the “Perform Volume Maintenance Tasks” in the list

Which you can see on my server the only group that shows up is “Administrators”, so my SQL Server and AS service account do not have the permission currently.

Domain environment setup

The most common environment I see is an Active Directory (AD) Domain configuration. I am going to walk through how I would set this up and hopefully show that it will save you time, and ensure any SQL Server machine you put on the domain gets this permission. The key to this time saving process is use of: Group Policies. They make the life of a system administrator or AD administrator so much easier when it comes to handing out permissions for a wide number of servers.

AD management

Just to explain one small thing when it comes to AD management. Just like you may order your files on your computer in folders, objects in Active Directory can be more easily managed in Organizational Units (OU). An example of my lab domain that I have setup:

In my lab environment I created an OU called “DBA_Servers”. If you are building a new environment, or migrating a new environment, it can be useful to request your AD admin to move all of your servers to a dedicated OU. This provides two main things for your administrators:

I have supported environments where a domain policy was implemented at the domain level (at that “lab (local)” level) that completely broke a customer’s SQL Server environment. Having a dedicated OU for the servers your servers can be excluded from a particular policy being applied.
If you need a policy created for your SQL Server environment, such as applying DIFI, it does not require a bunch of hurdles for your AD administrators.

Overall, if this level of planning is done at the start of building an environment it can make things easier in the long run. As well, making things easier for those other teams that you have to work with at times (e.g. AD admins, SAN admins, etc.).

Domain group

After I have that OU created, and all the SQL Server computer objects moved to it, I need to create a custom Domain Group for my SQL Server service account(s). I called mine “Database_IFI” and then added my SQL Server and the AS service account:

Create the group policy

Now I need to create the policy. When working with domain policies I have to utilize the Group Policy Management Console, but not the one I used to verify the permission (gpedit.msc), I need the “gpmmc.msc”. This console is available via the Window Server Remote Management pack that I have installed on a Windows 10 client within my lab domain.

When the GPM is opened this is the view I am given:

I drill down into the Domains > lab.local and I am provided a list of the OUs in my AD setup along with a few other objects that are of no concern to me right now:

If I select the “DBA_Servers” folder, I can see that I already have one policy configured and will add another one to the list:

I need to right-click on the “DBA_Servers” folder and select “Create a GPO in this domain, and Link it here…”

I give the GPO a good name like “Database IFI Permission – SQL Servers”. One thing about naming is it should match up to what policy it will apply. You can create a GPO and apply a dozen different permissions under one GPO, but in AD management that can be hard to manage. It is much easier to just create the GPOs with a 1:1 ratio to their permission being applied; one GPO has one permission policy configured.

Once that GPO is named I will see it added to the list under the “DBA_Servers” folder. I right-click on that GPO and select “Edit…”, which presents me with this window:

I follow a similar path as I did when I verified the setting:

Open up the properties for the “Perform volume maintenance tasks” and just do the following steps:

Check the box for “Define these policy settings”
Click on “Add User or Group…”
Type in the following: Administrators;Database_IFI
Click OK twice.

One thing to remember is that whatever change is being made in a GPO, it can have an adverse effect on any member server the policy is applied on. I know that the local Administrators group already has access, and it has this for a reason so it needs to keep it. If I do not add the Administrators group to this policy, when it is applied to the servers in the OU it will be removed. So adding it here ensures it keeps it.

Now verify again

Now, I should be able to go back to my server and see that it has permissions. I go back to the server and check “gpedit.msc” and even try “gpresult /R” at a PowerShell prompt. I don’t see the policy applied to my server:

This is actually common because the Group Policy for a domain member server is not necessary applied instantly. It is guaranteed to be applied upon restart, but that is not something I want to do in a production environment. In these situations, where I know I want the policy to apply, I can force the server to reconcile the policies to the domain controller. I just run the following command: “gpupdate /force” from a PowerShell prompt:

If I check “gpresult /R” results, I can see the policy is now being applied:

Summary

If you work out with your AD administrators to get this setup, it will allow you to ensure this security permission is applied to your SQL Server environment in the most efficient manner. This makes it easier for all teams involved and just takes a little planning, and maybe taking your administrator to lunch one day. When you get this all setup, any server you build just needs to be moved into the OU and automagically all the permissions you need can be applied. The command I used previously, “gpresult /R”, can be utilized to verify the administrators put your server in the right OU.

Author
Recent Posts

Shawn Melton

Shawn Melton is a SQL Server consultant at Pythian, a global IT services company based out of Ottawa - Canada.

After spending 6 years in the system and network administration world he found he enjoyed working and learning SQL Server. Since 2010 he has been involved in SQL Server.

He is passionate about PowerShell and automation around SQL Server and Windows. His experience is focused in SQL Server administration and some BI development (SSIS, SSRS).

View all posts by Shawn Melton