First published on MSDN on Dec 12, 2012
A colleague had the following question not too long ago
Question : Does SQL Server check for “OS permissions” such as Locked Pages In Memory (LPIM) and if so does it use the Windows “macro-style” constants for them – such as SE_VOLUME_MANAGE? The reason I ask is that I’m still wanting to determine “major things” that would break if a particular OS user right is not granted to the account running SQL Server. We frequently have requests from customers wanting to strip away OS rights from the service accounts and I prefer to have a better “justification” than “the installation guide says you need to have it”.
Answer : Let me start by answering it from a more fundamental level. Any user mode application, whether it is SQL Server, Notepad, Excel or MyDotNetApp ultimately calls base user-mode Windows APIs which are publicly documented. In other words, if a user-mode application needs ANY service from the OS, it has to call a Windows API. Now, to answer your question more specifically, if the API itself requires a particular permission, then the application simply has to comply and grant it. Typically, SQL Server won’t require a permission just to make it difficult on administrators. The Trustworthy Computing initiative started at Microsoft years ago drives many of these choices; the goals are – secure out of the box, minimum security opening, and only when necessary – as far as I understand the wide directives of the initiative in that aspect. Therefore, my experience is, that frequently it is the API that requires the permission.
To illustrate, Locked Pages in Memory is not a right that is required by SQL Server to allocate memory. The Windows API call that SQL Server uses, however, requires it. This windows API call in question AllocateUserPhysicalPages() is part of the Address Windowing Extensions API set. Yes, AWE API is used in 64-bit SQL Server to allocate the Buffer Pool, if you enable LPIM user right. If you do not enable it, then Virtual memory allocation via VirtualAlloc() is used instead. The AWE API architecturally bypasses the Virtual Memory Manager (VMM), and is therefore not impacted by VMM whims of paging processes out to the page file. Consequently, as a byproduct of not being “controlled” by VMM, AWE pages are “locked”. They are not really locked, they are simply out of reach or not impacted by paging actions.
As mentioned, the AWE API call for allocating memory is AllocateUserPhysicalPages() . If you look at the Remarks at the bottom of this MSDN article, it states the following:
“The AllocateUserPhysicalPages function is used to allocate physical memory that can later be mapped within the virtual address space of the process. The SeLockMemoryPrivilege privilege must be enabled in the caller's token or the function will fail with ERROR_PRIVILEGE_NOT_HELD ” (underline by J P)
SQL Server code does something like this
bool useLockedPagesInMemory = SetUserPrivilege ( L" SeLockMemoryPrivilege ", TRUE);
….
if (useLockedPagesInMemory)
AllocateUserPhysicalPages (….)
else
VirtualAlloc(…)
So the short answer to your question is “Yes, we do use those constants in SQL Server because we call the Windows APIs that require them”
Namaste!
Joseph
