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
‎Jan 15, 2020 02:52 AM - edited ‎Jan 15, 2020 02:52 AM
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:
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).
‎Jan 15, 2020 03:43 AM
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)?
‎Jan 15, 2020 03:56 AM
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:
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).
‎Jan 15, 2020 03:43 AM
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)?
‎Jan 15, 2020 03:56 AM
@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.
‎Jan 15, 2020 04:30 AM
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
‎Jan 17, 2020 12:52 PM
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;
‎Jan 21, 2020 06:24 AM
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
‎Feb 08, 2020 05:11 PM
‎Oct 05, 2020 09:46 AM
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.
‎Feb 10, 2021 08:47 AM
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é
‎Jan 28, 2022 04:54 AM
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.
‎Mar 25, 2022 09:16 AM
@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.
‎Mar 25, 2022 12:56 PM
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:
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
‎Apr 08, 2022 08:13 AM - edited ‎Apr 08, 2022 08:34 AM