FNMS Databases moved from SQL 2012 to SQL 2017, Config fails with "System.IO.FileLoadException: Could not load file or assembly 'sqlproceduresclr..."

[darren_haehnel]

We are running FNMS 2021 R1 on SQL 2012 and are attempting to move just the FNMS* databases to a newly provisioned SQL 2017 server.  We have restored the databases onto the new SQL 2017 server and executed the script to create the new FlexeraLogin, Flexera certificate, and FlexeraUser.  We also updated the ...\Reporter\CurrentVersion registry keys and ComplianceConnection table values to reflect the new db server.  We are now attempting to run the Config powershell script on the application server but receive a permissions issue running the "sqlproceduresclr" assembly.  The step that encounters the error is  "Executing step Configure FNMP database for operators".  The exact error is:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Error 10314, Level 16, State 11, Procedure ResetCognosRole, Line 16, Message: An error occurred in the Microsoft .NET Framework while trying to load assembly id 65561. 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)

We are NOT upgrading FNMS at this time.  Also, our 2021 R1 installation used the initial released installation source (there was an updated set of 2021 R1 installation source released after our upgrade was complete).  We do have a case open with support for this issue but the process has been slow. 

We are working through this issue in our DEV environment and if we enable "Trustworthy = True", the application and the script works fine.  However, we know this isn't the desired configuration so we must identify the issue preventing the assembly from executing with "Trustworthy = False".

Has anyone else encountered this or a similar assembly permissions issue?   

[jasonlu]

Hi, my thought here is to check that you've got all the prereqs covered on the new database.

Is CLR enabled?

j

[darren_haehnel]

Thank you.  We have checked all the prereqs that I can think of.  CLR is enabled as well as CLR strict security.  

[stefange]

Hi,

I had the same issue and the only solution was to change the db owner of the compliance database to the sa account

USE [FNM_Compliance]

GO

exec sp_changedbowner 'sa'

GO

Also enabled trustworthy for the Compliance database but disabled it after the upgrade.

I would suggest changing the DB owner first and check if that helps you

Stefan

[darren_haehnel]

Thanks Stefan,

We have tested this while the db owner is 'sa' and also the ID we use during application configuration.  We get the same results.  

I am currently trying to determine if the sqlproceduresclr assembly we have in our database is actually signed at all.  We upgraded to 2021R1 in August/Sept 2021 and Flexera released new installers in January of 22 that I think may include the signed code.  We had not yet re-installed using the newest installers.  

Also, 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".  

[stefange]

Hi,

Flexera published for the Database migration scripts and database certificate a V2 version in FNMS version 2021 R1

Stefan

[darren_haehnel]

Thanks Stefan.  I was really hopeful this was the source of our issue.  However, even after running through the new mgsDatabaseUpdate.exe command and updating the application on our App server, we still get the same assembly permissions issue.  

Our assemblies now have a modified date of 7/7/2022 which is the date that I ran the mgsDatabaseUpdate.exe command.  They have the "permission_set_desc" as "SAFE_ACCESS".  I have to assume this is correct.

The next area I'm going to investigate is the MSSQLSERVER service ID.  There is a difference in the account used to run the service between the old and new database servers.  According to this Microsoft article, it could be one of our issues.

https://docs.microsoft.com/en-us/troubleshoot/sql/admin/error-run-clr-object-create-assembly

[darren_haehnel · Accepted solution]

As it turns out our database did not have the latest signed SqlProceduresClr assembly installed even after re-running the latest 2021 R1 database upgrade scripts.  Kudo's go to Brad Wong who helped us get the old assembly deleted and the new assembly installed.