Error ComplianceMigration - FNMS 2019 R2 + SQL Server 2017 - Flexera Signed Security Certificate for SQL Server 2017

January 15, 2020 at 8:52 AM

Hi,

I've trouble installing FNMS 2019 R2 using a SQL Server 2017.

We've copied the .cer file to the SQL server and running the SQL script included in the zip file

USE master;Create CERTIFICATE Flexera    FROM FILE = 'C:\Temp\FlexeraCodeSigning.cer';  GO CREATE LOGIN FlexeraLogin FROM CERTIFICATE Flexera;GOGRANT UNSAFE ASSEMBLY TO FlexeraLogin;GOCREATE USER FlexeraUser FOR LOGIN FlexeraLogin;GO

Running this script without any error.

Next step is the DB migration scripts. Running the Compliance part (mgsDatabaseUpdate.exe -i ComplianceMigration.xml -nsu -dFNMSCompliance - S Server) we've got the following error:

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:

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)        File steps for Migration from 12.4 to 13.0 failed.Update failed at 15.01.2020 09:27:32.Check all displayed messages and log file ComplianceMigration.log for errors before proceding with FlexNet Manager Platform installation.

Could anybody please tell me how to check that the .cer stuff was activated right and how to solve this issue.

Question: Do we have to replace the "FlexeraLogin" or "FlexeraUser" with our service account?

Thanks and Best,

Dennis

FlexNet Manager Suite On-Premises

Top Rated Answers

ChrisG (Flexera Software)
7 years ago
I could be barking up the wrong tree here entirely, but I wonder if the following line of thinking might explain what you are seeing:
It appears you had some earlier version of FNMS installed, and you are trying to upgrade to FNMS 2019 R2.
However before doing the upgrade of FNMS, I'm guessing you have upgraded SQL Server to 2017. I'm assuming SQL Server was upgraded, since FNMS versions prior to the 2019 R2 release did not support SQL Server 2017.
I would not be surprised if this resulted in some sort of inconsistency: you now have an FNMS database from a pre-2019 R2 version installed on SQL Server 2017, which is not a configuration that I would expect to work.
Do you now have a way to try running the database migration scripts with the database hosted on a version of SQL Server that is supported by the FNMS version you are upgrading from? I'm thinking that this may be enough to get the DB migration done, after which you can then move the DB to a SQL Server 2017 instance where it will hopefully operate successfully (since an FNMS 2019 R2 database is supported on a SQL Server 2017 instance).
Expand Post
Selected as Best

All Answers

ChrisG (Flexera Software)
7 years ago
I could be barking up the wrong tree here entirely, but I wonder if the following line of thinking might explain what you are seeing:
It appears you had some earlier version of FNMS installed, and you are trying to upgrade to FNMS 2019 R2.
However before doing the upgrade of FNMS, I'm guessing you have upgraded SQL Server to 2017. I'm assuming SQL Server was upgraded, since FNMS versions prior to the 2019 R2 release did not support SQL Server 2017.
I would not be surprised if this resulted in some sort of inconsistency: you now have an FNMS database from a pre-2019 R2 version installed on SQL Server 2017, which is not a configuration that I would expect to work.
Do you now have a way to try running the database migration scripts with the database hosted on a version of SQL Server that is supported by the FNMS version you are upgrading from? I'm thinking that this may be enough to get the DB migration done, after which you can then move the DB to a SQL Server 2017 instance where it will hopefully operate successfully (since an FNMS 2019 R2 database is supported on a SQL Server 2017 instance).
Expand Post
Selected as Best
- dennis_reinhardt
  7 years ago
  Hi Chris,
  customer is running FNMS 2017 R2 on Windows Server 2012 R2 Std and SQL Server 2014 SP2. They build up a new infrastrucre with Windows Server 2016 Std and SQL Server 2017. We've copied the DBs from the old to the new system and started the migration scripts to update from 2017 R2 to the latest 2019 R2.
  
  Do you now have a way to try running the database migration scripts with the database hosted on a version of SQL Server that is supported by the FNMS version you are upgrading from?
  
  Would it be okay to copy the DBs on the current sql server, migrate and move the DBs to the new SQL2017 server or is a full installation requiered (db migration + application update)?
  Expand Post
  - ChrisG (Flexera Software)
    7 years ago
    "
    
    @dennis_reinhardt wrote:
    
    Would it be okay to copy the DBs on the current sql server, migrate and move the DBs to the new SQL2017 server or is a full installation requiered (db migration + application update)?
    
    "
    Yes, it would be fine to copy the databases to the current SQL Server instance (running on SQL Server 2014 SP2), run the DB migration scripts on the databases hosted there, and then copy the migrated databases back to the SQL Server 2017 instance. You do not need a full installation of the FNMS application server components in order to run the database migration scripts.
    Expand Post
- dennis_reinhardt
  7 years ago
  Hi @ChrisG
  Thanks for your help. As recommended by you I first migrated the DBs on the old system and then copied them to SQL Server 2017 and continued the installation. Setup is done and system is running. Thanks!
  
  If it is still an bug that should be reported so that the manual can be completed or a KB article can be created ?!
  
  Best,
  Dennis
  Expand Post
  - 0_erwinlindemann
    7 years ago
    Hi Dennis,
    For you next migration project: You could try to temporarily turn off CLR strict security on SQL Server 2017 and then re-enable it after your FNMS Database migration scripts have completed?
    EXEC sp_configure 'show advanced options', 1
    
    RECONFIGURE;
    
    EXEC sp_configure 'clr strict security', 0;
    
    RECONFIGURE;
    Expand Post
    pavol_holes
    7 years ago
    Query from @erwinlindemann didn't help me, but running this query helped me:
    ALTER DATABASE [FNMSCompliance] SET TRUSTWORTHY ON
    
    GO
    
    USE [FNMSCompliance]
    
    GO
    
    EXEC sp_changedbowner 'sa'
    
    GO
    
    Source: https://support.microsoft.com/sk-sk/help/918040/you-may-receive-an-error-message-when-you-try-to-run-an-existing-clr-o
    Expand Post
    DawnHoagland (Flexera)
    6 years ago
    Thank you @pavol_holes . It's been a while since I've done a Pre-2018 migration and you just saved my bacon this morning. OUTSTANDING find!
    Expand Post
    adrian_ritz1
    6 years ago
    Thank you @pavol_holes ,
    I run into the same issue today when I made an migration from FNMS 2019 R1 to FNMS 2020 R2, it went smooth till step migrating to version 15.2 when it failed with CLR error, using your work around saved my day.
    
    Expand Post
akuntze
5 years ago
Dear all,
we have detected the same problem while performing a tech refresh to SQL Server 2019. Even if all documented prerequisites were fulfilled, the assembly error appeared. Unfortunately, the workaround as described with the TRUSTWORTHY setting was prohibited by IT security as an insecure measure. Instead we were able to solve that by setting the assembly as trusted using sys.sp_add_trusted_assembly (compare https://nielsberglund.com/2017/07/23/sql-server-2017-sqlclr---whitelisting-assemblies/) .
Hope that helps.
Best regards,
André
Expand Post
- mfranz
  5 years ago
  Ran into the same issue migrating a FNMS 2020 R1 to 2021 R1 in a demo environment. Just removing the old user/login/certificate and re-registering them did not work for me. I have a feeling I need to remove the assembly as well and then have the migration scripts re-register it under the new user/login/certificate.
  Expand Post

10 of 13

Related Product Forums

→ Flexera One

→ Snow Atlas

→ FlexNet Manager

→ Snow License Manager

→ App Broker

Need help finding an answer?

Ask a Question →