Hi,

Spent so much time on FIM already and still cant get the basics right ....

Got FIM portal to sync with Metaverse and into AD - yay...finally !

I am now trying to synch this data with a test SQL database (trying to populate the SQL database) - which is not working.

I have a SQL database (called HR) - which has 3 columns (displayname, firstname, surname).

I created a SQL MA, connected and made the 'displayname' the anchor. Then created Run Profiles (Full Import, Full Sync, Export).

Then created a SET, for all Users - which works fine.

Then created a Sync Rule for SQL users: data flow direction -> outbound; type: person, external system: SQL MA; relationship criteria: displayname; outbound attribute flow for: displayname, firstname, surname

Then I created Workflows - Add SQL users; Delete SQl users

Then glued it all together using an MPR (one to provision, the other to deprovision).

I used the same tactic as the FIM-MV-AD process...however NO data is being populated into the SQL database.

There are no errors, all the export statistics are 0, and the SQL Connector Space is empty.

How do you even troubleshoot this product?

thanks

Are objects being created in the connector space for the SQL MA? Is the sync rule getting attached to the person objects?

IMO it's much easier to troubleshoot classic sync and provisioning rules...

Hi,

I can see my new provision rules in the CS of the FIM MA...but not in the SQL MA CS.

Yep, troubleshooting this thing, is something else...

• Edited by Moritz Anders Wednesday, November 10, 2010 2:54 PM typo

How are you defining the anchor?  It looks to me like you're not.  Can you create an identity column in SQL and then an initial [outbound] flow dn --> csObjectId?

I have recreated everything from scratch again.

attributes I am now trying to export is this:

• objectID -> csObjectID (as per your recommendations, and this is also the anchor)
• displayname -> displayname
• firstname -> firstname
• surname -> surname

I have recreated the Set, workflow, MPR - using principles that do work and sync the existing FIM Portal -> FIM MV -> AD

can anyone shed some light why my 4 attributes do not get to SQL, or even the SQL connector space? I am getting no errors in FIM; how does one troubleshoot this product? is there a guide on getting data from FIM MV to an external system like SQL anywhere?

It's just double-checking, but I don't think you answered my question about about whether the sync rule is actually getting attached to the person object via the ERE. What do you see on the Provisioning tab of the person in FIM? What do you see in the metaverse?

