SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Configuring Service Account Privileges for SQL Server


Configuring Service Account Privileges for SQL Server

Author
Message
okbangas
okbangas
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 1387
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
calvo
calvo
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2428 Visits: 4018
Good read, thanks for the article.

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Winni
Winni
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 34
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
Gazareth
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5748 Visits: 6001
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
Adam Seniuk
Right there with Babe
Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)

Group: General Forum Members
Points: 742 Visits: 1040
Great Article, I didn't know about the traceflag for standard edition.


Over 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
okbangas
okbangas
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 1387
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
GP Van Eron
GP Van Eron
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3491 Visits: 466
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
okbangas
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 1387
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
SimonLiew
SimonLiew
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4024 Visits: 1834
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
okbangas
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1887 Visits: 1387
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search