Powershell,PSEexec and lock pages in memory – made easy

What if you had to create a script stack that installs SQL Server from an image library? And  customisations – such as adding the SQL Server Start up account to the Lock Pages in Memory Local Policies. ?

If this needs to be rolled out quickly across hundreds of servers , or just one , then there is a way to do this using Group Policy Security Templates and Security Database.  – along with secedit , PSExec and Powershell

 secedit.exe is a command line tool and comes with Windows. You would use Powershell to call secedit.exe , pass a couple of parameters and job done!

 Main steps are:

Step 1 : Create Security template and Security Database

Step 2 : Write syntax for secedit.exe

Step 3: Create Powershell script to grab parameters and execute the command

 Before you start – create a MMC consoled and add 2 snap ins

1)Security Configuration and Analysis

2)Security templates

 Step 1: Create Security template and Security Database

 Expand Security Templates and right click on the security path : Click New template : Create a template name – for this example I’m using “sqlmemlock”. Click OK and you should see the  template appear on the template list.

Expand the template “sqlmemlock” | Local Policies | User Rights Assignment . I added LOCAL SERVICE for the example, but normally I have a dedicated SQL Server Start Up acct.

You should now have something like this:

Next – create the Security database. Right click the snap-in Security configuration and Analysis| Open Database and then add a filename : for the example : I’m using “sqlmemlockdb” | Click Open | Import Template : “sqlmemlock” (the one you created a few minutes ago).

Right click on  Security configuration and Analysis and you’ll see 2 options : Analyze this computer and Configure Computer now. Analyze this computer  is useful to do a pre and post check.

 Once you’ve done the analysis – choose Configure now and using gpedit.msc – you should see the desired result.

If this has not worked – troubleshoot and ensure you have this part working!

 Step 2 : Draft syntax for secedit.exe

Assuming – you’ve got the template  in “C:\windows\security\templates\” and  and sdb  in “C:\windows\security\databases\”

 The syntax is :

Secedit /configure /cfg  templatefilename /db databasefilename/overwrite /log logfilename

 For our example:

 Secedit /configure  /cfg C:\windows\security\templates\sqlmemlock.inf /db sqlmemlockdb.sdb  /log C:\windows\security\logs\sqlmemlock.log

Step 3: Create Powershell script to grab parameters and execute the command

 There are many ways to execute the script on multiple servers. Powershell & psexec – are fantastic tools – which can be used  to manage your servers.

 This is just one way of managing your servers

 1- Write the above syntax into a cmd file – I’ll call it – lockpages.cmd

 Secedit /configure  /cfg C:\windows\security\templates\sqlmemlock.inf /db sqlmemlockdb.sdb  /log C:\windows\security\logs\sqlmemlock.log

 2- Drag sqlmemlock.cmd onto the remote server

 3-       Create a powershell script and write this syntax :

 cmd /c psexec \\MYSERVER1  "C:\sqlunattended\lockpages.cmd"

 3-      Execute the powershell command – and presto!

 There are many possibilities of combining this process with a list of servers . Syntax that can iterate a list of servers.  Check this post Powershell : SQLCMD and invoke-expression  - adapt the syntax to your purposes

If you want syntax to to iterate through a list of servers, let me know and I’ll post.