Configuring the MIM 2016 Microsoft Generic SQL Connector for Oracle Date Time Values

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.

Overview

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.

exported-change-not-reimported 

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 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 guide (link provided below).  This configuration is specific to a Server implementation, nonetheless, I use this documentation as the base configuration for this article.

Microsoft Identity Manager 2016 Generic SQL Connector install guide 

https://docs.microsoft.com/en-us/microsoft-identity-manager/reference/microsoft-identity-manager-201…

Reference: Oracle ODBC Driver details

https://docs.oracle.com/database/121/ADFNS/adfns_odbc.htm#ADFNS1118

Create the Oracle Environment Variables on the FIM Synchronization Server

Launch the 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

Joe_Zinn_0-1584627375057.png

Restart the server.

Reference: Oracle NLS Setting Details

https://www.oracle.com/database/technologies/faq-nls-lang.html

Modify the Configuration of the Microsoft Generic SQL MA

Launch the MIM Synchronization Service Manager client.

Right Click the Generic SQL (Microsoft) connector

Select Properties

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:

dd.MM.yyyy HH:mm:ss

Joe_Zinn_1-1584627375050.png

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:

dd.MM.yyyy HH:mm:ss

Joe_Zinn_2-1584627375051.png

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

case "CS_DATE_STRING":
{
    csentry["CS_DATE_STRING"].Value = mventry["MV_DATE_STRING"].Value +"Z";
    break;
} 

Outbound Synchronization Rule:

Select the Outbound Synchronization Rule

Select Outbound Attribute Flow

Select New Attribute Flow

Select MV_DATE_STRING attribute

Select Concatenate Value

Select string

Enter Z in the text box.

Select Destination

Select CS_DATE_STRING

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.

Joe_Zinn_3-1584627375054.png

Joe_Zinn_4-1584627375055.png

Summary

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.

Best Wishes,

Joe Zinn
Senior Premier Field Engineer
Identity Management | Microsoft Services

Blog Posts: http://aka.ms/JoeZinn

 

This article was originally published by Microsoft's Premier Field Engineering Blog. You can find the original article here.