SQL STIGMonitor

End the STIG(ma)

Seriously, dream on!  End the STIGma is a good thing, but STIGs can be a burden.  Hit the easy button, if you’re not already using it.  Contact your SQL Data and AI Cloud Solutions Architect for the latest SQL STIG Monitor 2024 Q4 build!


 

Latest SQL STIG monitor 31 Oct 2024 release includes

DISA UPDATES – see link
MS SQL Server 2016 Instance STIG, V3R2:

(NOTE: DISA has been contacted to remove related CCI STIGID for AzureSQLDB that was overlooked: ASQL-00-010700)

POWERSHELL MODULE
Updated version to 1.23
Added STIGID parameter to Invoke-StigMonitor allowing granular control over STIGID scanning.

DATABASE CHANGES
Updated Checklist Templates for Q4 Revisions.
Updated Instance & Database STIG for Q4 benchmark date.
Script updates include:

CNTNMIXDB: Not A Finding if using Windows Auth
FORCENRYPT: NA if using Windows Auth
PWDCMPLX: Updated Finding to remove OS STIG reference
AZDBPERMISS: Revised script with new version.
DBPERMISS: Revised script with new version.
ENFCACCSS: Revised script with new version.
PSERRPERM: Revised script with new version.
UNQSVCACC: Removed code stripping out port number.
AZAUDITSTATE: Properly returns No Finding when audit setup is correct.
Fixed bug in vDocumentation view causing POAMs to not display custom comment in exported documentation.
Added usp_RemoveInstance stored procedure to easily clean up a specific Instance from StigMonitor that no longer exists.

DOCUMENTS
Updated checklist templates, Approvals scripts, and Documentation Templates for Q4 Revisions.
Removed Set-CEIPRegKeys.ps1, Set-FIPSCompliance.ps1, and Set-SqlRegKey.ps1 in favor of Module commands.
Updated InfoPage with new StigMonitor logo and text references.
Documentation updated with new examples of Invoke-StigMonitor STIGID parameter.
Updated documentation to add Azure DB Permission for MS_SecurityDefinitionReader.
Added DatabaseName to CSV Export of Export-StigDocumentation.

REPORTS
Updated Report banner to display new StigMonitor logo and latest report versions.
Removed Adhoc scanning to Policy Management Report in favor of Invoke-StigMonitor parameter.
Removed references to Sunset 2012 and 2014 STIGs.
Added AzureSQLMI for future use.
Combined NF and Approved in Total Findings summary
Reduced Recent Scans to latest 6.

Also please send us your feedback if you get a chance to check this out.
If you want to be added/removed from this, click here (Subscribe /Unsubscribe) or send us an email.

 

SCOM SSRS permissions

Microsoft SQL Server SSRS icon
Microsoft SQL Server SSRS icon

Let’s discuss SCOM SSRS permissions.  The SCOM Reporting role install really comes down to three (3) things – permissions, latest SSRS EXE downloaded (for install 2019, 2022), and ReportExtensions configuration.  I’ve hit some permission issues that need more ‘how to’ details.

 

Set SCOM Admins group permissions

Whether the permissions are set up as part of a group policy (GPO) or not, if these steps are missing, expect problems.

Verify that your SCOM Admins domain group is a local administrator on the SCOM servers (SSRS server in this case)

Right click on Start > Computer Management

Expand System Tools

Expand Local Users and Groups

Click on Groups

Double click on Administrators

Verify SCOM Admins group, or specific service/MSA accounts are listed

Computer Management with Administrators group properties documenting relevant members which include the SCOM Admins group, and any other SQL related service accounts.
Computer Management with Administrators group properties documenting relevant members which include the SCOM Admins group, and any other SQL related service accounts.

Click OK

 

 

Set SQL Instance permissions for SCOM Admins group

Reference Holman’s QuickStart > Install SCOM Reporting Role…

  • Log on using your domain user account that is a member of the OMAdmins group, and has “sysadmin” role level rights over the SQL instance.

RDP to server with SSMS that connects to SQL server

