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

  • Question

  • I am trying to configure a fresh install of SCOM 2019 using 2 new SQL 2017 servers running Windows Server 2019. I want to use SQL AlwaysOn Availability Groups for the SCOM databases. I ran the SCOM install using the SQL AlwaysOn Listener (rather than the real hostname), added the second SQL node into the Availability Group after the install was complete, and synchronized the server level user accounts between both SQL nodes.

    The SCOM console opens fine when SQL Node 1 (the SQL server that the initial install was run on) is the AO Primary, but when I fail the database over to SQL Node 2, the console fails to load. The initial error said it could not run CLR Assemblies. I changed the "CLR Enabled" option on Node 2 to enable CLR. That cleared up the initial error, but now I have a new error saying that it can't load the "microsoft.enterprisemanagement.sql.userdefineddatatype" assembly. I found an unrelated article that mentioned changing the permission of the assembly from "Safe" to "External Access", which I did, but I still get the same error.

    This only happens on Node 2, the SQL Server that did not perform the initial SCOM install. Anytime I fail the database back over to Node 1, the console opens fine. I'm definitely missing some kind of configuration on Node 2, but I can't figure out what. Can someone please help?

    Actual error text:

    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)

    Tuesday, June 4, 2019 12:10 AM

Answers

  • I'm not seeing that exact Event ID in the Application Log, but I am seeing Event ID 10314, which has exactly the same error text as my original post. I looked up that Event ID and I found this article:

    https://stackoverflow.com/questions/72281/fileloadexception-msg-10314-error-running-clr-stored-procedure

    Which is essentially the same thing as was provided by Crystal above, with one addition:

    USE database_name
    GO
    
    EXEC sp_changedbowner 'sa'
    ALTER DATABASE database_name SET TRUSTWORTHY ON

    I've already changed the DB Owner, so I just ran the Alter Database command on SQL Node 2, and it works!

    That being said... I ran this...

    select name, is_trustworthy_on from sys.databases

    ...on both nodes and saw that SQL Node 2 now shows the OperationsManager database as "Trustworthy", but SQL Node 1 does not, even though the SCOM console opens just fine on both nodes now. I don't love having different configurations on SQL servers that are supposed to be identical. I'm not sure what this "Trustworthy" setting is for. So is it ok to leave the servers configured this way? Or is this just a bandaid? What is best practices concerning this "Trustworthy" setting? In other words, do I need to continue to troubleshoot this issue? Or is it fine to leave as is?

    Wednesday, June 5, 2019 1:36 AM

