SCOM 2016 CLR Integration Code Access Security

Question

Hi all,

First time I'm facing such a question...

One of my customers wants to know what is the code access security used for CLR Integration on the SQL Server(s) in a SCOM 2016 deployment. From my reading I can see it can be SAFE, EXTERNAL_ACCESS or UNSAFE. I believe it is SAFE but how can i get a confirmation ?

Thanks in advance for any help.

Best regards,

Answer 1

Hi anonymous user,

What SQL Server version are you running?

CLR uses Code Access Security (CAS) in the .NET Framework, which is no longer supported as a security boundary. A CLR assembly created with PERMISSION_SET = SAFE may be able to access external system resources, call unmanaged code, and acquire sysadmin privileges.
Beginning with SQL Server 2017 (14.x), an sp_configure option called clr strict security is introduced to enhance the security of CLR assemblies. clr strict security is enabled by default, and treats SAFE and EXTERNAL_ACCESS assemblies as if they were marked UNSAFE. The clr strict security option can be disabled for backward compatibility, but this is not recommended. Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database.

Reference:
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/clr-strict-security?view=sql-server-ver15

----------

(If the reply was helpful please don't forget to upvote or accept as answer, thank you)

Best regards,
Leon

Answer 2

Hi anonymous user,

it grants the SAFE permissions to the clr assemblies, but this also depends on the SQL settings (clr strict security). I had the case some days ago, that I had to trust some of the internal SCOM assemblies to get it going. Exactly as described here:

Could not load file or assembly 'microsoft.enterprisemanagement.sql.userdefineddatatype' in an AlwaysOn Availability Group configuration.

so I had to trust them, using the code fragment, shared by the user in the forums:

USE master;  
GO  
DECLARE @clrName1 nvarchar(4000) = 'Microsoft.EnterpriseManagement.Sql.DataAccessLayer'  
DECLARE @hash1 varbinary(64) = 0xEC312664052DE020D0F9631110AFB4DCDF14F477293E1C5DE8C42D3265F543C92FCF8BC1648FC28E9A0731B3E491BCF1D4A8EB838ED9F0B24AE19057BDDBF6EC;  
EXEC sys.sp_add_trusted_assembly @hash = @hash1,  
                                 @description = @clrName1;  
    
DECLARE @clrName2 nvarchar(4000) = 'Microsoft.EnterpriseManagement.Sql.UserDefinedDataType'  
DECLARE @hash2 varbinary(64) = 0xFAC2A8ECA2BE6AD46FBB6EDFB53321240F4D98D199A5A28B4EB3BAD412BEC849B99018D9207CEA045D186CF67B8D06507EA33BFBF9A7A132DC0BB1D756F4F491;  
EXEC sys.sp_add_trusted_assembly @hash = @hash2,  
                                 @description = @clrName2;    
    
USE OperationsManager;  
GO  
SELECT * FROM sys.assemblies  
SELECT * FROM sys.trusted_assemblies  

I hope I was able to help you!

----------

(If the reply was helpful please don't forget to upvote or accept as answer, thank you)
Regards,
Stoyan

Answer 3

Thank you for your answers gentlemen