Connect to Database Engine

Expand instance , then expand Security folder, thirdly expand Logins folder

Right click on the SCOM Admins group and select properties

In the pop-up, click on SQL Server Role

Verify that sysAdmin

View of SSMS Database Engine showing SCOM Admins group SQL Server Role has sysAdmin
View of SSMS Database Engine showing SCOM Admins group SQL Server Role has sysAdmin

Follow similar steps if using a domain connected SVC/MSA account when configuration differs from Holman’s QuickStart template.

 

Additional troubleshooting from the SCOM install can be found in the user’s directory – C:\Users\<accountHere>\AppData\Local\SCOM\LOGS

 

Find additional details in the SQL install logs

C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles

NOTE that the Instance and version 'MSRS13.MSSQLSERVER' can change

 

 

Additional documentation and relevant links

The go-to reference is Holman’s QuickStart deployment guides for SCOM2019 forward list the how-to starting point.

Holman Quick Start links:

https://kevinholman.com/2022/05/01/scom-2022-quickstart-deployment-guide/

https://kevinholman.com/2019/03/14/scom-2019-quickstart-deployment-guide/

 

SSRS learn.microsoft.com site article https://learn.microsoft.com/en-us/troubleshoot/system-center/scom/cannot-deploy-operations-manager-reports

SSRS Error occurred when invoking the authorization extension https://learn.microsoft.com/en-us/answers/questions/266488/installing-scom-2019-reporting-ssrs-2019-error-an

SCOM SSRS ReportExtensions

For a smooth install, everything comes down to SCOM SSRS prerequisites.  The SCOM Reporting role install really comes down to three (3) things – permissions, latest SSRS EXE downloaded (for install 2019, 2022), and ReportExtensions configuration.  The go-to reference is Holman’s QuickStart deployment guides for SCOM2019 forward list the how-to starting point.  This post focuses on ReportExtensions configuration, where more ‘how to’ details are needed.

Quick Start links:

SCOM 2022 – QuickStart Deployment Guide

SCOM 2019 – QuickStart Deployment Guide

SSRS learn.microsoft.com site article https://learn.microsoft.com/en-us/troubleshoot/system-center/scom/cannot-deploy-operations-manager-reports

 

Configure Report Extensions via SSMS (GUI)

RDP to server with enabled account

Open SSMS that has connectivity to SSRS install/server

Change ‘Server type’ drop-down to Reporting Service

Change SSMS Server Type from Database Engine to Reporting Service
Change SSMS Server Type from Database Engine to Reporting Service

Click Connect

Right click on Server > Properties

In the Server Properties window, select the Advanced Tab

Click on the AllowedResourceExtensionsForUpload, and add *.*

Click OK

Screenshot of SSMS Connected to Reporting Service, expanding SSRS Properties > Advanced Tab > showing AllowedResourceExtensionsForUpload
Screenshot of SSMS Connected to Reporting Service, expanding SSRS Properties > Advanced Tab > showing AllowedResourceExtensionsForUpload

Don’t forget to restart SSRS to make changes take effect!

Once restarted, verify SVC/MSA account permissions, and begin SCOM Reporting role!

 

Configure Report Extensions via PowerShell

Testing learn article PowerShell for SSRS Defaults (pre-requisite for SCOM Reporting role with SSRS2017+ versus SSMS).   > Reporting Services

SSRS Note for ServiceAddress (SSRS URL) is other than localhost

On respective server, open PowerShell as Admin

Paste the following:

$ServiceAddress = ‘http://localhost

