-
-
Save jeffpatton1971/354c4c0948a93ea5b9e0 to your computer and use it in GitHub Desktop.
<# | |
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 | |
#> |
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 :(
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