Weird SQL issue from SCOM DB move

This post is courtesy of Andres Naranjo

Fix SQL2017+ .NET assembly error

Weird SQL issue from SCOM DB move to new SQL servers

Fix SQL2017+ .NET assembly errors after moving DB’s to new SQL servers.

Scenario: Moved the SCOM 2019 databases from a SQL 2014 database engine to a SQL 2019 database engine.  SQL ApplicationThe following error occurred when opening the SCOM admin console:

Operations Manager Event Log, Event ID 26317

Date: 10/22/2021 11:17:27 AM

Application: Operations Manager

Application Version: 10.19.10505.0

Severity: Error

Message:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly ‘microsoft.enterprisemanagement.sql.userdefineddatatype, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null’ or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)

System.IO.FileLoadException:

at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)

at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)

at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

at System.Reflection.Assembly.Load(String assemblyString)

Fix SQL2017+ .NET assembly

In addition, Operations Manager event ID’s 26317 events document the error (also check SQL Application log, see Holman’s blog).  Here is an example from the Operations Manager event log:

Weird SQL issue from SCOM DB move to new SQL servers

Cause:

Starting with SQL 2017, SQL restricts trusted managed assemblies.

See more details in Microsoft TechNet article here

First, ensure that SQL CLR execution is enabled with the following SQL query:

sp_configure @configname=clr_enabled, @configvalue=1
GO
RECONFIGURE
GO

NOTE: It is important to make sure the SQL Server Service is re-started after the query above.

Second, execute ‘add trusted’ stored procedure queries to mark both as trusted:

EXEC sp_add_trusted_assembly 0xFAC2A8ECA2BE6AD46FBB6EDFB53321240F4D98D199A5A28B4EB3BAD412BEC849B99018D9207CEA045D186CF67B8D06507EA33BFBF9A7A132DC0BB1D756F4F491

EXEC sp_add_trusted_assembly 0xEC312664052DE020D0F9631110AFB4DCDF14F477293E1C5DE8C42D3265F543C92FCF8BC1648FC28E9A0731B3E491BCF1D4A8EB838ED9F0B24AE19057BDDBF6EC

Verify assemblies are successfully registered as trusted run:

Select * from sys.trusted_assemblies

The output should look like this:

At this point, re-start the SCOM services System Center Data Access, and System Center Management Configuration, on all management servers, and re-launch the SCOM admin console to make sure everything is working properly.

Quicker ways to start SCOM services

From PowerShell (as Admin)

restart-service healthservice; restart-service omsdk; restart-service cshost

Leverage Invoke-Command

# Invoke-Command syntax is PoSH remoting is enabled

#

# Run on multiple servers

# From PowerShell on SCOM Mgmt server, where you have same credential/access

# Example 1

“server1”,”server2″| % {invoke-command $_ -scriptblock {$env:ComputerName; restart-service healthservice; restart-service omsdk; restart-service cshost; get-service healthservice; get-service omsdk; get-service cshost }}

# Example 2

# Restart healthservice on MS/Agent

“server1”,”server2″| % {invoke-command $_ -scriptblock {$env:ComputerName; restart-service healthservice; restart-service omsdk; restart-service cshost; get-service healthservice; get-service omsdk; get-service cshost }}