$ExtensionAdd = @(

                ‘*’

                ‘CustomConfiguration’

                ‘Report’

                ‘AvailabilityMonitor’

                ‘TopNApplications’

                ‘Settings’

                ‘License’

                ‘ServiceLevelTrackingSummary’

                ‘CustomPerformance’

                ‘MostCommonEvents’

                ‘PerformanceTop’

                ‘Detail’

                ‘DatabaseSettings’

                ‘ServiceLevelObjectiveDetail’

                ‘PerformanceDetail’

                ‘ConfigurationChange’

                ‘TopNErrorGroupsGrowth’

                ‘AvailabilityTime’

                ‘rpdl’

                ‘mp’

                ‘TopNErrorGroups’

                ‘Downtime’

                ‘TopNApplicationsGrowth’

                ‘DisplayStrings’

                ‘Space’

                ‘Override’

                ‘Performance’

                ‘AlertDetail’

                ‘ManagementPackODR’

                ‘AlertsPerDay’

                ‘EventTemplate’

                ‘ManagementGroup’

                ‘Alert’

                ‘EventAnalysis’

                ‘MostCommonAlerts’

                ‘Availability’

                ‘AlertLoggingLatency’

                ‘PerformanceTopInstance’

                ‘rdl’

                ‘PerformanceBySystem’

                ‘InstallUpdateScript’

                ‘PerformanceByUtilization’

                ‘DropScript’

)

Write-Output ‘Setting Allowed Resource Extensions for Upload’

$error.clear()

try

{

                $Uri = [System.Uri]”$ServiceAddress/ReportServer/ReportService2010.asmx”

                $Proxy = New-WebServiceProxy -Uri $Uri -UseDefaultCredential

                $Type = $Proxy.GetType().Namespace + ‘.Property’

                $Property = New-Object -TypeName $Type

                $Property.Name = ‘AllowedResourceExtensionsForUpload’

$ValueAdd = $ExtensionAdd | ForEach-Object -Process {

                                “*.$psItem”

                }

$Current = $Proxy.GetSystemProperties($Property)

                if ($Current)

    {

                $ValueCurrent = $Current.Value -split ‘,’

                $ValueSet = $ValueCurrent + $ValueAdd | Sort-Object -Unique

                }

                else

    {

        $ValueSet = $ValueAdd | Sort-Object -Unique

    }

$Property.Value = $ValueSet -join ‘,’

                $Proxy.SetSystemProperties($Property)

    Write-Output ‘  Successfully set property to: *.*’

}

catch

{

                Write-Warning “Failure occurred: $error”

}

Write-Output ‘Script completed!’

 

Successfully set property to: *.*
PS C:\Windows\system32> Write-Output ‘Script completed!’
Script completed!
PS C:\Windows\system32>

 

Don’t forget to restart SSRS.

Verify SVC/MSA account permissions, then begin SCOM Reporting role!

Enjoy!

DNS Scavenging alerts

DNS Scavenging how it works

Need DNS Scavenging alerts, to see what’s cleaned up, or that scavenging failed?  Download the DNS Addendum pack from my GitHub repo https://github.com/theKevinJustin/DNSAddendumAgnostic

Latest revision first includes a EventID 2502 monitor for scavenging failed.  Second, the monitor has count logic (setup to alert with 2 events in 30 minutes).  Third, EventID 2501 rule details scavenging totals.  Lastly, built a weekly report to summarize the scavenging alerts (cliff notes!).

 

 

Some quick ‘how-to’ setup DNS scavenging

Example of RegKey showing that Scavenging is setup – note Scavenging Interval key

 

Example of AD integrated DNS setup with 21 day scavenging interval, and prompts to configure (click OK twice)

DNS Scavenging setup on AD integrated DNS server

 

Import management pack, and run DNS scavenging.

 

Verify scavenging alerts

SCOM Monitoring Tab > Active Alerts > ‘Look for:’ scavenging

Example output

 

Additional SCOM PowerShell commands

Run PowerShell commands from the SCOM management server (MS)

$DNSAlerts = get-scomalert -name "*Scavenging*"
$DNSAlerts
$DNSAlerts | format-table PrincipalName,TimeRaised,Description -auto -wrap

 

Example Output

PS C:\Users\scomadmin> $DNSAlerts = get-scomalert -name “*Scavenging*”

PS C:\Users\scomadmin> $DNSAlerts

 

Severity     Priority   Name                                                                        TimeRaised

——–     ——–   —-                                                                        ———-

