Configuring Service Account Privileges for SQL Server

  • okbangas

    SSChampion

    Points: 11773

    Comments posted to this topic are about the item Configuring Service Account Privileges for SQL Server



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • calvo

    SSChampion

    Points: 12930

    Good read, thanks for the article.

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • Winni

    Grasshopper

    Points: 12

    Hello Ole,

    what's about this MSDN post regarding 64-bit operating systems?

    "How to: Enable the Lock Pages in Memory Option (Windows)"

    SQL Server 2008 R2

    The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.

    Regards,

    Winfried Terlinden

  • Gazareth

    One Orange Chip

    Points: 27737

    I've wondered about that with 64-bit systems myself. And having to use the trace flag on Standard edition is a new one on me.

    Looks like I've got some SQL services to restart!

    Edit: Also; thanks Ole, good article 🙂

  • Adam Seniuk

    SSCrazy

    Points: 2221

    Great Article, I didn't know about the traceflag for standard edition.


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • okbangas

    SSChampion

    Points: 11773

    Winfried Terlinden (2/28/2012)


    This privilege must be enabled to configure Address Windowing Extensions (AWE). (...) On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Locking pages in memory is not required on 64-bit operating systems.

    Hi Winfried.

    First of all, yes Lock Pages in Memory may cause issues on 32-bit systems without AWE. As I understand this issue, this is mainly since SQL Server will use and lock as much memory as it can in the first 4GB. With AWE enabled, this memory may be allocated above 4GB, which is less likely to affect other programs. Some have experienced similar issues with Lock Pages in Memory on 64-bit systems if Max Server Memory is not configured.

    Lock pages in Memory is not required for allowing 32-bit applications (as SQL Server x86 Editions) to allocate memory above 4GB. It is still required for preventing the OS from paging the buffer pool however.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • GP Van Eron

    SSCarpal Tunnel

    Points: 4626

    Hello,

    Thank you kindly for the great article.

    One question. (On a 64bit 2008 R2 Standard Server w/ paging file on much slower disk than data) Lets say the service account is set to lock pages in memory in attempt to avoid page swapping. The hope is that all pages will be read from memory to avoid paging file reads on slower disk. However, if the SQL Server is already under memory pressure and can't offload buffer to paging file could that potentially cause more issue than allowing the slower reads?

    I have a prod server that is already under a great deal of memory pressure (causes already known). My concern is that if the instance can't offload to the paging file it might further increase this memory pressure.

    Is it that when lock pages in memory is set SQL Server will handle / reuse / free up space more efficiently than if it was off, therefor not actually further increasing memory pressure? Apologies if my question is silly or misguided.

    Thank you,

    GP

  • okbangas

    SSChampion

    Points: 11773

    First of all, I want to thank everyone for the good feedback.

    No matter if Lock Pages in Memory is enabled or not, SQL Server will attempt to release memory on request by the OS. I say attempt, since there is no absolute guarantee that SQL Server will be able to, but normally SQL Server will be able to release memory. As I wrote in a previous comment, some have experienced instability if Lock Pages in memory is used without limiting the buffer pool by setting the Max Server Memory for the instance as well.

    In your scenario, you could configure MAX Server memory to leave enough memory for the OS and other applications. For instance, if your Server has 32GB of memory, you may configure 28GB as max server memory, leaving approximately 4GB for the OS and other applications.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • SimonLiew

    SSCertifiable

    Points: 7384

    Last year Nov, our company had a review with Microsoft on SQLRAP results. They highlighted SQL Service account granted "perform volume maintenance tasks" as a risk. I just close my ear and as they blah blah blah about the us impose such strict securities but leaving this security hole. I tried to explain the merit of instant file initialization, but they argued we should've performed more testing, etc. I told them our databases are very large up to 1TB, what'll happen if we need to restore this somewhere, they replied you test it out first and then decide if you really need this functionality.

    They might be true but I still reckons the merit outweights the risk.

    Wonder if M$ has recommended the same for someone's SQLRAP and someone has actually taken the time to validate M$ claims. I know so many expert recommends granting SQL privs to instant file initialisation, is it really a big security risk as M$ claim?

    Simon

    Simon Liew
    Microsoft Certified Master: SQL Server 2008

  • okbangas

    SSChampion

    Points: 11773

    First of all, I think the security risk of instant file initialization is a bit misunderstood. Let me try to explain how it works.

    On a single instance server, any members of sysadmin may use DBCC PAGE to retrieve raw data which has not been erased. This may be an issue if the server has been reused for other purposes, without wiping the disks, or if a volume holding your data pages has been extended from a shared storage. Note though, the data in this instance is not at risk, it is the data which used to be stored on the same location which is at risk.

    On a multi instnace server, it is the same. But in addition, data from a different instance may be at risk as well, as there may be different members of sysadmin on the two instances.

    If you have a group of SQL Server sysadmins which controls ALL your SQL Servers, and you don't share storage array with anyone else, I'd say there is no security risk. Otherwise, I'd actually not be very concerned about the secure servers, they're just fine. The problem is the servers with looser security, where syadmins on this server potentially may reveal data which used to be stored on the more secure servers.

    I hope this explanation is satisfactory.



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • administrator 99050

    Newbie

    Points: 1

    Nice article..

  • michael.garnett

    Newbie

    Points: 1

    Great posting. What if SQL Svr is running on a Windows 7 platform?

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4522

    Nice one. Will add to my kitty. "instant file initialization"

    Br. Kenneth Igiri
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • quackhandle1975

    SSChampion

    Points: 10963

    okbangas (2/28/2012)


    First of all, I want to thank everyone for the good feedback.

    No matter if Lock Pages in Memory is enabled or not, SQL Server will attempt to release memory on request by the OS. I say attempt, since there is no absolute guarantee that SQL Server will be able to, but normally SQL Server will be able to release memory. As I wrote in a previous comment, some have experienced instability if Lock Pages in memory is used without limiting the buffer pool by setting the Max Server Memory for the instance as well.

    In your scenario, you could configure MAX Server memory to leave enough memory for the OS and other applications. For instance, if your Server has 32GB of memory, you may configure 28GB as max server memory, leaving approximately 4GB for the OS and other applications.

    I did this at my last client, set MAX Server memory to 28GB however after reboot SQL Server (2005) ignored it. I wanted to try Lock Pages in Memory/AWE but it was high use prod server so never got the chance. First time I'd seem SQL Server ignore a max mem setting.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply