SCOM – SQL Management Pack Configuration

SCOM is a bastard of a product, isn’t it? It’s even more so when you’re trying to monitor a SQL instance or two. It’s also quite amusing that Chrome doesn’t recognise SCOM as a word in its dictionary and that it suggests SCAM as a possible word 🙂

My major project at work for the past few months has been SCOM. I am monitoring about 300 Windows VMs, about a third of which have a SQL database instances on them. I’ve kept with using the LocalSystem account as the SCOM action account and for the majority of the time, that’s enough. However, there have been a few times where it hasn’t been enough. It’s always a permissions issue, the LocalSystem account doesn’t have access to one or more of the databases so the discovery and monitoring scripts can’t run and you get a myriad of alerts.

When it comes to adding a management pack into SCOM, always read the damn documentation that comes with the MP. I know it’s tedious but it’s necessary. Reading the documentation for the SQL Management pack found at Microsoft’s website gives you some interesting recommendations. They suggest that you have three action accounts for SQL:

  1. A discovery account
  2. A default action account
  3. A monitoring account

They also recommend that you put the monitoring and discovery account into an additional AD group. Once you do that, you have to add the users to SQL, assign them specific permissions to databases, give them access to parts of the Windows registry, assign them permissions to various WMI namespaces, grant them local logon privileges and more. I’m not going to go over the whole process, if you really want to see it look at Microsoft’s documentation.

The point is, it’s a lot of work. Wouldn’t it be nice if we could automate it? Well, I’ve written a script that does precisely that. It’s a big one:


Function Set-WmiNamespaceSecurity
{
[cmdletbinding()]
Param ( [parameter(Mandatory=$true,Position=0)][string] $namespace,
[parameter(Mandatory=$true,Position=1)][string] $operation,
[parameter(Mandatory=$true,Position=2)][string] $account,
[parameter(Position=3)][string[]] $permissions = $null,
[bool] $allowInherit = $false,
[bool] $deny = $false,
[string] $computerName = ".",
[System.Management.Automation.PSCredential] $credential = $null)

Process {
#$ErrorActionPreference = "Stop"

Function Get-AccessMaskFromPermission($permissions) {
$WBEM_ENABLE = 1
$WBEM_METHOD_EXECUTE = 2
$WBEM_FULL_WRITE_REP = 4
$WBEM_PARTIAL_WRITE_REP = 8
$WBEM_WRITE_PROVIDER = 0x10
$WBEM_REMOTE_ACCESS = 0x20
$WBEM_RIGHT_SUBSCRIBE = 0x40
$WBEM_RIGHT_PUBLISH = 0x80
$READ_CONTROL = 0x20000
$WRITE_DAC = 0x40000

$WBEM_RIGHTS_FLAGS = $WBEM_ENABLE,$WBEM_METHOD_EXECUTE,$WBEM_FULL_WRITE_REP,`
$WBEM_PARTIAL_WRITE_REP,$WBEM_WRITE_PROVIDER,$WBEM_REMOTE_ACCESS,`
$READ_CONTROL,$WRITE_DAC
$WBEM_RIGHTS_STRINGS = "Enable","MethodExecute","FullWrite","PartialWrite",`
"ProviderWrite","RemoteAccess","ReadSecurity","WriteSecurity"

$permissionTable = @{}

for ($i = 0; $i -lt $WBEM_RIGHTS_FLAGS.Length; $i++) {
$permissionTable.Add($WBEM_RIGHTS_STRINGS[$i].ToLower(), $WBEM_RIGHTS_FLAGS[$i])
}

$accessMask = 0

foreach ($permission in $permissions) {
if (-not $permissionTable.ContainsKey($permission.ToLower())) {
throw "Unknown permission: $permission`nValid permissions: $($permissionTable.Keys)"
}
$accessMask += $permissionTable[$permission.ToLower()]
}

$accessMask
}

if ($PSBoundParameters.ContainsKey("Credential")) {
$remoteparams = @{ComputerName=$computer;Credential=$credential}
} else {
$remoteparams = @{ComputerName=$computerName}
}

$invokeparams = @{Namespace=$namespace;Path="__systemsecurity=@"} + $remoteParams

$output = Invoke-WmiMethod @invokeparams -Name GetSecurityDescriptor
if ($output.ReturnValue -ne 0) {
throw "GetSecurityDescriptor failed: $($output.ReturnValue)"
}