Warning      Normal     Windows DNS Event 2502 Scavenging Failed monitor addendum alert             8/19/2024 2:02:3…

Warning      Normal     Windows DNS Event 2502 Scavenging Failed monitor addendum alert             8/19/2024 1:07:0…

Information  Normal     Proactive DailyTasks DNSAlerts Scavenging Summary Report Alert              8/19/2024 10:11:…

 

 

DNS alerts formatted

PS C:\Users\scomadmin> $DNSAlerts | format-table PrincipalName,TimeRaised,Description -auto -wrap

 

PrincipalName    TimeRaised            Description

————-    ———-            ———–

DC02.testlab.net 8/19/2024 2:02:32 PM  Windows DNS Event 2502 Scavenging Failed monitor alert 1 alert in 15 minutes

Event Description:

The DNS server has completed a scavenging cycle but no nodes were visited.

Possible causes of this condition include:

The next scavenging cycle is scheduled to run in 168 hours.

 

Learn articles for more details https://learn.microsoft.com/en-us/troubleshoot/windows-server/networking/dns-scavenging-setup

SCOM Agent Maintenance

Wrench for SCOM agent maintenance
Wrench for SCOM agent maintenance

When we talk about best practices for monitoring, this will typically include (SLA) Service Level Availability.  SLA is an important piece in your environment, as uptime and happy customers come with a high SLA.  There are some cases where IT Teams do work on demand.  On-demand work is outside of a standard change window, a scheduled change.  Typically this is outside configuration management tools, responsible to update software (applications/packages), machines, drivers, compliance settings, and more.  In the one-off, non-scheduled maintenance or recovery, try leveraging ‘SCOM Agent Maintenance’ PowerShell commands on SCOM agents.

 

SCOM Agent maintenance PowerShell commands

cd “C:\Program Files\Microsoft Monitoring Agent\Agent”

Import-module .\MaintenanceMode.dll

Start-SCOMAgentMaintenanceMode -Duration 10 -Reason PlannedOther

 

# Verify

# If messages show with current timestamp, Agent objects are in maintenance.

 

get-eventlog -LogName “Operations Manager” -newest 50 | ? { $_.Message -like “Suspending monitoring*”  } | ft TimeGenerated,Message -autosize

 

TimeGenerated        Message

————-        ——-

6/25/2020 8:37:57 AM Suspending monitoring for instance “modeldev” with id:”{F9E45AA4-7DF7-C1F1-70C9-5D76C9F2725C}” …

6/25/2020 8:37:57 AM Suspending monitoring for instance “C:” with id:”{ED00048A-7DDC-D4BE-901D-D64DA281B7C6}” as the…

