Configure the SQL LowPriv Environment.

Setup-Server.ps1

<#
    This script will configure the local machine for the SQL MP
    Low Privilege Environment

    We need to set the following items on the server

    Add SQLDefaultAction account and SQLMonitor account to
        Performance Monitor Users

    Add SQLDefaultAction account and SQLMonitor account to
        EventLog Readers

    Add SQLDefaultAction account and SQLMPLowPriv group to
        Users

    Grant SQLDefaultAction account and SQLMPLowPriv group
        Logon Locally User Right

    Grant SQLDefaultAction account and SQLMPLowPriv group
        Read Permission
        HKLM:\Software\Microsoft\Microsoft SQL Server\

    Grant SQLMPLowPriv group
        Read Permission
        HKLM:\Software\Microsoft\Microsoft SQL Server\$INSTANCEID\MSSQLServer\Parameters

    Grant SQLDefaultAction account and SQLMPLowPriv group
        Execute Methods, Enable Account, Remote Enable, Read Security Permission
        WMI:\root\cimv2
        WMI:\root\default
        WMI:\root\Microsoft\SqlServer\ComputerManagement

    For each item we need to test if true, and if false
    perform the required item. 
    
    This may be able to be turned into a DSC flow.
#>
Param
(
    $Computername = $env:COMPUTERNAME,
    [string] $ActionAccount,
    [string] $LowPrivGroup
)
Import-Module C:\temp\SqlManagement.psm1
Import-Module C:\temp\ActiveDirectoryManagement.psm1
[System.Management.Automation.PSCredential] $SqlDefaultAction = (New-Object System.Management.Automation.PSCredential($ActionAccount,(New-Object System.Security.SecureString($null))))
[System.Security.Principal.NTAccount]$Account = New-Object System.Security.Principal.NTAccount($SqlDefaultAction.UserName);
[string]$sqlDaSid = $Account.Translate([System.Security.Principal.SecurityIdentifier]).ToString();
[System.Management.Automation.PSCredential] $SQLMPLowPriv = (New-Object System.Management.Automation.PSCredential($LowPrivGroup,(New-Object System.Security.SecureString($null))))
[System.Security.Principal.NTAccount]$Account = New-Object System.Security.Principal.NTAccount($SQLMPLowPriv.UserName);
[string]$sqlLpSid = $Account.Translate([System.Security.Principal.SecurityIdentifier]).ToString();
#
# Test group membership for the following groups
#    Performance Monitor Users
#    EventLog Readers
#    Users
#
$Groups = @("Performance Monitor Users","Event Log Readers","Users");
foreach ($Group in $Groups)
{
    $Members = Get-LocalGroupMembers -ComputerName $Computername -GroupName $Group;
    if (!($Members |Where-Object {$_.Name -eq $SqlDefaultAction.GetNetworkCredential().UserName}))
    {
        #
        # Account not found
        #
        Add-UserToLocalGroup -ComputerName $Computername -UserName $SqlDefaultAction.GetNetworkCredential().UserName -LocalGroup $Group -UserDomain $SqlDefaultAction.GetNetworkCredential().Domain;
        }
    if (!($Members |Where-Object {$_.Name -eq $SQLMPLowPriv.GetNetworkCredential().UserName}))
    {
        #
        # Account not found
        #
        Add-UserToLocalGroup -ComputerName $Computername -UserName $SQLMPLowPriv.GetNetworkCredential().UserName -LocalGroup $Group -UserDomain $SQLMPLowPriv.GetNetworkCredential().Domain;
        }
    }
#
# Test logon locally right for the following accounts
#    SQLDefaultAction
#    SQLMPLowPriv
#
# http://gallery.technet.microsoft.com/PowerShell-script-to-add-b005e0f6#content
#
$tmp = [System.IO.Path]::GetTempFileName()
secedit.exe /export /cfg "$($tmp)" 
$Configuration = Get-Content -Path $tmp 
$currentSetting = ""
foreach($Line in $Configuration)
{
	if( $Line -like "SeInteractiveLogonRight*")
    {
		$x = $Line.split("=",[System.StringSplitOptions]::RemoveEmptyEntries)
		$currentSetting = $x[1].Trim()
	    }
    }
