question

gomski avatar image
0 Votes"
gomski asked gomski edited

export multi value with Generic SQL Connecor to a MS SQL Database

Hello dear IdM fellowers,

I am struggling for days now the figure out how to export multiple values through the Generic SQL Connector using MIM2016. I am using only the Sync component without FIM Service or Portal - it is just plain code provisioning.

I am able to import multi value data but it seems to be impossible to export. I have tried it with table/views/stored proc exports but with no success.
The first data row of a multi value entry is exported fine because the foreign key ID is pushed/exported to the database resulting in a correct export. But this works only for the first row because the connector did not populate the foreign key on changes/updates to a multi value data field. Is there any way to force the population/export of a specific field?

this is my database schema
96384-db.png

this is how my Anchor and DN are definined in 'Schema 3' (not visible is the anchor of Employee which is EmployeeId)
96442-schema3.png
96310-schema4.png

this is my attribute mapping. I used a rules extension for group Id to be able to export the Group_Id right after provisioning the group.
96415-attr-map.png

Here is the respective code to map the Group_Id

  void IMASynchronization.MapAttributesForExport (string FlowRuleName, MVEntry mventry, CSEntry csentry)
         {
             switch (FlowRuleName)
             {
                 case "cd.Group:Group_Id<-mv.group:<object-id>":
                     csentry["Group_Id"].StringValue = csentry["Id"].StringValue;
    
                     break;
                 default:
                     throw new EntryPointNotImplementedException();
             }
         }

This is the provision code in the MV extension

 private bool ProvisioningAdGroupToIdmDb(MVEntry mventry, ConnectedMA agent)
         {
             bool result = false;
             string csAnchorName = "GroupName";
             string mvValueName = "cn";
    
             try
             {
                 if (mventry[mvValueName].IsPresent)
                 {
                     Logging.Log("create new connector on " + agent.Name + " for " + mventry[mvValueName].Value);
                     var csentry = agent.Connectors.StartNewConnector("Group");
                     string guid = mventry.ObjectID.ToString();// Guid.NewGuid().ToString();
                     var dn = agent.CreateDN("CN=" + guid).Concat("OBJECT=Group");
                     csentry["Id"].StringValue = guid;
                     //csentry["Group_Id"].StringValue = guid;
                     csentry.DN = dn;
                     csentry[csAnchorName].Value = mventry[mvValueName].Value;
                     csentry.CommitNewConnector();
                     result = true;
                 }
                 else
                 {
                     Logging.Log("WARNING: Cannot provision due to missing cn", false);
                 }
             }
             catch (Exception e)
             {
                 Logging.Log($"ERROR: {e.Message}", false);
             }
    
             return result;
         }

thats my export profile
96402-export-profile.png

This is what I see in a SQL trace when there is an update to the members field (multi value, mapped to Members_Id)

 exec sp_executesql N'Set xact_abort on  INSERT INTO GROUPMEMBERS
  ( [Member_Id]) 
  VALUES  (@P1) ;  Set xact_abort off ',N'@P1 nvarchar(36)',N'fa191b61-1b3a-48de-b895-0fbb7c3a376e'

I can think of the reason why the Group_Id ist not exported because it has not changed but without the Group_Id the INSERT into the GroupMembers table is just half of the thruth.

Can anyone give me a hint or a simple demo on how to achive an export? It would be great because I am out of any ideas on how to approach this.
Many thanks!


microsoft-identity-manager
db.png (77.3 KiB)
schema3.png (20.3 KiB)
schema4.png (26.9 KiB)
export-profile.png (27.1 KiB)
attr-map.png (46.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers