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

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

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;
GO

GRANT UNSAFE ASSEMBLY TO FlexeraLogin;
GO

CREATE 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

(2) Solutions
ChrisG
By Community Manager Community Manager
Community Manager

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).

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)

View solution in original post

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)?

View solution in original post

(13) Replies
ChrisG
By Community Manager Community Manager
Community Manager

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).

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)

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)?


@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.

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)

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

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;

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

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!

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.

 

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é

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.

@mfranz one of my colleagues @cdubs76 ran into this issue at his customer recently. He had to do some wonky stuff with the DB migration scripts to get it working correctly. I tagged him and will follow up and see if he can relay what he had to do to get it working.

@mfranz - I had this exact issue when moving from 2020 R1 to 2021 R1.  Prior to migration, I was being "proactive" by putting the new certs in place.  The problem---some of the db migration scripts still have DLLs signed by the old cert, so the db migration scripts were failing moving up through the 16.x versioning.  

I tried replacing the CLR DLL that it was referencing was "untrusted" with newer ones, but that caused other issues.

I had to actually have the DBAs put the OLD EXPIRED certs and logins back in place in the SQL server....run through the migration steps to get it a point where it would fail b/c it was trying to use new DLLs signed by the new ones.  I think the old certs get you up through the last part of the migration that is 16.x.   Then it would fail on migrating from 16.x (16.5 maybe? i can't remember) to 17.0.   

Once it got that far....then i had the DBAs put the new certs back in place, and it would succeed now running the 16.x --> 17.0 migration and finish.   The install would then work successfully.

It was a real fun Friday working through that one...but we did it.

I know, this is actually an old thread on a slightly different issue, but as the discussion went in this direction, I am going to post this here.

I just spent some time playing this through again in my test lab. I can only guess that with the new release, more people will run into this issue. Therefore let's try to structure this a bit for everyone else:

  1. End of '21, early '22, updated packages for FNMS 2021 R1 were released (cert, DB mig & installer). Reason was the certificate used for registering the CLR assembly was no longer valid.
  2. Database migration (e.g. 2020 R2 > 2021 R1) consists of multiple steps, of which some rely on the old certificate, one on the new certificate. To be exact: 16.0 > 16.1 > 16.2 > 16.3 > 16.4 > 16.5 still run with the old certificate. Only 16.5 > 17.0 need the new one.
  3. So when migrating, make sure not to replace the certificate right away. If you did so and need to restore the old cert, you can still take it from the 2020 R2 release (as 2021 R1 downloads do no longer contains the old cert).
  4. If you want to update the certificates, I guess you'll need to remove the "chain" User - Login - Certificate > see code below
  5. Then register the new Certificate, Login, User using the latest package. Be sure to do this only after you ran the early migration steps.
  6. Then re-run the migration and it should finish the last step.

Use this to remove the old User, Login and Certificate:

USE master;
DROP USER FlexeraUser;
DROP LOGIN FlexeraLogin;
DROP CERTIFICATE Flexera;

PS: SET TRUSTWORTHY ON is not a solution, as mentioned above, it works around the actual security feature here.

Edit: Formatting