Follow-on from instant initialization privilege checking


I got bored on the first leg of the journey from Seattle to London so thought I’d bang out a quick blog post.


After my previous post on checking whether a SQL instance is able to use instant initialization (see here), I had a discussion with Scott R., who regularly comments on blog articles. He proposed an alternative method of checking whether the SQL service account has the Perform volume maintenance tasks privilege (AKA SeManageVolumePrivilege) (or other useful privileges like Lock pages in memorySeLockMemoryPrivilege) and outlined the pros and cons of the various methods. With his permission, I’ve turned the information he provided into this blog post.


To use whoami /priv to find the SQL service account privileges you need to enable xp_cmdshell to do it from within SQL, or be logged into the Windows box as the SQL service account. Scott suggested using an alternative tool called AccessChk, written by my friend Mark Russinovich (formerly of Sysinternals, now a Technical Fellow at Microsoft). Using it you can find the privileges assigned to other users, services, or processes. The example syntax to do this for a SQL instance would be:



AccessChk –p sqlservr.exe –f –q -v


This allows you to find the privileges of the SQL service account without having to enable xp_cmdshell or login as the service account itself.


Let’s compare the whoami + xp_cmdshell combination with the AccessChk + command window combination.


Whoami + xp_cmdshell


Advantages:



  • You don’t need access to a command window.
  • You don’t need an administrator-capable user account.
  • Whoami may already be installed on a given Windosw server.

Disadvantages:



  • You need to enable xp_cmdshell (for a brief period – and have the authority to do so) to run whoami, which is often a separate security issue in many IT organizations.
  • Whoami can’t be run from a separate command window, because it can’t get the privileges from any user account other than the one which is running the command.  Since the goal is to get the privileges of the SQL service account, and that service account is often locked down from unnecessary privileges (such as interactive logon, using command windows, etc.), whoami can’t easily be used to derive this information outside the context of running from SQL Server via xp_cmdshell.
  • Whoami may not already be installed on a given Windows server.  This could be a barrier to using it for this purpose in IT organizations with rigid program install guidelines.

AccessChk + command window


Advantages:



  • You don’t need to enable xp_cmdshell, bypassing this separate security issue.
  • You don’t need to use the SQL service account as AccessChk can report on privileges of other running processes (and their implied user account) or other explicit user accounts, without requiring the security context of the desired user account to run AccessChk.
  • This approach may be used to automate capture of such information for configuration management and reporting purposes.

Disadvantages:



  • You need access to a command window on the Windows server.
  • You may need an administrator-capable user account (which you may not have) – I am not sure of this requirement, but it may be the case.
  • AccessChk is most likely not already installed on a given Windows server.  This could be a barrier to using it for this purpose in IT organizations with rigid program install guidelines, just like for whoami.

Summary


So is there a recommendation here? No, just a quick examination of the trade-offs with the two methods. Once again, I recommend you grant this privilege to the SQL service account for the massive performance boost it gives with data file creation or growth operations.

7 thoughts on “Follow-on from instant initialization privilege checking

  1. Hi Abbie,

    In this case, instant initialization is disabled. The privilege state equals the featue enabled state, if you see what I mean.

    You need to manually enabled that privilege for the SQL Service Account using the Local Security Policy tool, and then restart the SQL instance.

    Thanks

  2. I read further and found the related article from Kimberly’s blog (also very interesting). I saw this statement on the Granting the permission "Perform Volume Maintenance Tasks" section which I hope you or Kim could clarify:

    This permission can be granted by an administrator through the Local Security Policy tool (Start, All Programs, Administrative Tools) and once granted, SQL Server automatically uses instant initialization.

    Does this mean that as long as the privilege is among the output returned whoami then you won’t need to care whether the state is disabled or not? Thanks in advance to you and Kimberly.

  3. Just came across your blog today and I find it very interesting.

    I wanted to check whether we have this capability enabled in our systems so I ran whoami /priv as the SQL Server service account on a test machine. SeManageVolumePrivilege was one of the privileges returned by the command. However, I noticed that the State is Disabled. Is instant initialization enabled in this case? Do I need to do a seperate step to change the state to enabled or will SQL Server automatically enable and disable it as needed?

    Thanks.

  4. Paul,

    Another way of verifying/granting privileges (even remotely!) is using good ol’ ntrights.exe from the ResKit. It’s freely available from MS downloads.

    ntrights.exe +r SeManageVolumePrivilege -u DOMAINuser -m \MACHINE
    ntrights.exe +r SeLockMemoryPrivilege -u DOMAINuser -m \MACHINE

    Good for scripting and touch a bunch of servers, or doing unattended installs.

    Rgds,

    Argenis

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.