SCOM 2016 CLR Integration Code Access Security

Dufour, Francois 41 Reputation points
2020-10-20T17:12:31.197+00:00

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,

Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,396 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Leon Laude 85,566 Reputation points
    2020-10-20T17:28:02.997+00:00

    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


  2. SChalakov 10,256 Reputation points MVP
    2020-10-21T10:00:05.27+00:00

    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

    0 comments No comments

  3. Dufour, Francois 41 Reputation points
    2020-10-21T13:49:20.487+00:00

    Thank you for your answers gentlemen

    0 comments No comments