All replies

  • Hi

     

    For the SQL error message. I find an article mentioned this.  It can be caused because the Stored Procedure that was executing was CLR enabled thus it has the external_access or unsafe permission set from the database. And the login was not same . Thus it was not allowing to execute the CLR SP and resulting into error due to safety concerns.

     

    Please try the steps in the following article and see if it is working.

    https://support.microsoft.com/en-us/help/918040/you-may-receive-an-error-message-when-you-try-to-run-an-existing-clr-o  

     

    Hope the information can help.

     

    Best regards.

    Crystal


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, June 4, 2019 2:07 AM
  • Different logins on different servers sounds like a very reasonable explanation for this behavior. However I tried the workaround in the link you provided (changing the database owner to sa), and it had no effect. (I had to run the procedure on both SQL nodes, strangely enough).

    I did notice another interesting anomaly in the process though... I mentioned in my original post that I changed the permissions on the assembly to "External Access" as part of my original troubleshooting. In an effort to not make too many changes at once, I changed it back to the default "Safe" setting before changing the database owner. When that didn't work I tried changing the permissions back to "External Access" again. What is strange is that it only let me make that change when SQL Node 1 is the primary. When Node 2 is the primary, I get the following error:

    Alter failed for SqlAssembly 'Microsoft.EnterpriseManagement.Sql.UserDefinedDataType'.  (Microsoft.SqlServer.Smo)

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    ALTER ASSEMBLY for assembly 'Microsoft.EnterpriseManagement.Sql.UserDefinedDataType' failed because assembly 'Microsoft.EnterpriseManagement.Sql.UserDefinedDataType' is not trusted. The assembly is trusted when either of the following is true: the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission, or the assembly is trusted using sp_add_trusted_assembly. (Microsoft SQL Server, Error: 10327)
    Tuesday, June 4, 2019 4:07 PM
  • Hi,

    Are you receiving 18054 events in the SQL Application Log?

    Maybe this could help:
    https://kevinholman.com/2017/08/27/scom-2016-event-18054-errors-in-the-sql-application-log/


    "When SCOM is installed – it doesn’t just create the databases on the SQL instance – it adds data to the sysmessages view for different error scenarios, to the Master database for the instance.

    This is why after moving a database, or restoring a DB backup to a rebuilt SQL server, or when using SQL AlwaysOn and failing over to another instance – we might end up missing this data.

    These are important because they give very good detailed data about the error and how to resolve it.  If you see these – you need to update your SQL instance with some scripts.  Or – if you KNOW you are using SQL AlwaysOn, or migrating a DB – be PROACTIVE and handle this manually, up front."



    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    Tuesday, June 4, 2019 9:47 PM
  • I'm not seeing that exact Event ID in the Application Log, but I am seeing Event ID 10314, which has exactly the same error text as my original post. I looked up that Event ID and I found this article:

    https://stackoverflow.com/questions/72281/fileloadexception-msg-10314-error-running-clr-stored-procedure

    Which is essentially the same thing as was provided by Crystal above, with one addition:

    USE database_name
    GO
    
    EXEC sp_changedbowner 'sa'
    ALTER DATABASE database_name SET TRUSTWORTHY ON

    I've already changed the DB Owner, so I just ran the Alter Database command on SQL Node 2, and it works!

    That being said... I ran this...

    select name, is_trustworthy_on from sys.databases

    ...on both nodes and saw that SQL Node 2 now shows the OperationsManager database as "Trustworthy", but SQL Node 1 does not, even though the SCOM console opens just fine on both nodes now. I don't love having different configurations on SQL servers that are supposed to be identical. I'm not sure what this "Trustworthy" setting is for. So is it ok to leave the servers configured this way? Or is this just a bandaid? What is best practices concerning this "Trustworthy" setting? In other words, do I need to continue to troubleshoot this issue? Or is it fine to leave as is?

    Wednesday, June 5, 2019 1:36 AM
  • The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it. By default, this setting is OFF, but can be set to ON by using the ALTER DATABASE statement.

    So I believe this should be set to ON as you have done, this should be enough for the databases to be able to fail over.

    For your original Microsoft .NET Framework error 65537, here's a post that explains it:
    https://www.sqlshack.com/impact-clr-strict-security-configuration-setting-sql-server-2017

    This also states to enable the trustworthy feature and a few more things.


    Blog: https://thesystemcenterblog.com LinkedIn:

    Wednesday, June 5, 2019 5:52 AM
  • I knew about the "CLR Strict Security" feature added to SQL 2017. I tried disabling that on both nodes, and it didn't make any difference, so I turned it back on again. It was only when I turned that "Is Trustworthy" feature on for Node 2 that actually got it working properly. So at the moment, I have two different configurations on two different SQL nodes, and that concerns me a bit:

    Node 1 (Original SCOM install): CLR Strict Security: On, Trustworthy: Off
    Node 2: (Added to AO after install): CLR Strict Security: On, Trustworthy: On

    Everything works fine in the current configuration, but I'm curious why one SQL node needs to have the "Is Trustworthy" feature enabled and the other one doesn't. This is kind of an obscure setting as far as my experience is concerned, and I'm worried about leaving these servers configured differently when they are supposed to be identical servers. Is this going to come back and bite me later? This is a highly secure environment, is it best practice to leave that "Is Trustworthy" feature enabled like that?

    Wednesday, June 5, 2019 6:42 PM
  • I haven't used an SQL Always On for the SCOM databases so I'm afraid I can't tell, but it doesn't seem right to me.

    Here's an article I found for configuring SCOM on SQL AlwaysOn:
    https://www.tech-coffee.net/alwayson-part-8-methods-to-add-database-scom

    Here's also a very detailed guide:
    https://gallery.technet.microsoft.com/SQL-2012-and-System-Center-553b5161
    (same principles should apply to newer System Center)


    Blog: https://thesystemcenterblog.com LinkedIn:

    Wednesday, June 5, 2019 7:10 PM
  • Interesting. The first link you provided doesn't mention anything about CLR or changing the DB owner or anything. The second link is enormous, so I just did a search of the PDF for key words that fixed my issue and didn't find anything. It doesn't seem right to me either, that a fresh install of a supported configuration of Microsoft's own products would require as much tinkering as it did in order to get it working. Oh well, at least it's working. Hopefully this post will help someone else. Thank you for all your help!
    Thursday, June 6, 2019 2:46 AM
  • Hi!

    I had the same problem, new SCOM 2019 installed on SQL 2019 AlwaysOn Availability Groups and got the exact same error when databases was running on Node 2, so many thanks for pointing me in the right direction! I think I found a different solution to the problem instead of setting the whole database as Trustworthy. After stumbling across the excellent blog post by Niels Berglund  (https://nielsberglund.com/2017/07/23/sql-server-2017-sqlclr---whitelisting-assemblies/)  I though, maybe this is how the SCOM installation program does it, by whitelisting the assemblies it installs in the OperationsManager database? So by listing whitelisted assemblies on Node 1 I verified that the two SCOM assemblies are in fact whitelisted. 

    Code:

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

    So with that info I set out to whitelist them on Node 2 as well and came up with the following code (which I'm sure an SQL expert can improve upon)

    Code:

    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
    

    After the whitelisting the error is gone and the console works as expected.

    I also stumbled on an other issue that you might as well have, apparently SQL Service broker isn't enabled correctly when installed onto an Availability Group which will cause all sort of troubles with tasks in SCOM. To solve that I had to take the OperationsManager DB out of the AG Group and then enable SQL Service Broker and add it back again.

    Code:

    SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'
    ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    ALTER DATABASE OperationsManager SET ENABLE_BROKER
    ALTER DATABASE OperationsManager SET MULTI_USER
    SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'




    Tuesday, July 9, 2019 2:19 PM