KB418073: Metadata connection cannot be established due to error `option:ACCESS_TRANSACTION_HACK - invalid attribute/option identifier` with Microstrategy Intelligence Server

SYMPTOM:
After restoring a metadata from an Access file to MS SQL Server and trying to configure the Strategy Intelligence Server, the following error occurs when trying to configure a server definition via the Strategy Configuration Wizard:
 
option:ACCESS_TRANSACTION_HACK - invalid attribute/option identifier
STEPS TO REPRODUCE:
Follow the steps below to reproduce this issue:

• Launch the Configuration Wizard and select 'Set up Strategy Intelligence Server' as shown below:
  

• Select the metadata that was restored in step 1.
• On validating user credentials, the following error occurs:
  

• User authentication failed: Database error <>: Begin DML Transaction failed. Error type: Odbc error. Odbc operation attempted: SQLSetConnectionOption. Option: ACCESS_TRANSACTION_HACK. Invalid attribute/option identifier
  

CAUSE:
This is because the original metadata was created using a different database type. Database type is stored in the 'DSSMDSYSPROP' table in the metadata, as shown below:

This value is not changed when the tables are restored on the MS SQL Server. In general, project duplication is the only supported way to move metadata from one database type to another.
 
ACTION:
Follow the steps below to resolve this issue:

• Create a fresh metadata on the MS SQL Server using the Strategy Configuration Wizard, as shown below:
  

• Create a project source, A, to point to this newly created metadata.
• Create another project source, B, to point to the Access File where the original metadata is stored.
• Perform project duplication by setting A as the destination Project Source and B as the Source. For more information about project duplication, refer to the following Strategy Knowledge Base technical note: KB12330 (KB4100-8X-0391): How to duplicate a project in Strategy Developer

KB418073