6/25/2020 8:37:57 AM Suspending monitoring for instance “central_log” with id:”{EA619D69-D1CC-3B19-D93C-2E3FCD1409AE…

 

PS C:\Program Files\Microsoft Monitoring Agent\Agent> get-eventlog -LogName “Operations Manager” -newest 25 | ? { $_.Message -like “Resuming monitoring*”  } | ft TimeGenerated,Message -autosize

 

343998 Jun 25 08:50  Information HealthService          1073743040 Resuming monitoring for instance “modeldev” wit…

343997 Jun 25 08:50  Information HealthService          1073743040 Resuming monitoring for instance “C:” with id:”…

343996 Jun 25 08:50  Information HealthService          1073743040 Resuming monitoring for instance “central_log” …

343995 Jun 25 08:50  Information HealthService          1073743040 Resuming monitoring for instance “dnmll05s1.UNE…

New SQL Updates

Updated SQL patches released in July
Updated SQL patches released in July

It’s that time again, time to update SQL.  Just in case your configuration management solution automatically add SQL updates, you can be prepared.  Secondly, if you have to tell the configuration management team to approve updates, patches, this will help jumpstart that process.  Either way, knowing about the updates helps you make decisions for your organization’s change process.  I believe ‘knowledge is power’, so power up and take away whatever you need to keep up to date.

 

 

Subset of the SQL product group released ‘new SQL updates’ in July

SQL2016SP3GDR Security Update – 9 July https://techcommunity.microsoft.com/t5/sql-server-blog/security-update-for-sql-server-2016-sp3-gdr/ba-p/4187396

SQL2017RTMCU31 Security Update – 9 July https://techcommunity.microsoft.com/t5/sql-server-blog/security-update-for-sql-server-2017-rtm-cu31/ba-p/4187385

SQL2019 RTM CU27 Security Update – 9 July https://techcommunity.microsoft.com/t5/sql-server-blog/security-update-for-sql-server-2019-rtm-cu27/ba-p/4187401

SQL2022 RTM CU13 Security Update – 9 July https://techcommunity.microsoft.com/t5/sql-server-blog/security-update-for-sql-server-2022-rtm-cu13/ba-p/4187356

SQL2022 RTM CU14 23 July https://techcommunity.microsoft.com/t5/sql-server-blog/cumulative-update-14-for-sql-server-2022-rtm/ba-p/4199659

 

 

Example New SQL Updates for SQL2022

Cumulative Update #14 for SQL Server 2022 RTM

The 14th cumulative update release for SQL Server 2022 RTM is now available for download at the Microsoft Downloads site. Please note that registration is no longer required to download Cumulative updates.
To learn more about the release or servicing model, please visit:

Starting with SQL Server 2017, we adopted a new modern servicing model. Please refer to our blog for more details on Modern Servicing Model for SQL Server

New SQL management pack

SQL Server Blog – New SQL Management pack released!

 

The blog posting the pack release fell through the cracks.  Released on 10 July, I’ve had some issues getting the updated MSI’s, but they’re live now.   I normally use the SQL Tech Community SQL releases site https://techcommunity.microsoft.com/t5/sql-server-blog/bg-p/SQLServer/label-name/SQLReleases

 

Don’t forget to look for SQL Security updates, (CU) Cumulative Updates, or (SP) Service Pack updates at the SQL releases link!  https://techcommunity.microsoft.com/t5/sql-server-blog/bg-p/SQLServer/label-name/SQLReleases

 

 

New SQL pack released

Microsoft System Center Management Pack for SQL Server enables the discovery and monitoring of SQL Server 2012, 2014, 2016, 2017, 2019, 2022, and upcoming versions.

Download link https://www.microsoft.com/en-us/download/details.aspx/?id=56203

Version:
7.6.5

File Name:
SQLServerMP.Windows.msi

SQLServerMP.CustomMonitoring.msi

SQLServerMP.Linux.msi

SQLServerMPWorkflowList.pdf <missing as of today>

Date Published:
7/10/2024

Functionality https://learn.microsoft.com/en-us/system-center/scom/sql-server-management-pack-changes-history?view=sc-om-2022

https://www.microsoft.com/en-us/download/details.aspx/?id=56203

June 2024 – 7.5.19.0 CTP

What’s New

  • Added new “Table Clustered Index Fragmentation” monitor that targets databases and checks for high fragmentation of clustered indexes
  • Added new “Property Bag” step in the custom monitor setup to extend the alert context with a property from the query result
  • Updated the “Product Version Compliance” monitor with the most recent version of public updates for the SQL Server
  • Reworked the “Long Running Queries” alert rule to improve security
  • Improved accessibility for the Summary Dashboard view and Monitoring Wizard template, including the following major changes:
    • implemented Keyboard Navigation using the A and D buttons on the tiles in the dashboard
    • added the ability for the screen reader to announce buttons and errors in the SQL Server wizard
    • redesigned dashboard list controls for greater accessibility

 

Pretty simple steps

Download and save to your SCOM server, or SCOM console connected machine

Navigate to the Administration tab

Expand Management Packs

Click on Installed Management packs

Click the Add drop-down, select the packs

Verify selections, and click Install button

Importing new SQL v7.6.5.0 packs into the SCOM Console

Click Close after import

v7.6.5.0 management packs are imported into the SCOM console

 

Enjoy!