Remove-Item $tmp;
if (!(($currentSetting.Contains($sqlDaSid)) -and ($currentSetting.Contains($sqlLpSid))))
{
    if (!($currentSetting.Contains($sqlDaSid)))
    {
        $currentSetting += ",*$($sqlDaSid)";
        }
    if (!($currentSetting.Contains($sqlLpSid)))
    {
        $currentSetting += ",*$($sqlLpSid)";
        }
    Write-Host $currentSetting
$outFile = @"
[Unicode]
Unicode=yes
[Version]
signature="`$CHICAGO`$"
Revision=1
[Privilege Rights]
SeInteractiveLogonRight = $($currentSetting)
"@
    $tmp = [System.IO.Path]::GetTempFileName()
    $outFile | Set-Content -Path $tmp -Encoding Unicode -Force
    Push-Location (Split-Path $tmp);
    try
    {
        secedit.exe /configure /db "secedit.sdb" /cfg "$($tmp)" /areas USER_RIGHTS
        }
    finally
    {
        Pop-Location
        Remove-Item $tmp
        }
    }
else
{
    Write-Host "NO ACTIONS REQUIRED! Account already in ""Allow Logon Locally""" 
    }
#
# http://msdn.microsoft.com/en-us/library/ms147899(v=vs.110).aspx
#
# Test Read Permission for the following registry entries
#    HKLM:\Software\Microsoft\Microsoft SQL Server\
#
#    Will need to get each instance first
#
#    HKLM:\Software\Microsoft\Microsoft SQL Server\$INSTANCEID\MSSQLServer\Parameters
#
$Registry = "HKLM:\Software\Microsoft\Microsoft SQL Server\";
try
{
    $Acl = Get-Acl $Registry -ErrorAction SilentlyContinue;
    $Aces = ($Acl |Select-Object -Property Access).Access;
    if (!($Aces |Where-Object {$_.IdentityReference -eq $SqlDefaultAction.UserName}))
    {
        #
        Write-Host "Missing permission for SqlDefaultAction"
        #
        $Ace = New-Object System.Security.AccessControl.RegistryAccessRule(
            $SqlDefaultAction.UserName, 
            [System.Security.AccessControl.RegistryRights]::ReadKey, 
            [System.Security.AccessControl.InheritanceFlags]::ContainerInherit, 
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow);
        $Acl.SetAccessRule($Ace);
        }
    if (!($Aces |Where-Object {$_.IdentityReference -eq $SQLMPLowPriv.UserName}))
    {
        #
        write-host "Missing permission for SQLMPLowPriv"
        #
        $Ace = New-Object System.Security.AccessControl.RegistryAccessRule(
            $SQLMPLowPriv.UserName, 
            [System.Security.AccessControl.RegistryRights]::ReadKey, 
            [System.Security.AccessControl.InheritanceFlags]::ContainerInherit, 
            [System.Security.AccessControl.PropagationFlags]::None,
            [System.Security.AccessControl.AccessControlType]::Allow);
        $Acl.SetAccessRule($Ace);
        }
    $Instances = Get-SQLInstance;
    foreach ($Instance in $Instances)
    {
        $InstanceReg = "$($Registry)$($Instance.InstanceID)\MSSQLServer\Parameters";
        Write-Host $InstanceReg
        $Acl = Get-Acl $InstanceReg;
        $Aces = (Acl |Select-Object -Property Access).Access;
        if (!($Aces |Where-Object {$_.IdentityReference -eq $SQLMPLowPriv.UserName}))
        {
            #
            Write-Host "Missing permission for SQLMPLowPriv"
            #
            $Ace = New-Object System.Security.AccessControl.RegistryAccessRule(
                $SQLMPLowPriv.UserName, 
                [System.Security.AccessControl.RegistryRights]::ReadKey, 
                [System.Security.AccessControl.InheritanceFlags]::ContainerInherit, 
                [System.Security.AccessControl.PropagationFlags]::None,
                [System.Security.AccessControl.AccessControlType]::Allow);
            $Acl.SetAccessRule($Ace);
            }
        }
    }
catch
{
    Write-Host "SQL Not Installed"
    }
