Known Issue: Database migration may fail if database is restored on to a SQL Server 2017 (or later) instance that does not have the appropriate certificate installed: "CREATE or ALTER ASSEMBLY for assembly 'SqlProceduresClr' [...] failed [...]" (IOK-1001941)

[#1]

Summary

When FlexNet Manager Suite is upgraded to 2022 R1 or later and Microsoft SQL Server was upgraded to SQL Server 2017 or later, the following error may occur:

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)

Diagnosis

The issue occurs because the older versions of SQLProceduresCLR.dll are signed with an older Flexera code signing certificate which is now expired. The newer versions are signed with an updated certificate, which the customer may have already imported into the database server.

Since the old certificate may not exist in the database, the SQL Server treats that assembly as "not secure" and it does not allow the installation.

Solution / Workaround

The solution is to add the old certificate to the database. This needs the old as well as the new code signing certificates in the database, so that the migration runs successfully.

1. If only the latest Flexera code signing certificate is imported on the database server:
   Download the Old Flexera Signed Security Certificate 2021 for SQL Server 2017 and 2019.zip and follow the steps mentioned in the readme.txt.

1. If no Flexera code signing certificate is imported on the database server:
   Download the All Flexera Signed Security Certificates for SQL Server 2017 and 2019.zip and follow the steps mentioned in the readme.txt.

Fix status

This issue is under consideration to be addressed in a future FlexNet Manager Suite release.

Other information

Affected components: Database

Master issue ID: IOK-1001941

Also known as: ITAM-677