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.
