KB422084: How to fix error with MicroStrategy ODBC Driver for Salesforce Wire Protocol: “[MicroStrategy][ODBC Salesforce driver][Salesforce]The configuration options used to open the database do not match the options used to create the database”

The Strategy ODBC Driver for Salesforce Wire Protocol driver on Windows, UNIX, and Linux requires the creation of an embedded database to store metadata about the Salesforce objects.  The following comes from the DataDirect help documentation :
“The driver automatically maps Salesforce objects and fields to tables and columns the first time it connects to a Salesforce instance. The driver maps both standard and custom objects and includes any relationships defined between objects.
The driver uses a local embedded database to instantiate the mapping of the remote data source objects to tables and the metadata associated with those tables. The driver creates a database per user. The embedded database is created in the directory from which the application is run and uses the user ID specified for the connection as the name of the database. If the user ID contains punctuation or other non-alphanumeric characters, the driver strips those characters from the user ID to form the name of the database. The driver provides connection options that you can use to override the default setting for the name and location of the database (Database). “
If a user makes any changes to the Strategy Salesforce DSN settings after the creation of the local database and then attempts to connect to Salesforce the following message is displayed:
 
Error Message

The configuration options used to open the database do not match the options used to create the database

 
 
UNIX/Linux DSN
 

 
The following are valid values for the ‘Create Database/CreateDB’ setting:

• If set to 0 (No), the driver will check to see if a database exists and if does not find one the connection will fail.   The driver will not attempt to create the locally embedded database.
• If set to 1 (ForceNew), the driver will delete the existing database if it exists and will create a new database for each new connection.
• If set to 2 (NotExist), and the driver detects an existing database it will not attempt to create the database.  If the driver does not find an existing database it will create the database.

The Salesforce DSN that is created using the Strategy Connectivity Wizard has a default value of "2 - NotExist" for the "Create Database/CreateDB" setting.  If a user encounters the error message ““The configuration options used to open the database do not match the options used to create the database” this value can be set to ‘1’ which will force the locally embedded database to be recreated and it will use the new DSN options.  Once all Strategy components are shown to be connecting without this error the DSN should be updated to use a value of ‘2’ so that there is not the overhead of recreating the embedded database for each new connection.
Note:  As the DataDirect documentation mentions, the ODBC driver will check for the embedded database from within the directory the application is executed.  The Strategy components do not all get executed from the same directory so that means there could be several copies of the local database on the Intelligence Server machine.  For example, if after making the change in the DSN, the user only confirms that the Salesforce driver is working with the Strategy DB Query tool and then changes the ‘Create Database/CreateDB’ value back to the default value of ‘2’ they might still see the error message if they open a connection from the Strategy Desktop Warehouse Catalog.  The reason is that the executable for both of these applications is in different directories.  To work around having to modify several databases on the same machine the user can use the ‘Database’ setting in the DSN.
 
How to work around multiple error messages using the ‘Database’ setting
The second DSN setting that can be modified is the ‘Database’ setting.  In Windows you set this value using the 32-bit Microsoft ODBC Administrator to edit the DSN.  In UNIX/Linux you set this in the odbc.ini.
 
Windows DSN
 

 
UNIX/Linux DSN
 

 
Valid values for this setting are:

• If left blank or empty it means that the Salesforce driver will prefix the embedded database files with the User ID value.   For example, if a user connects with User ID JohnDoe@salesforce.com the driver will create all of the database files with the ‘JohnDoe’ prefix.   It will also check for the embedded database in the directory from which an application is run.  If it does not find the embedded database, the driver will create the database (if still using the default value for CreateDB).
• If a prefix is provided the Salesforce ODBC driver will give the embedded database files the provided prefix instead of the User ID.  For example, if a user configures the Database value to ‘MSTRuser’ and connects with User ID JohnDoe@salesforce.com , the driver will create all of the database files with the ‘MSTRuser’ prefix instead of the User ID prefix.  It will also check for the embedded database in the directory from which an application is run.  If it does not find the embedded database, the driver will create the database (if still using the default value for CreateDB).
• If a path and prefix are provided the Salesforce ODBC driver will create the embedded database in the directory provided while using the prefix provided for the database files.  For example, if a user configures the ‘Database’ value to ‘C:\Program Files (x86)\Common Files\MicroStrategy\MSTRuser’, the driver will create the embedded database in the ‘C:\Program Files (x86)\Common Files\Strategy’ directory and use the ‘MSTRuser’ prefix instead of the User ID.   All connections, regardless of from where the application is executed, will look to the provided path + prefix and check for the embedded database only in that directory.

By default, the Salesforce DSN that is created using the Strategy Connectivity Wizard will not have any value for the ‘Database’ setting.  Since Strategy applications are not all executed from the same directory that means the driver will create several copies of the embedded database on the local machine and the database files will all have the User ID as the prefix.  To work around this the user can set an absolute path and prefix using the ‘Database’ setting.  For example, if on Windows and the following is set: C:\Program Files (x86)\Common Files\MicroStrategy\MSTRuser’.  See below:
 

 
The first connection made after the change to the Database setting will look to the C:\Program Files (x86)\Common Files\Strategy’ directory and create the embedded database with the ‘MSTRuser’ prefix.  See below:
 

 
All subsequent connections to Salesforce that use this DSN will also use the same embedded database files.  The same can be done on UNIX/Linux.
KB422084