Known Issue: Errors may be reported when using SAP-related functions in the web UI due to failed configuration of the FNMSAPPortalKey symmetric key in the compliance database because the TRIPLE_DES encryption algorithm is used (IOJ-2189563)

[#1]

Summary

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.

Details

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.

Troubleshooting

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:

• DisplayInsecureErrorMessages = true
• DisplayErrorStackTrace = true

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)

Workaround

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.

Fix details

This issue has been addressed by changing the symmetric key to be created using the AES_256 algorithm.

Related issues

Also see the following article which describes another issue with very similar symptoms to the issue described here:

https://community.flexera.com/t5/FlexNet-Manager-Suite-Known/Known-Issue-Errors-may-be-reported-when-using-SAP-related/ta-p/299877.

Fix status

This issue has been fixed in the following FlexNet Manager Suite releases: 2021 R1.4 / Mar 2022 (Cloud), 2022 R1 (On Premises)

Other information

Affected components: Database, SAM for SAP, Security

Master issue ID: IOJ-2189563

Also known as: FNML-73505

[jakubkania]

This issue still persist in 2023 R1 on SQL Server 2022

Is there any patch that could be applied ? 

[ChrisG]

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

[jakubkania]

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

[jakubkania]

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

[ChrisG]

@jakubkania - check out the following article, which describes another issue with almost identical symptoms to the issue discussed in this article:

Known Issue: Errors may be reported when using SAP-related functions in the web UI due to failed configuration of the FNMSAPPortalKey symmetric key in the compliance database if non-default SQL Server password complexity requirements are configured (IOJ-1927439)

[jakubkania]

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

[ChrisG]

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

[jakubkania]

Thanks Chris,

We've opened case for that already. Yes something is wrong beyound my ability to toubleshoot it or reverse-engineer:)

BR,

Jakub