Configuration of the SQL MA

And now for a closer look at the configuration of SQL MAs.

The config details that are common to all MA types are covered in this post, so I won’t repeat them here.

The SQL-specific options probably apply equally to the Oracle and DB2 MAs, but I’ve never used ’em myself.

We’ll start on the Connect to database page of the MA config, where you will no doubt have already filled in your server, database and table or view name. You may optionally enter a name of a Delta View (which could actually be a table) and Multivalue Table (which could equally be a view). Both options need explanation, and you will find it further down this page.

On the Configure Columns page you will see the columns and their data types as found in SQL. There is an Edit button here but it all it does is allow you to set how empty fields are treated. If you want to change a data type, or add an extra field, you must do it in SQL and then, in ILM, choose the MA and Refresh Schema.

The Set Anchor option refers to the primary key of the table. It will be used to uniquely identify the record. Get it right as you can’t change it later!

For the Object Type you have two choices – fixed or column. This will also be unchangeable after MA creation.

• For the Fixed type you state that all objects imported into this connector space will be of type “blah”. (or type “person”, or “group” if you don’t like “blah”…)
• For the Column type you are telling ILM to go look up the object type in one of the table columns. This is a nicely flexible option – just add a column called “ObjectType” to your table, and then populate it with person, group, dog, cat, tea-tray… whatever you need.

Delta Table/View

The Delta View/Table is where you list that subset of records which have changed since the last import, allowing you save time with a Delta Import and Delta Sync. The Delta table must have every column of the primary table plus an extra column indicating if the change is an ‘Add’, ‘Delete’ or ‘Modify’. As I’m just talking MA configuration here I shall refer you to my earlier post on how to make SQL delta tables. It’s also covered in the MIIS Walkthroughs.

Once you enter your Delta table name on the Connect to database page and click OK, you get this alarming looking message from ILM: “You must configure change type attribute (delta) in order to continue”. Which is kind of silly at this point, because you don’t configure it until the next page. So OK the message, and you will arrive on the Configure Columns page.

Now you have the option to Configure delta. Click this button and you get a little form where you select that extra column in the delta table which holds the ‘Add’,’Delete’ or ‘Modify’ instruction. While you’re here you can also tell ILM you don’t want to use the words ‘Add’,’Delete’ and ‘Modify’, but something else instead – up to you.

You will also notice an option for Attribute modify. This is only enabled when you’re also doing multi-value imports. It’s rather an advanced topic so I shall refer you to this other post I wrote on attribute-level imports.

MultiValue Table/View

The SQL MA will interpret each table line as a seperate object, and each column entry as a single value. This is fine for surname, userID and other such singled-valued attributes – but is no good for listing multiple telephone numbers, or direct reports, or group members.

To populate multi-valued attribtes from a SQL MA you must use a multivalue table.

Such a table can have multiple lines for each object, one for each attribute value. Using the classic example of group membership, you may have:

ObjectID	AttributeName	Value
Directors	member	than
Directors	member	jpearson
Sales	member	mdali
Sales	member	ffranc
Sales	member	vrickmans

Now, when configuring your MA, you enter the name of the MultiValue table on the Connect to database page, and then, on the Configure Columns page, you will find you can now click the Multi-value button.

This takes you to a form which is, frankly, as clear as mud (and also includes a number of pointless options which are always greyed out), but lucky for you, I have it figured out 😉

The Attribute name column is the one, in your Multivalue table, where you listed the name of the multivalue attributes – eg., member, telephone, directReports etc. In my example I have called this column, with dazzling logic, “AttributeName”.

The Attribute type can only ever be String – don’t ask me why the others are there. It wants you to enter the name of the column where the attribute value will be found. Again I have been truly unimaginative and stuck to “Value” as my column heading.

And finally, at the bottom, you list the expected attributes. It is not enough for ILM to go and find these in that AttributeName column – they must also be registered here, along with their data type. (This is not so surprising really – compare it to selecting the required attributes in an AD MA.)

For other posts on multivalue tables please see

• Who needs Group Populator when you have Multivalue tables?
• Delta and Multivalued Combined.

Export Flows and De/Provisioning

Certain restrictions exist if you wish to export data from a SQL MA – such as creating or deleting lines in the table, or updating individual values.

You can do it, but only to tables or simple views. If the view is built from several tables you will find you cannot write to it. This is a restriction imposed by SQL rather than ILM.

And finally…

SQL MAs are mighty useful and you may find yourself using them a lot. Sometimes it is easier to sync via a SQL table that direct to a tricky application. SQL is great at tasks like creating delta tables from a comparison of two snapshots. There’s also no reason why you can’t create other databases on the ILM server to hold the tables for your SQL MAs.

Just don’t put ’em in the MicrosoftIdentityIntegrationServer database…

I wouldn’t say it if I hadn’t seen it done.