I found this step-by-step for exporting to a TEXT file; and it works 100% (http://technet.microsoft.com/en-us/library/ee534904(WS.10).aspx)

I followed all these steps again, the only difference - its a SQL MA...

But it still does not work :-(

When I do a Sync on the FIM MA; it shows Outbound Synchronisation to FIM MA, AD MA, FILE MA...but not SQL MA...frustrating

S.Kwan,

Can you check the provisioning tab of a user you except to see in SQL and verify if the outbound sync rule for SQL is displayed here with pending add status? If you see this, then the sync rule is in the MV and is attempting to apply to this object.

If you don't see this there, verify that that the sync rule for SQL outbound is in the MV. You can do this by going to MV Search, setting scope to SynchronizationRule and verify that SQL Outbound Sync rule is present in the MV.

If you see the value on the provisioning tab but this still isn't working, it would seem likely at this point that there is problem with your outbound sync rule and even though it is applying to the object, there is something wrong it that prevents it from applying. Verify that you have initial flow being used for attribute that you stated is anchor.

Can you check the provisioning tab of a user you except to see in SQL and verify if the outbound sync rule for SQL is displayed here with pending add status? If you see this, then the sync rule is in the MV and is attempting to apply to this object.

Hi,

Where do I check the above thing please? where is the "provisioning tab of a user..."?

Secondly, if I search the MV, I do see the "SQL Sync Rule".

Thanks

Hi,

Still no luck getting any data from FIM MV to SQL (HR).

Does anyone have a step-by-step guide for the old MIIS/ILM method of doing this please - I do not think we will use the FIM Portal functionality anymore.

thanks

Well

Just a question about the entries you provision in SQL: Do you populate the mandatory attributes in SQL ?

You know, as when you provision user accounts in LDAP and so must populate a DN value and all other mandatory attributes such UserAccountControl or unicodePwd in case of Active Directory, I believe that you need in case of provisioning to SQL declare the mandatory attrubtes like GUID (in SQL and not in CS)

It was a long time now I didn't provision something to a DB but I m not sure that the SQL MA generate the GUID for you, and depending how you declare the primary key in your table you must respect the unicity and attribute format to succesfuly create an entry into the DB

Do you see something wrong in the SQL log ?

My feeling suppose you validate all remarks from this thread and succeed to have an export run from the CS to the SQL DB. As you ask, you will find the "provisioning tab" into the edit form of a User into the Portal, just double click on a User and go to the last tab. If you correctly set a MPR firing the provisioning WF adding the resource to your Sync Rule, you will see an ERE (Expected Rule Entry) in this list. Notice that you must import ERE attribute from FIM to MV for the Person object in the FIMMA configuration.

Hope this helps

Fabrice

You may be unto something...where would I find these "mandatory SQL attributes"?

Found the Provisioning tab, thanks.

The ERE for "HR SQL" Synch Rule is set to "ADD"

The mandatory attributes are these taking part to the primary key of the schema of your SQL table

In the begining of this thread you talk about a table in your SQL DB with following collumns: displayname, firstname, surname

You said that in the SQLMA configuration you set the displayName as Anchor, but is the attribute displayName is the primary key of your SQL table ? Do you create manually this table in your database ?

As your object are not populate into the CS of the SQLMA, and you have an ERE for your User (confirm that your MPR and WF is working), I believe that you forget to flow the ExpectedRuleList attribute from FIM (Data Source) to the MV in the FIMMA cofiguration.

Another explanation is (I can't remember how work exactly the SQL MA) that the CS for the SQLMA is not created. To create the CS you have to do a staging: Full Import. Just like for the LDAP MA that need to be created before provision work as the root of an LDAP directory is part of the DN.

Hope this helps

yes, the SQL database I created manually, then I created the columns (manually) as follows:

• EmployeeID
• Displayname
• Firstname
• Surname

EmployeeID has been set as the Anchor in FIM.

I have now deleted all bits and pieces in the FIM Portal, and trying to simply synch data from FIM MV to SQL using the old Sync Service Manager (avoiding the FIM Portal confusion). I have worked thru the SQL MA - but even by just using the old style technique, no data is being populated in SQL either.

Been looking, but have not found any walk-throughs on how to get data from FIM MV to a SQL database.

found this walk thru: http://technet.microsoft.com/en-us/library/cc720650(WS.10).aspx

old MIIS & classic provisioning hopefully will do the trick

So let me recap one last time:

I found this step-by-step for exporting from FIM to a TEXT file; and it works 100% (http://technet.microsoft.com/en-us/library/ee534904(WS.10).aspx) - however, when I try the same steps to write data to an external SQL database...it does not work.

So I found this MIIS/SQL walk through (http://technet.microsoft.com/en-us/library/cc720650(WS.10).aspx) which I tried on FIM 2010...but it still will not write any data to the SQL CS or SQL itself.

So let me ask Microsoft one more time:

- Do you have an 'export data from FIM to SQL' step-by-step guide anywhere I could work through please?

Have a look at these:

http://www.wapshere.com/missmiis/getting-started-with-object-creation-and-deletion

http://www.wapshere.com/missmiis/debugging-your-extension-code-from-visual-studio

And make sure you've got "Enable provisioning rules extension" ticked in the Sync Service (Tools -> Options). There are two tick options that look pretty similar - one is for classic prov code, and the other is for the portal-based sync

What Carol is suggesting is a possible alternative if you're not that happy with the logging/troubleshooting options of the declarative rules. I myself have never been invovled with ILM/MIIS so i'm rather unfamiliar with classic rules extensions. I tend to avoid them.

In your case I would check the following:

• Is the "enable declarative provisioning" check in the "options" in the Synchronization manager: I assume it is as provisioning to AD works
• Is the SQL OSR added to the user on the provisioning tab: you checked
• Is the SQL OSR in the ERE attribute on the user in the Metaverse?
• Do you have "create resource in external system" checked in the OSR?
• What happens when you run a full synchronization preview on the user in the Metaverse? What does it say for the SQL MA
• What happens if you add a user using SQL queries: like described here: http://www.w3schools.com/sql/sql_insert.asp
  INSERT INTO Your_Table
  VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')
  To be more precise, this can be done from within the SQL management studio. Right click your database and choose new query or something alike.

Some might be a summary of what is listed above in earlier posts.

I would really suggest you check these items one by one and provide us an answer. Perhaps a screenshot of the preview you run could be convenient as

Thanks Thomas,

so I have created a new SQL database and table, with some basic columns (EmployeeID, Dislpayname, Firstname, Surname).

The INSERT INTO SQL Query works 100%.

now if I make the employeeID the ANCHOR attribute in FIM (SQL MA), then I cannot export the employeeID from FIM to SQL in the attribute flow section (says employeeID is Read Only)....is this normal?

OK, so I changed the Anchor ID to the 'correlation ID' of SQL (ie. the auto-generated value in SQL per row).

Now I am trying to understand this article: http://social.technet.microsoft.com/Forums/en-US/ilm2/thread/6ac2bed1-9704-4fcc-94d2-3be73c5a7f47

Yes I am serious (and I had much the same response as you way back when http://www.wapshere.com/missmiis/what-do-you-mean-i-have-to-write-code). The code is easy - honest. It's also a heck of a lot easier to troubleshoot, and way more flexible, than the portal-based rules. (IMO of course.)

Just a comment though, you clearly weren't following the MIIS walkthrough you found correctly if you didn't do the coding part...

Thomas is right anyhow - you need to understand the process end-to-end and then step through. Somewhere there will be a missing object or an error message.

to answer your earlier questions:

• Is the "enable declarative provisioning" check in the "options" in the Synchronization manager: I assume it is as provisioning to AD works

• Is the SQL OSR added to the user on the provisioning tab: you checked

• Is the SQL OSR in the ERE attribute on the user in the Metaverse?

• Do you have "create resource in external system" checked in the OSR?

• What happens when you run a full synchronization preview on the user in the Metaverse? What does it say for the SQL MA

• What happens if you add a user using SQL queries: like described here: http://www.w3schools.com/sql/sql_insert.asp

>> works 100%

Hopefully these answers will lead us somewhere :-) thanks for your time and efforts

Yes I am serious (and I had much the same response as you way back when http://www.wapshere.com/missmiis/what-do-you-mean-i-have-to-write-code ). The code is easy - honest. It's also a heck of a lot easier to troubleshoot, and way more flexible, than the portal-based rules. (IMO of course.)

Just a comment though, you clearly weren't following the MIIS walkthrough you found correctly if you didn't do the coding part...

Thomas is right anyhow - you need to understand the process end-to-end and then step through. Somewhere there will be a missing object or an error me

As you said the ERE for "HR SQL" is set to ADD in the Portal.

If you check the Connector Space of the FIM MA in the Synchronization Service manager, do you see this ERE in the expected rule list attribute for a given object?
If you check the Metaverse in the Synchronization Service manager, do you see this ERE in the expectedRuleList?

If you click the connectors tab of an object in the FIm Synchronization Manager, then choose the connector for the FIM Service, and run a full sync preview. In my case I have a "provisioning summary" which has my rule with some info from it (the attribute flows marked for initial flow only). What do you have

To answer your questions though:

• If you check the Connector Space of the FIM MA in the Synchronization Service manager, do you see this ERE in the expected rule list attribute for a given object?

• If you check the Metaverse in the Synchronization Service manager, do you see this ERE in the expectedRuleList?

• If you click the connectors tab of an object in the FIm Synchronization Manager, then choose the connector for the FIM Service, and run a full sync preview

>> sorry, but where is this option? Operations tab? management Agents tab? Metaverse Designer tab? Metaverse Search tab? Joiner tab?

Unfortunately time is running out, so if we cannot figure out how to use the new FIM method of provisioning to an external system like SQL, we will need to revert back to the classical MIIS provisioning code method, pity that this new method is so complicated :-(

I'm sorry to hear that. However I would really suggest to get to know the different areas in the Synchronization Manager a bit better. Independent whether you are using classical or codeless this will help you alot during troubleshooting.

Perhaps as a last thing,

1. Is it possible to sync data from FIM Portal to an external SQL server using the new FIM Portal methods (MPR, etc) (and not classic vb code) ?

2. Perhaps someone from MS or a partner can think about publishing a few more walk-throughs for FIM ?

Thank you for your help.

1. Yes, that is possible. The SQL MA is just another out of the box MA and can be used to provision using a synchronisation rule, set, workflow and MPR etc. It's very much the same as with any other MA. You create the MA with the correct credentials properties to be able to retreive the table or view and can then start creating the neccesary componenten (MPR, Sync rule etc) in the Portal.

2. You're right I don't think that there is a MS walk-through yet on provisioning to SQL Server like there is for AD groups and Users like there is here. (alltough besides a few differences on how to connect the external system the idea is pretty much the same).

S. Kwan.

The reason there is no guide is because it's pretty straightforward I think. Ok to be honest apart from the obvious it took me 2 hours. I forgot something...

SO:

As an example for the table creation:

 CREATE TABLE Persons
 (
 ID int PRIMARY KEY IDENTITY,
 AccountName varchar(255) NOT NULL,
 FirstName varchar(255)
 )

What is the abve so special? the "IDENTITY" word. This will ensure the "ID" never needs to be flowed towards SQL and will be autoincremented for each entry. However if you want you can "manually" manage the ID by flowing it from within FIM too.

1. Create the usual set - OSR - WF (ADD SR) - MPR (transition in)
2. In the OSR have a "initial flow only" from csobjectID -> dn
3. Make sure you have "enable synchronization rule provisioning" checked in the options section from the sync manager (i forgot this one....)
4. Besides checking "ID" as anchor and connecting to the SQL\table everything in the SQL MA is default

An article by Markus which helps too: http://social.technet.microsoft.com/Forums/en/ilm2/thread/6ac2bed1-9704-4fcc-94d2-3be73c5a7f47

Perhaps I write a blogpost about it, but there is so many topics to blog about and so few time.

Thomas,

OK, then there must be something funny with my setup...as I have the following...and according to the above, this should work:

SQL Server

• Database called HR
• Auto-increment column ID (bigint, Primary key)
• EmployeeID nvarchar(50), allow nulls
• Displayname nvarchar(50), allow nulls
• Firstname nvarchar(50), allow nulls
• Surname nvarchar(50), allow nulls

FIM Sync Service Manager

• SQL Server MA
• can connect to the database and get the columns
• Anchor is set to the Auto-increment ID
• Connector Filter nothing configured
• Join & Projection nothing configured
• Attribute Flow nothing configured
• Deprovisioning (make them disconnectors)
• Extension nothing is configured

FIM Portal

• Set I am using is 'All Active Users' (this shows all the users in the FIM Portal)

Sync Rules called 'SQL user outbound'

• data flow direction: outbound
• scope: person - SQL MA - person
• relationship: displayname - displayname
• Outbound Sync:
• displayname -> displayname
• employeeID -> employeeID
• sn -> surname
• csObjectID -> dn (initial flow only)

Workflow called 'Add SQL User'

• "Run on Policy Update" NOT selected
• activities: Add the target resource to sync rule SQL user Outbound - Add action

MPR called 'SQL Rule SQL users'

• Policy is enabled
• Transition defition is 'All active users'
• Transition In
• Policy workflow: 'Add SQL User'

What do you think...should the above config work?

Yes, your configuration seems to very ok.

Do you have "enable Synchronization Rules Provisioning" checked below the "Synchronization Rule Settings" in the Options from the Synchronization Manae FIM pger?

Do you flow "ExpectedRuleList" from FIM Portal to Metaverse for the User Object? (in the FIM MA)

Do you flow the ERE objects from the FIM Portal to the Metaverse? (in the FIM MA)

Did you run a full import on your SQL MA?

We should definately be able to get to the bottom of this...

Regards,
Thomas

hi,

• "enable Synchronization Rules Provisioning" is definately enabled

• under Object Type 'person' in the FIMMA I am flowing the following relevant object:

• ExpectedRuleList from FIM Portal to MV

• Within the FIM MA, there are also Object Types for DetectedRuleEntry and ExpectedRuleEntry and SynchronizationRule...within those are a number of automatically generated attribute flows (I did not touch anything that was created by default)

FIM MA have the following run profiles:

• Full Import
• Full Sync
• Delta Import
• Delta Sync
• Export

SQL MA has the following profiles:

• Full Import
• Full Sync
• Export

a full import on the SQL MA completes successfully....but the statistics show nothing, all are 0

If you dont mind, I keep on asking:

about what I asked earlier:

• If you check the Metaverse in the Synchronization Service manager, do you see this ERE in the expectedRuleList?
  >> yes I see both the 'AD user inbound/outbound' and 'SQL user outbound' synchronization Rule

To be more concrete can you do the following:

• Synchronization Manager - Metaverse Search
• Scope by Object Type: "Person"
• Double-click a user which is supposed to be provisioned to SQL
• Click the 3 dots button in "ExpectedRuleList" attribute. Is your SQL OSR listed there?

OK, I just want to inform you that I now have 2 separate FIM systems - wanted to get another system that had no other warning or errors.

1. The original FIM system we have been working on; and in this one the ... ERE does contain the SQL OSR

2. My second system is the fully functional MOC 50382 lab environment. In here I have recreated the entire set of rules as we have discussed above....but on this configuration I DO NOT see the SQL OSR in the ... ERE list

Perhaps we need to work on the second system (if you agree), since it is not throwing any strange exception errors (this system also does not have Update 1 installed). So, why do we not see the SQL OSR in the ERE list?

thanks

PS. should I enable 'Run on Policy Update' ?

Ok, so if the rule is not listed in the ExpectedRuleList attr in the Metaverse we have to go the portal. If you go the provisioning tab of the user (in the portal), is the OSR l

Ok,

So you created an MPR which triggers a WF, which should add the SR to the user.

What set does you use for the MPR (transition in)? If the user is part of the set now, can you make it fall out of the set and back in it? This should add the OSR to the provisioning tab of the user.

so I tried to change the MPR (transition in) set...first tried 'All active users', then 'Adatum AD users', then 'All Full Time employees'...but still no luck, nothing new appearing on the Provisioning tab of the user.

I also ensured that the Sets I tested, did in fact contain the users I have been testing with; and they do.

I even tried changing the Set in the MPR, then run the Run Profiles...but that did nothing to help the situation.

The VMs have other MAs that all work...AD MA, ADLDS MA, Telephone MA, HR MA, FIM MA....I have compared their settings to the SQL MA numerous times...but no luck still...

Don't know if you have the time, but the VM set i am working on is on the Internet, I could give you login details to take a look perhaps?

If you don't have "run on policy update" checked, then the MPR will only be triggered whenever a user actually transitions into a set. If it's already in the set nothing will happens.

If you check "run on policy update" and then change the MPR to use another set which holds the user aswell it should work.

Bear in mind, i'm not saying "run on policy update" should be checked by default. It's more like a tool you can use in some situations.

Thomas, you are a genius !!!

I did what u suggested and suddenly the Provisioning tab had the right ERL list in it, then the Run Profiles did the rest !!

:-)

So the process we had was eventually correct, but for some reason FIM was just not picking up the Set contents correctly...at least now we know how to troubleshoot this process in the future. Would be nice if this was somewhere logged, some form of troubleshooting step-through, or some error message or something.

Next we are going to start working on some other scenarios...mailbox provisioning, workflows, etc.

So once again, thank you SO much for your patience and perseverance with me ... enjoy the weekend !

S.K.

Hehe, glad we sorted it out.

I'm currently writing a blogpost about this. It might help others as well or serve as a checklist in order not to forget something.

Here it is: http://setspn.blogspot.com/2010/11/fim-troubleshooting-codeless.html

It's definately not a "how to provision to external systems" guide as that has been covered on Technet tiptop. However if I would get a system in my hands that's broken, these would be the steps I would check.

If I left any out, feel free to comment on them.

Hi Guys,

I was having similar issues before Christmas and came back to eventually find a useful forum article such as this.  Congrats to S.K for not giving in with the codeless provisioning attempt!  All my boxes were ticked as per Thomas's blog but the key thing that did it for me and started provisioning off to the external SQL DB was from Markus's article where it suggests in the OSR to add an initial flow from csobjectID to dn.  Worked a treat!

happy days

Rob

If you don't have "run on policy update" checked, then the MPR will only be triggered whenever a user actually transitions into a set. If it's already in the set nothing will happens.

If you check "run on policy update" and then change the MPR to use another set which holds the user aswell it should work.

Bear in mind, i'm not saying "run on policy update" should be checked by default. It's more like a tool you can use in some situa

I have solution for this. question seems to be quite old but solution might help others :)

Its working in my test environment. If any one interested I can share details 

Adi, if you have those details, I'd be interested in seeing them. I've run into a highly similar problem as the original poster of this thread.

Thanks!

Farrell

Farrell, Basically I Did following to write data to a database in SQL server from FIM .

1- Created a database and a table with a field called "ID" which will auto increment upon addition of new records

2-Created a SQL MA for above database / table with "ID" attribute as anchor

3-Created Synchronization rules

created Inbond and outbond sync rule

- Under relationship tab I added 2 attributes with the option create resources in external system checked

1- in my case "employee ID"

2-"ID" (Created during SQL steps)

4- Created MPR etc for outbond synchronization

5- refer image for outbound sync rule attributes flow

6- inbound sync rule have certain attributes flows as per my requirement ( in my case telephone number)

Let me know if this helps. This worked for me  i wanted to read data ( telephone number modified in SQL) and populate users in SQL DB (write to SQL)