SQL Server 2016 has added a couple of nice new options to the setup experience. First they added the ability to have multiple tempdb files on install, a nice time saver for later. And now, with CTP 3.0 they have added the ability to enable IFI on install.

What is IFI, and how do I get it?

IFI stands for instant file initialization, and if you are not aware, enabling this allows SQL Server to grow data files almost instantaneously. Without this enabled the data file space has to be claimed and then filled with zeroes, something that is a long and arduous tasks, especially on slower spinning media type storage.

This is only relevant to data files, for security and integrity reasons the log files still need to be zeroed out.

Enabling IFI is actually quite a simple task, you just need to add the SQL service account to the Perform Volume Maintenance Tasks section of the Local Security Policy and then restart the SQL Server service.

What does SQL 2016 do differently?

Prior to SQL Server 2016 (CTP 3.0) you would need to manually add the SQL account to the Perform Volume Maintenance Tasks (PVMT) section of the Local Security Policy (secpol). Now you can have the installer take care of this for you. That really helps with not forgetting to do it later, which can cause some serious performance problems down the road.

If you are doing a command line install you can add the /SQLSVCINSTANTFILEINIT argument or check the box in the GUI.

Installing SQL 2016 with IFI enabled

Here we’re just going to take a quick look at the installation option, cover the places where we can validate that IFI was enabled, and also look at, what I consider to be, a security risk.

Installation

Very simply, all we need to do is check the following box;

That’s it. It is nothing more complicated than that.

Validating

There are a couple of ways that we can validate that IFI is in place. Firstly, open up secpol and check that the service account is listed in PVMT:

One thing you’ll see here is that, instead of granting the permission directly to the installation service account it is using the virtual account name. This is an interesting added level of security to the permission, meaning that someone logging on to the server with the service account would not get that right (unless the account were also an admin, which it really shouldn’t be).

Now that we know the secpol setting is good we can validate that IFI is functioning correctly inside of SQL.

To do this we enable a couple of trace flags (do not do this on a production system):

• 3004 – shows information around file creation and backups
• 3605 – redirects the output to the SQL Server error log

With the flags enabled we can then either create a database, or increase the size of a data and log file. In this example it’s a quick db create (after all, local dev system)

Then we can open up the error log and look at the output

In the log you can see where the trace flags were enabled and disabled. Between those times there are a couple of lines marking that zeroing was started and completed on the log file, but nothing on the data file. This is because no zeroing was needed thanks to SQL being able to quickly just allocate the space required.

At this point we can have full confidence that SQL has IFI enabled and we are good to go.

How do Group Policy settings change things?

One of my biggest curiosities when this was announced was how this would work with Group Policy Objects (GPO). GPO’s are a way for your domain administrator to set many things at the domain, organizational unit (OU), or even server level. These settings can (and frequently do) include setting, and preventing changes to certain secpol settings. Restricting access to PVMT is one of the more common GPO uses, so I wanted to test this and see if it would cause the SQL install to fail, or just throw an error on completion.

Setting a GPO

I created a new OU and threw a server in it, ready for testing the SQL 2016 install. Then I created a new GPO which was set to limit the PVMT setting to the BUILTINAdministrators group only. This would mean that no other users would be able to be added through secpol (I also restricted the ability for anyone other than enterprise admins to be able to make the change).

I ensured that the policy had been correctly applied on the server in question by logging on and attempting to change the PVMT setting:

As you can see here the Administrators group has permissions, but there are no options for me to make any further changes. This is locked down by the GPO.

Comfortable in the knowledge that the installer would not be able to grant the rights I was interested to see whether I would get a hard or soft failure of the installer. Much to my surprise I got neither…

Reviewing the SQL install log

When you install SQL Server a detailed log of the install steps is written to the filesystem. You can find it at

%programfiles%Microsoft SQL ServerxxxSetup BootstrapLog<YYYYMMDD_HHMM>Detail.txt

Where

• %programfiles% is the location of your Program Files directory (typically C:Program Files)
• xxx is the version of SQL Server (130 for SQL 2016)
• <YYYYMMDD-HHMM> is the year, month, day, hour, minute of the install operation

I then dug through the log to try and find where this permission would be set. Other than the friendly name the PVMT secpol is also known as SeManageVolumePrivilege. I searched for this and was able to find it, along with several other secpol items that are set as a part of the install.

There were no errors or warnings in the log stating that there were any problems with the PVMT config. So I decided to go back to secpol and check the status there.

Ignoring GPO

Much to my surprise the virtual SQL account showed up in the PVMT secpol setting. I had no idea how it got there. Reviewing the setting I was able to confirm that the account I used for install was not able to make any adjustments and yet somehow the permissions were set.

To check and see if the permissions were active I did another test with setting the trace flags and creating a database, and it did show that IFI was enabled.

Refreshing Group Policy

Wondering if this was a temporary thing, rather an a total override of the GPO I ran gpupdate /force to enforce a refresh. When I did this the SQL virtual account was removed from the policy. So at least this would not be a permanent change.

A security concern?

To me this was very worrying. If some process were able to ignore GPO settings and somehow add itself to a secpol despite it being locked down there’s something to be worried about here.

I am not an expert in GPO by any means, but so far as I could tell everything was configured in such a way that there wasn’t any ability by my account to be able to get the permission set.

If anyone knows of how this could happen, or what I might have done incorrectly in the GPO please let me know.

 

TL;DR

You can now enable IFI on install in SQL Server 2016, which is a great thing, but there is a worry about security that comes along with it.