$acl = $output.Descriptor
$OBJECT_INHERIT_ACE_FLAG = 0x1
$CONTAINER_INHERIT_ACE_FLAG = 0x2

$computerName = (Get-WmiObject @remoteparams Win32_ComputerSystem).Name

if ($account.Contains('\')) {
$domainaccount = $account.Split('\')
$domain = $domainaccount[0]
if (($domain -eq ".") -or ($domain -eq "BUILTIN")) {
$domain = $computerName
}
$accountname = $domainaccount[1]
} elseif ($account.Contains('@')) {
$domainaccount = $account.Split('@')
$domain = $domainaccount[1].Split('.')[0]
$accountname = $domainaccount[0]
} else {
$domain = $computerName
$accountname = $account
}

$getparams = @{Class="Win32_Account";Filter="Domain='$domain' and Name='$accountname'"}

$win32account = Get-WmiObject @getparams

if ($win32account -eq $null) {
throw "Account was not found: $account"
}

switch ($operation) {
"add" {
if ($permissions -eq $null) {
throw "-Permissions must be specified for an add operation"
}
$accessMask = Get-AccessMaskFromPermission($permissions)

$ace = (New-Object System.Management.ManagementClass("win32_Ace")).CreateInstance()
$ace.AccessMask = $accessMask
if ($allowInherit) {
$ace.AceFlags = $OBJECT_INHERIT_ACE_FLAG + $CONTAINER_INHERIT_ACE_FLAG
} else {
$ace.AceFlags = 0
}

$trustee = (New-Object System.Management.ManagementClass("win32_Trustee")).CreateInstance()
$trustee.SidString = $win32account.Sid
$ace.Trustee = $trustee

$ACCESS_ALLOWED_ACE_TYPE = 0x0
$ACCESS_DENIED_ACE_TYPE = 0x1

if ($deny) {
$ace.AceType = $ACCESS_DENIED_ACE_TYPE
} else {
$ace.AceType = $ACCESS_ALLOWED_ACE_TYPE
}

$acl.DACL += $ace.psobject.immediateBaseObject
}

"delete" {
if ($permissions -ne $null) {
throw "Permissions cannot be specified for a delete operation"
}

[System.Management.ManagementBaseObject[]]$newDACL = @()
foreach ($ace in $acl.DACL) {
if ($ace.Trustee.SidString -ne $win32account.Sid) {
$newDACL += $ace.psobject.immediateBaseObject
}
}

$acl.DACL = $newDACL.psobject.immediateBaseObject
}

default {
throw "Unknown operation: $operation`nAllowed operations: add delete"
}
}

$setparams = @{Name="SetSecurityDescriptor";ArgumentList=$acl.psobject.immediateBaseObject} + $invokeParams

$output = Invoke-WmiMethod @setparams
if ($output.ReturnValue -ne 0) {
throw "SetSecurityDescriptor failed: $($output.ReturnValue)"
}
}
}