#
# Test Execute Methods, Enable Account, Remote Enable, Read Security Permission on WMI entries
#        WMI:\root\cimv2
#        WMI:\root\default
#
#        This entry depends on the sql version installed, so we just need to match ComputerManagement
#
#        WMI:\root\Microsoft\SqlServer\ComputerManagement
#
#
$Namespaces = @("root\cimv2","root\default","root\Microsoft\SqlServer\ComputerManagement10","root\Microsoft\SqlServer\ComputerManagement11");
Write-Host $Namespaces
foreach ($Namespace in $Namespaces)
{
    try
    {
        $Error.Clear()
        $Security = Get-WmiObject -Class __SystemSecurity -Namespace $Namespace -ErrorAction SilentlyContinue
        $Descriptor = @($null);
        $Result = $Security.PsBase.InvokeMethod("GetSD",$Descriptor)
        if ($Result -eq 0)
        {
            $daSDDL = "(A;;CCDCWPRC;;;$($sqlDaSid))";
            $converter = New-Object System.Management.ManagementClass Win32_SecurityDescriptorHelper;
            $rootSDDL = $converter.BinarySDToSDDL($Descriptor[0]);
            if (!($rootSDDL.SDDL.Contains($sqlDaSid)))
            {
                $NewSDDL = $rootSDDL.SDDL += $daSDDL;
                $WMIbinarySD = $converter.SDDLToBinarySD($NewSDDL);
                $WMIconvertedPermissions = ,$WMIbinarySD.BinarySD;
                $Result = $security.PsBase.InvokeMethod("SetSD",$WMIconvertedPermissions);
                }
            $lpSDDL = "(A;;CCDCWPRC;;;$($sqlLpSid))";
            if (!($rootSDDL.SDDL.Contains($sqlLpSid)))
            {
                $NewSDDL = $rootSDDL.SDDL += $lpSDDL;
                $WMIbinarySD = $converter.SDDLToBinarySD($NewSDDL);
                $WMIconvertedPermissions = ,$WMIbinarySD.BinarySD;
                $Result = $security.PsBase.InvokeMethod("SetSD",$WMIconvertedPermissions);
                }
            }
        else
        {
            Write-Error "An error occurred retreiving Security Definitions from WMI.`r`nError Number : $($Result)";
            break;
            }
        }
    catch
    {
        $_
        }
    }
#
# http://msdn.microsoft.com/en-us/library/aa394063(v=vs.85).aspx
#
# Don't need these, but don't want to lose them
#
$amFlags = @{1="FILE_READ_DATA";2="FILE_WRITE_DATA";4="FILE_APPEND_DATA";8="FILE_READ_EA";16="FILE_WRITE_EA";32="FILE_EXECUTE";64="FILE_DELETE_CHILD";128="FILE_READ_ATTRIBUTES";256="FILE_WRITE_ATTRIBUTES";65536="DELETE";131072="READ_CONTROL";262144="WRITE_DAC";524288="WRITE_OWNER";1048576="SYNCHRONIZE"}
$afFlags = @{1="OBJECT_INHERIT_ACE"; 2="CONTAINER_INHERIT_ACE";4="NO_PROPAGATE_INHERIT_ACE";8="INHERIT_ONLY_ACE";16="INHERITED_ACE"}
<#
    This script will configure the SQL Server for the SQL MP
    Low Privilege Environment
#>

You will need to pass in a username and group name, these would have been the ones you defined when you were working through the SQL MP Documentation. You will also need to use my ComputerManagement and SqlManagement libraries, you can find them over here (https://github.com/jeffpatton1971/mod-posh/tree/master/powershell/production/includes)

This has been tested and works, updated to catch when SQL registry isn't found. IE sql not installed.

You will also need the following two modules that I wrote in order for this to work for you.

https://github.com/jeffpatton1971/mod-posh/blob/master/powershell/production/includes/ActiveDirectoryManagement.psm1

https://github.com/jeffpatton1971/mod-posh/blob/master/powershell/production/includes/SqlManagement.psm1

Also, bear in mind this was written to address monitoring SQL servers using low-priv accounts from system center operations manager.

Jeff, I am setting up monitoring on SCOM 2012 and SQL Server 2014 and found this - looks like it can save me a lot of manual steps. However, while your comments at the top of setup-server.ps1 say that it adds the SQLMonitor account where necessary, it isn't passed in as a parameter like the default action account is, nor does the code make any reference to it where the manual says it should. I am new at SCOM and SS, so could have missed something, but is this right?

Regards
Peter

@pjbisset,

The reason the SQLMonitor account is not passed in at all is because the MP guide says to place the Monitoring account within the lowprivGroup :) Which is being passed in with Jeffs script. Jeff great script! I've added to it and included the SQL pieces too! pure gold!

When I try to run this script I get a lot of errors. What is the command line I should use? This is what I tried:

PS C:\Scripts\LowPriv> .\Setup-SQL-Server.ps1 "DOMAIN\ActionAccount" "DOMAIN\GroupName"

But then I get errors like this:

New-Object : Exception calling ".ctor" with "2" argument(s): "Cannot process argument because the value of argument "userName" is invalid. Change the value of the "userName" argument and run the operation again."

At C:\Scripts\LowPriv\Setup-SQL-Server.ps1:46 char:76

• [System.Management.Automation.PSCredential] $SqlDefaultAction = (New-Object <<<< System.Management.Automation.PSCredential($ActionAccount,(New-Object System.Security.SecureString($null))))
  • CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException
  • FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

New-Object : Constructor not found. Cannot find an appropriate constructor for type System.Security.Principal.NTAccount
.

Please help, I'm not a PowerShell expert :(