SCOMCore Addendum – having a strong core makes bigger gains
Updated SCOMCore addendum pack now contains DWDataRP integration, and additional overrides since the last pack posted in 2023. There’s been a lot of updates made since the last update to GitHub. Github Link https://github.com/theKevinJustin/SCOMCoreAddendum
Integration – time to integrate data sources to data lake
Ready for a single pane of glass? Ready to have your insights in a common location? Let’s discuss Data Integration with SQL2022.
Let’s start with some background on SQL2022 and similarly SQL2025, start with the learn site link. SQL2022 by design is Azure enabled with multiple capabilities like ‘Bi-directional HA/DR to Azure SQL’ and ‘Azure Synapse Link’. Basically, Synapse link is the key.
SQL2022 by design is Azure Enabled
Utilize the PowerBI Cloud Service with today’s hybrid environments. SQL2022 allows integration with other Azure capabilities like Azure Data factory/data lake, and Azure Synapse. Another reason to upgrade SQL 2022, is design simplification. However, PowerBI data gateway adds a potential break point (single point of failure). While PowerBI data gateway centralizes all premise data to a central location. In the same way, consolidating data sent to the cloud. When PowerBI data gateway fails, insights and visualizations have stale data (i.e. data NOT transferred for a near real-time display).
Why SQL2022 then?
Connect insights and visualization to justify ‘Data Integration with SQL2022’ scenarios.
SQL2022 built in capability to Azure Synapse Analytics
Use SQL2022 to configure SQL agent jobs which pull SQL scripts from your cloud environment. DevOps and common Azure Storage repository are great advantages for speed of execution.
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.
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.
Click OK
Set SQL Instance permissions for SCOM Admins group
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.
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
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
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.
Unsure your experience, but recently, I’ve run across multiple occurrences where servers show up as ‘not monitored. As a result, I’ve found three distinct sets of issues that might cause ‘not monitored’ status –
This scenario usually entails deleting server from Managed Agents view in Administration tab, where alerts or other details may still exist. The procedure leverages Holman’s orphaned agent blog (tried and true) post from years back to aid cleanup.
2) Do you have packs or connectors extending classes?
3) Rebuilding a server with the same name is a common server occurrence
Related to 1, Holman’s orphaned agent blog to be used to cleanup.
First, let’s test in the Lab. Second, let’s talk about the express lane ‘easy button’. Begin by deleting the Windows Computer orphaned object GUID. Process the Windows Computer object (bottom), followed by the top two (2) are HealthService, and HealthServiceWatcher object properties (see three items highlighted).
16db02 properties
Second piece, marking the Windows Computer GUID for deletion (IsDeleted=1) cleans up nearly ALL properties. See the progress below, how this slight change makes short order of orphaned properties for ‘server’.
Windows Computer object marks all but SCVMM for deletion
Third HealthService & SCVMM objects require manual deletion per GUID.
Note first screenshot shows health service properties marked ‘IsDeleted’ = 1 after manually processing each GUID.
HealthService marked for deletion
Fourth, screenshot shows there the HealthServiceWatcher property is marked for deletion (IsDeleted=1)
HealthServiceWatcher marked for deletion
If you have SCVMM, you will need to repeat for each of the SCVMM properties to clear out the orphans in the DB.
Why – the issue:
Typically, when servers are reimaged, i.e. NOT deleted from SCOM, there are two+ healthservice, HealthServiceWatcher, Windows Computer properties created for each image of example server. Additional properties may show duplicated for any class discoveries that are common to the old and new image.
NOTE: Deleting the current agent may clean up objects for that instance of the discovered server, but NOT the old server image.
The SQL team noted that the newer versions are defaulting Encrypt to be Yes/Mandatory. That is why the new drivers were having an issue. Setting up a certificate in the SQL endpoint would have allowed the connection to work:
Whether you’re building a new SCOM2019, SCOM2022 environment or not, you might be missing these event details, and NOT even know!
It’s been a while for me, and I came across these, so posting for a fresh heads up!
Leverage Holman’s TXT files to keep your logging up to maximum potential! Use the information below to resolve SCOM2016+ SQL SysMessages and 18054 events.
— Source entity of the relationship doesn’t exist.
EXECUTE sp_addmessage @msgnum = 777980002, @msgtext = N’The specified relationship doesn”t have a valid source.’, @severity = 16, @lang = ‘us_english’, @with_log = false, @replace = ‘REPLACE’
GO
— Target entity of the relationship doesn’t exist.
EXECUTE sp_addmessage @msgnum = 777980003, @msgtext = N’The specified relationship doesn”t have a valid target.’, @severity = 16, @lang = ‘us_english’, @with_log = false, @replace = ‘REPLACE’
GO
— Discovery data from invalid managed entity is dropped.
EXECUTE sp_addmessage @msgnum = 777980004, @msgtext = N’Discovery data has been received from a rule targeted to a non-existent entity. The discovery data will be dropped.’, @severity = 16, @lang = ‘us_english’, @with_log = false, @replace = ‘REPLACE’
GO
— Invalid relationship rejected by cycle detection.
EXECUTE sp_addmessage @msgnum = 777980005, @msgtext = N’Relationship {%s} was rejected because it would cause a containment cycle; relationship source = ”%s” and target = ”%s”.’, @severity = 16, @lang = ‘us_english’, @with_log = false, @replace = ‘REPLACE’
GO
— Discovery data generated by invalid connector.
EXECUTE sp_addmessage @msgnum = 777980006, @msgtext = N’Discovery data generated by invalid connector:%s.’, @severity = 16, @lang = ‘us_english’, @with_log = false, @replace = ‘REPLACE’
GO
— Discovery data generated by invalid rule, task, discovery.
EXECUTE sp_addmessage @msgnum = 777980007, @msgtext = N’Discovery data generated by invalid discovery source. Id:%s.’, @severity = 16, @lang = ‘us_english’, @with_log = false, @replace = ‘REPLACE’
GO
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional
Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes.The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
