cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Compliance Database Upgrade Error (Could Not Load Assembly "sqlproceduresclr") Going From FNMS 2019R1 to 2022R1. I Resolved It But Have A Question.

Hi all ...

I am going through the process of upgrading my FNMS environment from FNMS 2019R1 to 2022R1.  I'm also taking this time to migrate onto faster hardware and from being based on Win2012R2 to Windows Server 2019. I am also migrating the database from SQL Server 2016 to SQL Server 2019.

So the first step was to set up SQL Server 2019, apply CU 16 and then perform the additional setup Flexera requires (enable CLR, turn off six Intelligent Query Optimizer components, etc.).

Next, I backed up and restored the FNMS databases from my production SQL Server 2016 server to the new one.

Then, I copied over the database migration ZIP file, expanded it and started running the migration utilities.

[Generally, this is all described in the Upgrade Guide to FNMS 2022 R1, pages 38 to 47.]

I had no issues upgrading the Inventory Database.

I did have an issue with the Compliance database:  

Running cm-migr1300-ComplianceSchema.sql...100%
        Running cm-migr1300-CompliancePreCLRInstall.sql...50%
                There was an error while attempting to run 'cm-migr1300-CompliancePreCLRInstall.sql'.
                An error occurred in the Microsoft .NET Framework while trying to load assembly id 65538. 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 'sqlproceduresclr, 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)

Others have had this same error as well.  

The entry I pointed to above listed two workarounds for this issue.

(1) One solution was to run the below T-SQL commands, then run the Compliance upgrade utility, then changing TRUSTWORTHY to OFF and rerunning the commands:

ALTER DATABASE [FNMSCompliance] SET TRUSTWORTHY ON
GO
USE [FNMSCompliance]
GO
EXEC sp_changedbowner 'sa'
GO

(2) The other solution implied that you could get the CLR certificate from a previous release, apply it, run the Compliance upgrade until it fails, remove the older certificate, then import the recent one and rerun the upgrade until it finishes. 

The second solution seemed like a lot of extra work, so I tried the first solution (TRUSTWORTHY set to ON while the upgrade runs, then set it OFF) and that worked for me.  The database was upgraded successfully.

However, I'm a little concerned I had to perform this workaround since it isn't officially documented in the upgrade guide.  Are there any potential issues that could occur "down the road" because of how this was accomplished? I ask because this poster had a similar issue trying to run the config.ps1 command and stated "I did try to set Trustworthy as "True" during the Config.ps1 step and while that allowed the script to complete successfully, the application gave redbox errors on nearly every page after setting Trustworthy back to 'False'. "  Granted, the cause of this poster's issue could have been different, but I obviously don't want to install FNMS later on and get redbox errors that tie back to this workaround.

And if this is indeed the proper method of performing a database upgrade across several releases, shouldn't the upgrade guide be modified to reflect this as part of its procedures?

--Mark

 

 

 

 

(1) Reply

I think the potential for future issues depends on how Flexera implements certificates and code signing in future releases.  I believe the problem exists if they have upgrade code signed by old as well as new certificates and the transition isn't effectively managed.  This has been (and still is) a significant gap in their upgrade instructions which caused more than a month of delay/trouble for us.   Anyway, I'm glad the "Trustworthy ON/Upgrade/Trustworthy OFF" process worked for you.