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)

[#1]

Summary

A symmetric key named FNMSAPPortalKey is created using a random complex password in the compliance database when SAP-related functionality in FlexNet Manager Suite is used. However if your SQL Server instance is configured with non-standard password complexity requirements, the random password generated for this purpose may not meet your particular complexity requirements.

This can result in errors occurring when using various SAP-related functionality in the FlexNet Manager Suite web UI. 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 messaging that may be similar to 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 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   [...]
   at DevExpress.Xpo.DB.ConnectionProviderSql.SelectDataSimple(Query query, CriteriaOperatorCollection targets, Boolean includeMetadata)

The following SQL query can be executed against the compliance database to confirm whether a symmetric key named FNMSAPPortalKey exists:

SELECT *
FROM sys.symmetric_keys
WHERE name = 'FNMSAPPortalKey'

Workaround

1. If (a) you are using FlexNet Manager Suite 2021 R1 or earlier, AND (b) the database compatibility level of the compliance database is 130 (SQL Server 2016) or later, temporarily set the compatibility level to 120 (SQL Server 2014). This is required to avoid problems arising due to the following 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. Run the following SQL statements on your compliance database to configure a more complex password than is configured by default for encrypting and decrypting secrets:

   -- Clean existing CertificateKey if any
   IF EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = 'CertificateKey')  
      DROP SYMMETRIC KEY CertificateKey
   
   -- Clean existing FNMSAPPortalKey if any
   IF EXISTS(SELECT * FROM sys.symmetric_keys WHERE name = 'FNMSAPPortalKey')  
      DROP SYMMETRIC KEY FNMSAPPortalKey  
   
   -- Set custom password suffix
   EXECUTE dbo.CreateSymmetricKeyForCustomEncryption
      @MasterKeyPwd = 'your-password-for-SQL-master-key-encryption', -- Specify password to use for encrypting master key in the database (if a master key is not already configured)
      @CertificateName = 'certFNMSAPPortalKey',
      @SymmetricKeyName = 'CertificateKey',
      @CustomPwd = 'your-password-here' -- Specify your complex password here

1. If you changed the database compatibility level at step #1, reset it back to its original value.

Fix status

This issue is still open but not currently scheduled to be addressed.

Other information

Affected components: SAM for SAP, Security, Web UI

Master issue ID: IOJ-1927439

Also known as: ITAM-1245