The FNMSAPPortalKey symmetric key configured in the compliance database uses TRIPLE_DES algorithm.
Use of this algorithm may cause errors when the compliance database is hosted on SQL Server 2016 or later, and the compatibility level is set to 130 or higher. The TRIPLE_DES algorithm may also be considered insecure, and inappropriate for encrypting database symmetric keys.
As per information on the following page, the TRIPLE_DES algorithm is not supported on SQL Server 2016 or later unless the database compatibility level is set to 120 or lower: Choose an Encryption Algorithm
If the FlexNet Manager Suite compliance database is hosted on SQL Server 2016 or later that is configured to use a newer compatibility level, then errors may occur when using various SAP-related functionality in FlexNet Manager Suite. For example, attempting to create a new SAP system in the web UI may fail with the following error:
A Problem Occurred FlexNet Manager Platform detected a problem and could not complete your request. Please refresh the page to try again or contact your IT Administrator for assistance.
Additional information about the "A Problem Occurred" error can be obtained by configuring the following registry entries under the key HKLM\SOFTWARE\Wow6432Node\ManageSoft Corp\ManageSoft\Compliance\CurrentVersion on the FlexNet Manager Suite web server:
With these entries configured, additional details including the following will be shown in the error message:
DevExpress.Xpo.DB.Exceptions.SqlExecutionErrorException: Executing Sql 'CLOSE SYMMETRIC KEY FNMSAPPortalKey' with parameters '' exception 'System.Data.SqlClient.SqlException (0x80131904): Cannot find the symmetric key 'FNMSAPPortalKey', because it does not exist or you do not have permission. [...] at ManageSoft.Compliance.Database.Impl.ComplianceDataEncryption.EncryptString(String p_Value) at ManageSoft.Compliance.Portal.Logic.Impl.SapService.SAPSystemPropertiesSave(PropertyChangeSet p_ChangeSet, IDValueInfoCollection p_RemoteSystems, IDValueInfoCollection p_SAPUserAccounts)
To avoid errors due to the use of the TRIPLE_DES algorithm, configure the compatibility level of the compliance database to 120 (SQL Server 2014) when it is hosted on SQL Server 2016 or later.
This issue has been addressed by changing the symmetric key to be created using the AES_256 algorithm.
Also see the following article which describes another issue with very similar symptoms to the issue described here:
This issue has been fixed in the following FlexNet Manager Suite releases: 2021 R1.4 / Mar 2022 (Cloud), 2022 R1 (On Premises)
Affected components: Database, SAM for SAP, Security
Master issue ID: IOJ-2189563
Also known as: FNML-73505
Mar 08, 2022 01:29 AM - edited Nov 24, 2023 01:27 AM
This issue still persist in 2023 R1 on SQL Server 2022
Is there any patch that could be applied ?
@jakubkania - You might try the workaround suggested in this article to see if it helps. However the code that caused this problem in earlier releases is not included in the 2023 R1 release, so I suspect whatever problem you are encountering does not actually have the same root cause as the issue that is described in this article. You will probably have to troubleshoot further to understand the root cause before working out how to mitigate your specific issue.
Thanks for the reply.
I've already tryied to lower the Compatibility level to 120 with no effect.
Still getting this exception:
DevExpress.Xpo.DB.Exceptions.SqlExecutionErrorException: Executing Sql 'CLOSE SYMMETRIC KEY FNMSAPPortalKey' with parameters '' exception 'System.Data.SqlClient.SqlException (0x80131904): Cannot find the symmetric key 'FNMSAPPortalKey', because it does not exist or you do not have permission.
Is it possible that this key 'FNMSAPPortalKey' from whatever reason does not exist? Where can I verify this?
Thank you,
Jakub
Regarding missing 'FNMSAPPortalKey' key, I can't find it in sys.symmetric_keys table on SQL server. Actually this table is empty.
Is there a way to import those keys - maybe something went wrong during the deployment?
BR/
Jakub
@jakubkania - check out the following article, which describes another issue with almost identical symptoms to the issue discussed in this article:
Hello Chris,
Thank you for that hint, I got the keys created by the porcedure [dbo].[CreateSymmetricKeyForCustomEncryption].
However this procedure has a line at the end that basically drops FNMSAPPortalKey.
So the method ManageSoft.Compliance.Database.Impl.ComplianceDataEncryption.CloseSymmetricKey()
Is looking for something that does not exist in DB.
I took a liberty and commented out the line DROP SYMMETRIC KEY FNMSSAPPortalKey and rerun the procedure. Now the error is different:
DevExpress.Xpo.DB.Exceptions.SqlExecutionErrorException: Executing Sql 'CLOSE SYMMETRIC KEY FNMSAPPortalKey' with parameters '' exception 'System.Data.SqlClient.SqlException (0x80131904): The key 'FNMSAPPortalKey' is not open. Please open the key before using it.
Still this error is returned from the method ManageSoft.Compliance.Database.Impl.ComplianceDataEncryption.CloseSymmetricKey() It looks like the problem is hardcoded somewhere there.
BR,
Jakub
@jakubkania - it appears there is something with your SQL Server setup that is making FlexNet Manager Suite's use of symmetric keys in your SQL Server instance fail. Unfortunately the error messages you see in the UI likely won't give much insight into the real root cause of the problem here. (The message you see about closing the key is a consequence of a previous error detected by the code - but the UI doesn't show information about the previous error.)
If none of the guidance in this article or the other article referenced above help then you may need some more dedicated troubleshooting assistance from Flexera Support.
(NB. Please don't modify the CreateSymmetricKeyForCustomEncryption stored procedure. The "DROP SYMMETRIC KEY" statement is intended to be in there to ensure any existing symmetric key that was configured with different password details from the password details provided when calling the stored procedure has been cleanly removed. The symmetric key will be created when needed.)
Thanks Chris,
We've opened case for that already. Yes something is wrong beyound my ability to toubleshoot it or reverse-engineer:)
BR,
Jakub