Grant SQL Server account access to Lock Pages in Memory using PowerShell DSC.
Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk.
I was implementing SQL server hardening via PowerShell DSC and one of the requirement was to have Lock Pages In Memory enabled for SQL Service account. There was a powershell script created and available from blog post by keepingitgeek.
I converted this to a DSC module for my use. The code snippet is added below.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ## Powershell script referenced from http://keepingitgeek.blogspot.com/2015/01/grant-sql-server-account-access-to-lock.html | |
| ## Powershell script was converted to a DSC module for implementation. | |
| Configuration createServiceUserUser { | |
| param( | |
| # Parameter help description | |
| [Parameter(Mandatory)] | |
| [string] | |
| $VMHostName, | |
| # Parameter help description | |
| [Parameter(Mandatory)] | |
| [pscredential] | |
| $user | |
| ) | |
| Import-DscResource -ModuleName 'SqlServerDSC' -ModuleVersion '12.2.0.0' | |
| ## Set SeLockMemoryPrivilege | |
| ## Variables | |
| $TempLocation = "C:\Temp" | |
| $SQLServiceAccount = $user.UserName #Account used for the SQL Service | |
| $SQLInstance = "MSSQLSERVER" | |
| $ChangeFrom = "SeManageVolumePrivilege = " | |
| $ChangeFrom2 = "SeLockMemoryPrivilege = " | |
| $ChangeTo = "SeManageVolumePrivilege = SQLServerSQLAgentUser$" + $vmHostName + "`$" + "$SQLInstance," | |
| $ChangeTo2 = "SeLockMemoryPrivilege = $SQLServiceAccount," | |
| Node $VMHostName { | |
| User CheckServiceAccountPresent { | |
| UserName = $user.UserName | |
| Ensure = 'Present' | |
| PasswordNeverExpires = $true | |
| Password = $user | |
| } | |
| Script SeLockMemoryPrivilege{ | |
| GetScript = { | |
| return @{ | |
| test = 'results' | |
| } | |
| } | |
| TestScript = { | |
| $TempLocation = "C:\Temp" | |
| $SQLServiceAccount = $user.UserName #Account used for the SQL Service | |
| $SQLInstance = "MSSQLSERVER" | |
| IF ((Test-Path $TempLocation) -eq $false) | |
| { | |
| New-Item -ItemType Directory -Force -Path $TempLocation | |
| Write-Host "Folder $TempLocation created" | |
| } | |
| # Set a name for the Security Policy cfg file. | |
| $fileName = "$TempLocation\SecPolExport.cfg" | |
| #export currect Security Policy config | |
| Write-Host "Exporting Security Policy to file" | |
| secedit /export /cfg $filename | |
| IF ((Get-Content $fileName) | where { $_.Contains("SeLockMemoryPrivilege") }){ | |
| return $true | |
| }else{ | |
| return $false | |
| } | |
| } | |
| SetScript = { | |
| $TempLocation = "C:\Temp" | |
| $SQLServiceAccount = $user.UserName #Account used for the SQL Service | |
| $SQLInstance = "MSSQLSERVER" | |
| $ChangeFrom = "SeManageVolumePrivilege = " | |
| $ChangeFrom2 = "SeLockMemoryPrivilege = " | |
| $ChangeTo = "SeManageVolumePrivilege = SQLServerSQLAgentUser$" + $vmHostName + "`$" + "$SQLInstance," | |
| $ChangeTo2 = "SeLockMemoryPrivilege = $SQLServiceAccount," | |
| $fileName = "$TempLocation\SecPolExport.cfg" | |
| #export currect Security Policy config | |
| Write-Host "Exporting Security Policy to file" | |
| secedit /export /cfg $filename | |
| # Use Get-Content to change the text in the cfg file and then save it | |
| (Get-Content $fileName) -replace $ChangeFrom, $ChangeTo | Set-Content $fileName | |
| # As the line for SeLockMemoryPrivilege only exists if there is something already in the group | |
| # this will check for it and add your $SQLServiceAccount or use Add-Contect to append SeLockMemoryPrivilege and your $SQLServiceAccount | |
| IF ((Get-Content $fileName) | where { $_.Contains("SeLockMemoryPrivilege") }) | |
| { | |
| Write-Host "Appending line containing SeLockMemoryPrivilege with $SQLServiceAccount" | |
| (Get-Content $fileName) -replace $ChangeFrom2, $ChangeTo2 | Set-Content $fileName | |
| } | |
| else | |
| { | |
| Write-Host "Adding new line containing SeLockMemoryPrivilege" | |
| Add-Content $filename "`nSeLockMemoryPrivilege = $SQLServiceAccount" | |
| } | |
| # Import new Security Policy cfg (using '1> $null' to keep the output quiet) | |
| Write-Host "Importing Security Policy..." | |
| secedit /configure /db secedit.sdb /cfg $fileName 1> $null | |
| Write-Host "Security Policy has been imported" | |
| } | |
| # DependsOn = '[User]CheckServiceAccountPresent' | |
| } | |
| } | |
| } |