Last active
September 12, 2021 16:07
-
-
Save jeffpatton1971/354c4c0948a93ea5b9e0 to your computer and use it in GitHub Desktop.
Configure the SQL LowPriv Environment.
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
| <# | |
| 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 | |
| #> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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
New-Object : Constructor not found. Cannot find an appropriate constructor for type System.Security.Principal.NTAccount
.
Please help, I'm not a PowerShell expert :(