Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
Configuring the MIM 2016 Microsoft Generic SQL Connector for Oracle Date Time Values
Published Mar 19 2020 07:48 AM 5,202 Views
Microsoft

 

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 USI recently ran into a few issues exporting dates from MIM 2016 to an Oracle 12 data source using the Generic SQL Connector.   

 

When I was attempting to perform delta imports, I was receiving the error "literal does not match type string".  

 

Additionally, when exporting time and date values to an Oracle 12 table, I 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 perform delta imports and to import and export dates to and from Oracle.  

 

Overview: 

The Water Mark Date format that is stored by MIM is defined in the Water Mark Query field located on the Global Parameters page.  This format can be tested in an Oracle query against the Water Mark attribute defined in the MA.  If the Oracle query fails, modify the format to meet the proper Oracle Date Time format used in your implementation.  

 

Below are some examples of water mark formats that have worked in MIM environments:

Oracle 12:   SELECT TO CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS) FROM DUAL

Oracle 9i:  SELECT TO CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS) FROM DUAL

 

Also note that after each change to the water mark query, a Full Import must be run to establish the initial water mark date.  Once established, Delta Imports can be performed.  

 

For imports and exports of date time values, the Generic SQL Connector performs string to date conversions within the management agent code from .NET format to that of the connected SQL data sourceIn 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 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  

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

 

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 

 

Select WaterMark as the delta strategy. 

 

Enter the Water Mark Query (This date format should be tested in an Oracle query against the WaterMark attribute to validate functionality):  In this case the proper Water Mark Query was SELECT TO CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS) FROM DUAL.

 

Specify the Water Mark Attribute.

 

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 

2 Comments
Co-Authors
Version history
Last update:
‎Apr 13 2022 08:38 AM
Updated by: