Hello, my name is Joe Zinn and I am a Senior Premier Field Engineer for Microsoft supporting Azure, Identity, AD Connect, FIM and MIM solutions in the US. I recently ran into a few issues exporting dates from MIM 2016 to an Oracle 12 data source using the Generic SQL Connector.
In this case, I was exporting time and date values to an Oracle 12 table and was receiving the errors ORA-01858: a non-numeric character was found where a numeric was expected and ORA-01861: literal does not match format string. Additionally, on import the date values were being imported with a trailing Z which did not match any of the date formats configured.
The following document contains information needed to solve these issues and configure the Microsoft Generic SQL Connector to export dates (in any format) to Oracle.
The Generic SQL Connector performs string to date conversions within the management agent code from .NET format to that of the connected SQL data source. In the case of Generic SQL Connector and Oracle, the NLS format is used to determine what format to send to Oracle. This must match the configured format in the Generic SQL Connector.
If the NLS_LANG and NLS_DATE_FORMAT values are not defined in environment variables, the default NLS_LANG of AMERICAN_AMERICA.US7ASCII and NLS_DATE_FORMAT of DD/MM/YYYY are used.
The default NLS_DATE_FORMAT does not include the time value in the format and therefore will only permit the date value to be exported to Oracle. If a time value is sent to Oracle, one of the following error messages referenced below will occur on export.
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-01861: literal does not match format string
If just the date value is sent the export will work. However, the confirming import will fail with the following error, because the date format imports in the following format 2019-12-25 00:00:00Z.
For those that are transitioning from the former Oracle Management Agent to the Generic SQL Connector, the NLS modifications were not required with the Oracle connector. The string to date value translations were handled within the Oracle management agent and were specific to an Oracle connected data source. In the Generic SQL Connector, the default settings are specific to .NET and Microsoft SQL data sources and therefore must be adjusted for Oracle data sources.
Installation and Configuration:
Install the Oracle client and setup the TNSNAMES.ORA configuration file.
Install and configure the Microsoft Generic SQL Connector following Microsoft’s step-by-step guide (link provided below). This configuration is specific to a Microsoft SQL Server implementation, nonetheless, I use this documentation as the base configuration for this article.
Microsoft Identity Manager 2016 Generic SQL Connector step-by-step install guide
Reference: Oracle ODBC Driver details
Create the Oracle Environment Variables on the FIM Synchronization Server:
Launch the Windows Server Control Panel application
Select Advanced System Settings
Select Environment Variables,
In the System variables pane, select New,
Enter the NLS Format settings that you intend to use. The following is one example that can be used:
Enter the following Variables and Values
NLS_DATE_FORMAT = DD.MM.YYYY HH24:MI:SS
NLS_LANG = AMERICAN_AMERICA.AL32UTF8
Restart the server.
Reference: Oracle NLS Setting Details
Modify the Configuration of the Microsoft Generic SQL MA:
Launch the MIM Synchronization Service Manager client.
Right Click the Generic SQL (Microsoft) connector
Under global parameters,
Change the Data Source Time Zone and set it to the same time zone as the Oracle server, for example (UTC) Coordinated Universal Time.
Change the Data Source Date Time Format (case sensitive) to match the NLS format chosen above. Below is the matching .NET date format that coincides with the Oracle NLS Format noted above:
Under Configure Partitions and Hierarchies, select the Configure button
Select the Partition DSN File used in the initial setup
Enter the Partition User Name and Password
In the Additional Parameters section
Change the Partition Data Source Time Zone to the same time zone as the Oracle server, for example (UTC) Coordinated Universal Time.
Change the Partition Date Time Format (case sensitive) to match the NLS format chosen above. Below is the matching .Net date format that coincides with the Oracle NLS Format noted above:
Configure an export attribute flow via sync rule or rules extension for properly re-importing the Oracle date and time value.
While in our example the date time value in the metaverse is represented in the format ( 2019-12-25 13:01:52 ), when importing dates from Oracle via the Generic SQL MA the date format is imported from Oracle containing a trailing Z and will appear in the connector space as ( 2019-12-25 13:01:52Z ).
In our example, an export flow rule must be created to append the letter Z to the end of the metaverse date time value. This will prevent receipt of the synchronization error exported-change-not–reimported when the confirming import is run.
NOTE: If you choose a different date format from the example used herein, the rules extension or sync rule may need to be modified to support the date format chosen.
Below are examples of a rule extension supporting our example date format and the steps to create a synchronization rule to perform data translations on export:
Simplistic Example of C# Rules Extension:
csentry["CS_DATE_STRING"].Value = mventry["MV_DATE_STRING"].Value +"Z";
Outbound Synchronization Rule:
Select the Outbound Synchronization Rule
Select Outbound Attribute Flow
Select New Attribute Flow
Select MV_DATE_STRING attribute
Select Concatenate Value
Enter Z in the text box.
Select Ok and Ok to save.
Wrapping it up:
Finally, performing an export to Oracle results in a successful export and confirming import.
The following is the screenshot of a successful date export to Oracle using this configuration.
Exporting dates to Oracle using the Generic SQL Connector can be achieved when the environment variables and the date format configured in the Generic SQL MA match. Additionally, you also must account for the trailing Z that accompanies the date value on import in a rule extension or custom synchronization rule.
With this knowledge, moving dates to and from Oracle via the Generic SQL Connector can be achieved.
Senior Premier Field Engineer
Identity Management | Microsoft Services
Blog Posts: http://aka.ms/JoeZinn