Function Add-DomainUserToLocalGroup
{
[cmdletBinding()]
Param(
[Parameter(Mandatory=$True)]
[string]$computer,
[Parameter(Mandatory=$True)]
[string]$group,
[Parameter(Mandatory=$True)]
[string]$domain,
[Parameter(Mandatory=$True)]
[string]$user
)
$de = [ADSI]“WinNT://$computer/$Group,group”
$de.psbase.Invoke(“Add”,([ADSI]“WinNT://$domain/$user”).path)
} #end function Add-DomainUserToLocalGroup

Function Add-UserToLocalLogon
{
[cmdletBinding()]
Param(
[Parameter(Mandatory=$True)]
[string]$UserSID
)
$tmp = [System.IO.Path]::GetTempFileName()
secedit.exe /export /cfg "$($tmp)"
$c = Get-Content -Path $tmp
$currentSetting = ""

foreach($s in $c) {
if( $s -like "SeInteractiveLogonRight*") {
$x = $s.split("=",[System.StringSplitOptions]::RemoveEmptyEntries)
$currentSetting = $x[1].Trim()
}
}

if( $currentSetting -notlike "*$($UserSID)*" ) {
if( [string]::IsNullOrEmpty($currentSetting) ) {
$currentSetting = "*$($UserSID)"
} else {
$currentSetting = "*$($UserSID),$($currentSetting)"
}

$outfile = @"
[Unicode]
Unicode=yes
[Version]
signature="`$CHICAGO`$"
Revision=1
[Privilege Rights]
SeInteractiveLogonRight = $($currentSetting)
"@

$tmp2 = [System.IO.Path]::GetTempFileName()

$outfile | Set-Content -Path $tmp2 -Encoding Unicode -Force

Push-Location (Split-Path $tmp2)

try {
secedit.exe /configure /db "secedit.sdb" /cfg "$($tmp2)" /areas USER_RIGHTS

} finally {
Pop-Location
}
}
}

#Set Global Variables

$Default_Action_Account = "om_aa_sql_da"
$Discovery_Action_Account = "om_aa_sql_disc"
$Monitoring_Action_Account = "om_aa_sql_mon"
$LowPrivGroup = "SQLMPLowPriv"

$WindowsDomain = "Intranet"
#Add users to local groups

Add-DomainUserToLocalGroup -computer $env:COMPUTERNAME -group "Performance Monitor Users" -user $Monitoring_Action_Account -domain $WindowsDomain
Add-DomainUserToLocalGroup -computer $env:COMPUTERNAME -group "Performance Monitor Users" -user $Default_Action_Account -domain $WindowsDomain
Add-DomainUserToLocalGroup -computer $env:COMPUTERNAME -group "Event Log Readers" -user $Monitoring_Action_Account -domain $WindowsDomain
Add-DomainUserToLocalGroup -computer $env:COMPUTERNAME -group "Event Log Readers" -user $Default_Action_Account -domain $WindowsDomain
Add-DomainUserToLocalGroup -computer $env:COMPUTERNAME -group "Users" -user $LowPrivGroup -domain $WindowsDomain
Add-DomainUserToLocalGroup -computer $env:COMPUTERNAME -group "Users" -user $Default_Action_Account -domain $WindowsDomain
#
#AD SIDs for Default Action Account user and Low Priv group - required for adding users to local groups and for service security settings.

#Define SIDs for Default Action and Low Priv group. To get a SID, use the following command:
#Get-ADUser -identity [user] | select SID
#and
#Get-ADGroup -identity [group] | select SID
#Those commands are part of the AD management pack which is why they're not in this script, I can't assume that this script is being run on a DC or on
#a machine with the AD management shell installed
#>

$SQLDASID = "S-1-5-21-949506055-860247811-1542849698-1419242"
$SQLMPLowPrivsid = "S-1-5-21-949506055-860247811-1542849698-1419239"

Add-UserToLocalLogon -UserSID $SQLDASID
Add-UserToLocalLogon -UserSID $SQLMPLowPrivsid

#Set WMI Namespace Security

Set-WmiNamespaceSecurity root add $WindowsDomain\$Default_Action_Account MethodExecute,Enable,RemoteAccess,Readsecurity
Set-WmiNamespaceSecurity root\cimv2 add $WindowsDomain\$Default_Action_Account MethodExecute,Enable,RemoteAccess,Readsecurity
Set-WmiNamespaceSecurity root\default add $WindowsDomain\$Default_Action_Account MethodExecute,Enable,RemoteAccess,Readsecurity
if (Get-WMIObject -class __Namespace -namespace root\microsoft\sqlserver -filter "name='ComputerManagement10'") {
Set-WmiNamespaceSecurity root\Microsoft\SqlServer\ComputerManagement10 add $WindowsDomain\$Default_Action_Account MethodExecute,Enable,RemoteAccess,Readsecurity }
if (Get-WMIObject -class __Namespace -namespace root\microsoft\sqlserver -filter "name='ComputerManagement11'") {
Set-WmiNamespaceSecurity root\Microsoft\SqlServer\ComputerManagement11 add $WindowsDomain\$Default_Action_Account MethodExecute,Enable,RemoteAccess,Readsecurity }

Set-WmiNamespaceSecurity root add $WindowsDomain\$LowPrivGroup MethodExecute,Enable,RemoteAccess,Readsecurity
Set-WmiNamespaceSecurity root\cimv2 add $WindowsDomain\$LowPrivGroup MethodExecute,Enable,RemoteAccess,Readsecurity
Set-WmiNamespaceSecurity root\default add $WindowsDomain\$LowPrivGroup MethodExecute,Enable,RemoteAccess,Readsecurity
if (Get-WMIObject -class __Namespace -namespace root\microsoft\sqlserver -filter "name='ComputerManagement10'") {
Set-WmiNamespaceSecurity root\Microsoft\SqlServer\ComputerManagement10 add $WindowsDomain\$LowPrivGroup MethodExecute,Enable,RemoteAccess,Readsecurity }
if (Get-WMIObject -class __Namespace -namespace root\microsoft\sqlserver -filter "name='ComputerManagement11'") {
Set-WmiNamespaceSecurity root\Microsoft\SqlServer\ComputerManagement11 add $WindowsDomain\$LowPrivGroup MethodExecute,Enable,RemoteAccess,Readsecurity }

#Set Registry Permissions

$acl = Get-Acl 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
$Rule = New-Object System.Security.AccessControl.RegistryAccessRule ("$($WindowsDomain)\$($Default_Action_Account)","readkey","ContainerInherit","None","Allow")
$acl.SetAccessRule($Rule)
$acl | Set-Acl -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
$acl = $null
$acl = Get-Acl 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
$Rule = New-Object System.Security.AccessControl.RegistryAccessRule ("$($WindowsDomain)\$($LowPrivGroup)","readkey","ContainerInherit","None","Allow")
$acl.SetAccessRule($Rule)
$acl | Set-Acl -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
$acl = $null

$SQLInstances = Get-ChildItem 'registry::hklm\SOFTWARE\Microsoft\Microsoft SQL Server' | ForEach-Object {Get-ItemProperty $_.pspath } | Where-Object {$_.pspath -like "*MSSQL1*" }

$SQLInstances | Foreach {
$acl = Get-Acl "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$($_.PSChildName)\MSSQLSERVER\Parameters"
$Rule = New-Object System.Security.AccessControl.RegistryAccessRule ("$($WindowsDomain)\$($LowPrivGroup)","readkey","ContainerInherit","None","Allow")
$acl.SetAccessRule($Rule)
$acl | Set-Acl -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$($_.PSChildName)\MSSQLSERVER\Parameters"
$acl = $null

$acl = Get-Acl "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$($_.PSChildName)\MSSQLSERVER\Parameters"
$Rule = New-Object System.Security.AccessControl.RegistryAccessRule ("$($WindowsDomain)\$($Default_Action_Account)","readkey","ContainerInherit","None","Allow")
$acl.SetAccessRule($Rule)
$acl | Set-Acl -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$($_.PSChildName)\MSSQLSERVER\Parameters"
$acl = $null

}

#Set SQL Permissions

#Get SQL Version
if ($SQLInstances.Count -eq $null) {

$version = Get-ItemProperty "registry::HKLM\Software\Microsoft\Microsoft SQL Server\$($SQLInstances.PSChildName)\MSSQLSERVER\CurrentVersion"

} else {

$version = Get-ItemProperty "registry::HKLM\Software\Microsoft\Microsoft SQL Server\$($SQLInstances[0].PSChildName)\MSSQLSERVER\CurrentVersion"

}
#Import appropriate SQL PowerShell module

if ($version.CurrentVersion -ge 11) {
#Import SQL 2012 Module
Import-Module sqlps
#change out of sql context
c:
} else {
#Add SQL 2008 Snap-in
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
}

#Create database users and assign permissions

$CreateDatabaseUsers = "use master
go

create login [$($WindowsDomain)\$($LowPrivGroup)] from windows
go

grant view server state to [$($WindowsDomain)\$($LowPrivGroup)]
grant view any definition to [$($WindowsDomain)\$($LowPrivGroup)]
grant view any database to [$($WindowsDomain)\$($LowPrivGroup)]
grant select on sys.database_mirroring_witnesses to [$($WindowsDomain)\$($LowPrivGroup)]
go

create login [$($WindowsDomain)\$($Default_Action_Account)] from windows
go

grant view server state to [$($WindowsDomain)\$($Default_Action_Account)]
grant view any definition to [$($WindowsDomain)\$($Default_Action_Account)]
grant view any database to [$($WindowsDomain)\$($Default_Action_Account)]
grant alter any database to [$($WindowsDomain)\$($Default_Action_Account)]
grant select on sys.database_mirroring_witnesses to [$($WindowsDomain)\$($Default_Action_Account)]
go"

#Generate query to assign users and permissions to databases
$DatabaseUsers1 = "SELECT 'use ' + name + ' ;'
+ char(13) + char(10)
+ 'create user [$($WindowsDomain)\$($LowPrivGroup)] FROM login [$($WindowsDomain)\$($LowPrivGroup)];'
+ char(13) + char(10) + 'go' + char(13) + char(10)
FROM sys.databases WHERE database_id = 1 OR database_id >= 3
UNION
SELECT 'use msdb; exec sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername=''$($WindowsDomain)\$($LowPrivGroup)'''
+ char(13) + char(10) + 'go' + char(13) + char(10)
UNION
SELECT 'use msdb; exec sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername=''$($WindowsDomain)\$($LowPrivGroup)'''
+ char(13) + char(10) + 'go' + char(13) + char(10)
"

$DatabaseUsers2 = "SELECT 'use ' + name + ' ;'
+ char(13) + char(10)
+ 'create user [$($WindowsDomain)\$($Default_Action_Account)] FROM login [$($WindowsDomain)\$($Default_Action_Account)];'
+ 'exec sp_addrolemember @rolename=''db_owner'', @membername=''$($WindowsDomain)\$($Default_Action_Account)'';'
+ 'grant alter to [$($WindowsDomain)\$($Default_Action_Account)];'
+ char(13) + char(10) + 'go' + char(13) + char(10)
FROM sys.databases WHERE database_id = 1 OR database_id >= 3
UNION
SELECT 'use msdb; exec sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername=''$($WindowsDomain)\$($Default_Action_Account)'''
+ char(13) + char(10) + 'go' + char(13) + char(10)
UNION
SELECT 'use msdb; exec sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername=''$($WindowsDomain)\$($Default_Action_Account)'''
+ char(13) + char(10) + 'go' + char(13) + char(10)
"

#
$SQLInstances | Foreach {
if ($_.PSChildName.split('.')[-1] -eq "MSSQLSERVER") {
$InstanceName = $env:COMPUTERNAME
} else {
$InstanceName = "$($env:COMPUTERNAME)\$($_.PSChildName.split('.')[-1])" }

Invoke-Sqlcmd -ServerInstance $InstanceName $CreateDatabaseUsers
$Provision1 = Invoke-Sqlcmd -ServerInstance $InstanceName $DatabaseUsers1
$Provision2 = Invoke-Sqlcmd -ServerInstance $InstanceName $DatabaseUsers2

$Provision1 | foreach {
Invoke-Sqlcmd -ServerInstance $InstanceName $_.ItemArray[0]
}
$Provision2 | foreach {
Invoke-Sqlcmd -ServerInstance $InstanceName $_.ItemArray[0]
}
}

#Grant Default Action account rights to start and stop SQL Services

$SQLServices = Get-Service -DisplayName "*SQL*"

$SQLServices | Foreach {
& c:\windows\system32\sc.exe sdset $_.Name D`:`(A`;`;GRRPWP`;`;`;$($SQLDASID)`)`(A`;`;CCLCSWRPWPDTLOCRRC`;`;`;SY`)`(A`;`;CCDCLCSWRPWPDTLOCRSDRCWDWO`;`;`;BA`)`(A`;`;CCLCSWLOCRRC`;`;`;IU`)`(A`;`;CCLCSWLOCRRC`;`;`;SU`)`S`:`(AU`;FA`;CCDCLCSWRPWPDTLOCRSDRCWDWO`;`;`;WD`)
}

There are huge swathes of this script that I can not take credit for, mostly the functions.

The SetWMINameSpaceSecurity function was pilfered directly from here: https://live.paloaltonetworks.com/t5/Management-Articles/PowerShell-Script-for-setting-WMI-Permissions-for-User-ID/ta-p/53646. I got it from Palo Alto’s website but it appears to have been written by Microsoft themselves

The Add-DomainUserToLocalGroup function was stolen from the Hey, Scripting Guy! Blog, found here: https://blogs.technet.microsoft.com/heyscriptingguy/2010/08/19/use-powershell-to-add-domain-users-to-a-local-group/

The Add-UserToLocalLogon function was lifted wholesale from here: https://ikarstein.wordpress.com/2012/10/12/powershell-script-to-add-account-to-allow-logon-locally-privilege-on-local-security-policy/

The rest, however, is all mine which you can probably tell from the quality of the code. You will need to change some variables from line 223 to match your environment. That said, it works and that’s all I care about. Enjoy!

Sigh, sometimes, software can be too bloody clever for its own good. The Code Block module that I’m using isn’t doing a very good job of formatting this script and it’s replaced some characters such as &, < and > with their HTML equivalents. I think I’ve weeded them all out but I may not have. If not, let me know.

Leave a Reply

%d bloggers like this: