Event 26319 - Assembly Trust Issues with SQL 2017+

Posted by : on

Category : troubleshooting   guides   operationsManager


Thank you to Lorne Sepaugh for the original creation of the KB!

Symptom

I had a customer today that had an issue with being unable to open any SCOM Console without receiving the following error:

Date: 6/8/2022 1:57:24 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)

This error is due to a change in the SQL 2017 security approach for CLR, as stated in the SQL Server 2017 docs:
CLR SQL 2017 documentation highlight

There are two assemblies used by SCOM that are marked as UNSAFE and not allowed to run by default in one of these scenarios - as such we need to mark them as safe and trusted on each server instance. The assemblies are:

  • Microsoft.EnterpriseManagement.SQL.DataAccessLayer
  • Microsoft.EnterpriseManagement.SQL.UserDefinedDataType

How to Resolve

Prerequisites

*If using availability groups, this is to be completed after the databases are added

  • Ensure that CLR is enabled on all SQL Server instances with this script:
    Sp_configure 'show advanced options', 1; 
    GO 
    RECONFIGURE; 
    GO 
    Sp_configure 'clr enabled', 1; 
    GO 
    RECONFIGURE; 
    GO
    
  • Make sure that you have SQL admin access, or a DBA on hand
  • (Optional) Stop all the SCOM services on each management server - don’t forget to restart when finished
  • Make sure you have a database backup

Step 1: Verify CLR Strict Security State

Run this query in SQL Management Studio:

SELECT * FROM sys.configurations WHERE name LIKE 'clr strict security'; 

You should get a return like this, “value_in_use” and “value” should be 1:
Example showing sys.configurations value_in_use

This table describes what the values mean:

Value Description
0 Disabled - Provided for backwards compatibility. Disabled value is not recommended
1 Enabled - Causes the Database Engine to ignore the PERMISSION_SET information on the assemblies, and always interpret them as UNSAFE. Enabled is the default value for SQL Server 2017 (14.x)

 

Note

By default, CLR strict security will be OFF after upgrading to SQL Server 2017

If the value is 0 - check this doc for more info on how to set it to 1 - https://docs.microsoft.com/sql/database-engine/configure-windows/clr-strict-security?view=sql-server-2017

Step 2: Create the Trusted Assemblies

To create the Trusted Assemblies, run the below TSQL Query on each SQL 2017+ instance(s) hosting the Operations Manager Database:

USE master;
GO

-- First Trusted Assembly
DECLARE @clrName1 nvarchar(4000) = 'Microsoft.EnterpriseManagement.Sql.DataAccessLayer'
PRINT N'Trusted Assembly: ' + CAST(@clrName1 AS nvarchar(120))
DECLARE @hash1 varbinary(64) = 0xEC312664052DE020D0F9631110AFB4DCDF14F477293E1C5DE8C42D3265F543C92FCF8BC1648FC28E9A0731B3E491BCF1D4A8EB838ED9F0B24AE19057BDDBF6EC;

-- Drop trusted assembly if exists
IF EXISTS (select * from sys.trusted_assemblies where description = @clrName1)
BEGIN
PRINT N' - Dropping Trusted Assembly'
EXEC SYS.sp_drop_trusted_assembly @hash1
END

--Add to trusted assembly
PRINT N' - Adding Trusted Assembly'
EXEC sys.sp_add_trusted_assembly @hash = @hash1,
                                 @description = @clrName1;

PRINT N' '
-- Second Trusted Assembly
DECLARE @clrName2 nvarchar(4000) = 'Microsoft.EnterpriseManagement.Sql.UserDefinedDataType'
PRINT N'Trusted Assembly: ' + CAST(@clrName2 AS nvarchar(120))
DECLARE @hash2 varbinary(64) = 0xFAC2A8ECA2BE6AD46FBB6EDFB53321240F4D98D199A5A28B4EB3BAD412BEC849B99018D9207CEA045D186CF67B8D06507EA33BFBF9A7A132DC0BB1D756F4F491;

-- Drop trusted assembly if exists
IF EXISTS (select * from sys.trusted_assemblies where description = @clrName2)
BEGIN
PRINT N' - Dropping Trusted Assembly'
EXEC SYS.sp_drop_trusted_assembly @hash2
END

--Add to trusted assembly
PRINT N' - Adding Trusted Assembly'
EXEC sys.sp_add_trusted_assembly @hash = @hash2,
                                 @description = @clrName2;

You can verify the currently trusted assemblies with the following query:

USE OperationsManager;
GO
SELECT * FROM sys.assemblies
SELECT * FROM sys.trusted_assemblies

Once done on all SQL Server instance(s) that host the Operations Manager Database, restart the SCOM Console on the management servers and everything should load correctly.

Page Views


Share on:
About Blake Drumm
Blake Drumm

I like to collaborate and work on projects. My skills with Powershell allow me to quickly develop automated solutions to suite my customers, and my own needs.

Email : [email protected]

Website : https://blakedrumm.com

About Blake Drumm

This is the personal technical blog for Blake Drumm. Currently primarily focused towards Microsoft System Center Enterprise Management Products. I am an Microsoft Support Engineer on the System Center North America Support Team. I am new to blogging. July 6th, 2021 marks my first day as a Microsoft FTE converting from a contractor. I had been working as a contractor since January 2020. Continue to check back for new posts or tips. I like to continually update this page as time permits.

Follow @blakedrumm
Useful Links