Synchronizing SQL Server Objects to Active Directory

Applies To: Forefront Identity Manager

The objective of MIIS 2003 is to enable the management of distributed identity data from a central point. This includes the synchronization of identity data across various connected data sources. The source identity data can be stored in a variety of different data repositories including directories and databases. Synchronizing identity data across different types of data repositories typically involves a transformation of the data to make it fit into the specification of the target data repository. For example, Active Directory supports the concept of multi-valued attributes. This means that multiple attribute values can be assigned to a single object attribute. The member attribute of a group in Active Directory is an example for such a multi-valued attribute. In a common database like Microsoft SQL Server, each row in the database represents a single object, with each column in that row having only one value for an attribute. Microsoft Identity Integration Server 2003 does not support this type of table, where multiple values exist in a single column. Because a single column for a single row can have more than one value, there is no guaranteed way of changing any one value.

This document discusses how and what you need to do if you want to synchronize objects from an SQL Server database to Active Directory.

What This Document Covers

This document is part of a series of documents that discuss the challenges many businesses face when they use a management agent for SQL Server for identity integration. All documents in this series are based on a simple test environment that uses Active Directory® and a Microsoft SQL Server™ database as connected data sources for a server running MIIS 2003.

This document shows how to synchronize objects from SQL Server to Active Directory. After completing the procedures in this document, you will be able to:

• Synchronize data from SQL Server to Active Directory

• Handle multi-valued reference attributes in form of group membership.

• Let SQL server maintain the anchor attribute for objects.

• Implement different object types in SQL Server.

The following illustration shows the setup for connected data sources discussed in this document.

The document uses the following management agents:

• A management agent for SQL Server, name MySQLMA

• A management agent for Active Directory, named MyADMA

The script and code in the document assume ideal data. Advanced coding practices, such as developing structured code and handling invalid data, are beyond the scope of this document.

The following illustration shows the met directory design of the scenario discussed in this document.

Prerequisite Knowledge

This document assumes that you have a basic understanding of the following information technology (IT) concepts and tasks:

• Managing Active Directory, including managing organization units, groups and users, and domain controllers.

• Managing SQL Server databases, including creating databases, table, and views.

• Administering MIIS 2003, including the concepts described in Getting Started with MIIS 2003 Walkthrough (https://go.microsoft.com/fwlink/?LinkID=83357).

A description of how to set up MIIS 2003 and Active Directory is out of the scope of this document.

For an introduction to essential MIIS 2003 concepts, see the following documents:

• MIIS 2003 Overview (https://go.microsoft.com/fwlink/?LinkId=30737).

• Getting Started with MIIS 2003 Walkthrough (https://go.microsoft.com/fwlink/?LinkID=83357).

Audience

This guide is intended for IT planners, systems architects, technology decision makers, consultants, infrastructure planners, and IT personnel who plan and develop MIIS 2003 solutions using a management agent for Active Directory and SQL Server.

Time Requirements

This the procedures in this document require 60 to 90 minutes for a new user to complete. An experienced MIIS 2003 user can complete them in 30 to 40 minutes.

Note

These time estimates assume the testing environment is already configured and ready for testing to begin and do not include the time required to set up the test environment.

Scenario Description

Fabrikam, a fictitious corporation, uses a central SQL Server database as a primary source for user and group management data. Periodically, they use MIIS 2003 to synchronize this data from the central SQL Server database to Active Directory.

Testing Environment

To perform the procedures in this document, your testing environment should have the following characteristics:

• One Active Directory domain controller (DC1)

• One server, named MIISSrv1, hosting MIIS 2003 with Microsoft Visual Studio® .NET 2003 development system installed

  This server requires Microsoft Windows Server® 2003, Enterprise Edition and Microsoft SQL Server 2000.

• One server, named, SQLSrv1, hosting SQL Server

  This server can run either SQL Server 2000 or SQL Server 2005.

In addition, this document assumes that all servers are running Windows Server 2003 and that all computers are members of the Fabrikam.com forest.

The following illustration shows the infrastructure used in the scenario for this document.

Note

It is possible to test the results of the procedures in this document on a computer that has all of these characteristics. However, for your production environment, we strongly recommend that you do not set up MIIS 2003 and Active Directory on the same computer for performance reasons. To complete the procedures in this document, you must use SQL Server 2000 as the database backend.

You must have accounts with sufficient rights for the management agent for SQL Server and the management agent for Active Directory. This document uses the domain administrator account for both management agents. However, in a production environment, use appropriately locked-down accounts. For information about locking down accounts, see MIIS 2003 Security Considerations Guide (https://go.microsoft.com/fwlink/?LinkID=58877).

Before You Begin

This document provides you with information you must have to build an environment to synchronize objects for SQL Server to Active Directory. To keep the procedures in this document independent from the version of SQL Server you are using, and to significantly reduce the time required to complete these procedures, you can use the scripts in the Appendix to automate and simplify administrative tasks.

For your convenience, the Appendix includes a database table viewer called TableViewer. TableViewer is an HTA application that is preconfigured to display the contents of all scenario tables in an HTML page, which will help you verify whether the scenario tables contain the expected results.

This section includes a complete list of the scripts and code in this document and provides instruction for running the scripts and creating the viewer.

Scripts and Code in this Document

The following table shows the scripts and code that are included in the Appendix.

Appendix	Description
Appendix A: Script to Create the Database	Script to create the SQL database
Appendix B: Script to Create the Objects Table	Script to create objects table
Appendix C: Script to Populate the Objects Table	Script to populate the objects table
Appendix D: Script to Create the Reference Table	Script to create the references table
Appendix E: Script to Populate Active Directory Objects	Script to populate Active Directory objects
Appendix F: Provisioning Code	Script to provision to Active Directory
Appendix G: HTA Code to View the Contents of the SQL Server Tables	Script to view the contents of the SQL Server tables

Running the scripts

The scripts in this document are designed to run locally on a computer. You run a script that configures Active Directory objects on the Active Directory domain controller, and you run a script that configures SQL Server components on the computer running SQL Server.

To run a script

1. In the Appendix, copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\Appendix.vbs.

3. Although the name of the file is irrelevant, it must have the .vbs file name extension.

4. To run the script, double-click the icon for the.vbs or the .hta file.

   Exceptions to this procedure are noted in the Appendix.

Configuring the database viewers

As mentioned earlier, this document includes a TableViewer for the data in the scenario tables. The following sections provide information for creating the viewers.

Creating TableViewer

TableViewer is a HTML application (HTA) you can use to see, in one view, the content of all scenario tables that are part of this document. At this point the viewer is empty and does not return any data. Data is returned when the SQL Server table is populated.

To implement TableViewer

1. In Appendix G, copy the HTA code, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as an .hta file, for example, C:\TableViewer.hta.

Note

Make sure you save this file with the extension .hta and not .vbs.

For more information about using the supplied scripts, see Running the scripts.

Implementing the Procedures in This Document

In this document, you configure the Active Directory, SQL Server, and MIIS 2003 environments. You then populate these environments with test data and verify whether Active Directory contains the expected results.

To implement the procedures in this document, you must complete the following steps in the following order:

1. Configure the SQL Server environment

2. Configure the initial Active Directory environment.

3. Update the metaverse schema.

4. Create the management agents.

5. Configure the object deletion rule.

6. Enable object provisioning.

7. Configure run profiles.

8. Test the configuration.

9. Populate the reference table.

10. Test the configuration changes.

Note

You must complete the configuration of the connected data sources before you configure MIIS 2003 because the management agent configuration depends on the availability of some connected data source components. For example, the import scope of the Active Directory management agent is limited to the newly created organizational unit. Each management agent performs schema detection in an early state of the configuration phase, which is why the required connected data source structure must be in place before you can configure a management agent.

Configuring the SQL Environment

The SQL Server environment for this document has a new SQL Server database, two tables, and one data viewer.

The following table shows the required SQL environment for this document.

Required Element	Description
One new SQL Server database	Stores all required data for this document.
One objects table	Stores the users and group and that will be synchronized to Active Directory
One reference table	Stores the group members from the objects table that will be synchronized to Active Directory

Configuring the SQL Server Database

To synchronize SQL server objects to Active Directory you must create a database on the computer running SQL server. The following illustration shows the database; name ADObjects, on which this document is based.

Creating the SQL Server Database

You can use the tools provided by SQL Server to create the database manually of you can use the script in Appendix A to create the database. If you create the database manually, you must save it with the name ADObject. For information about using the supplied scripts, see Running the scripts.

To configure the SQL Server database using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To configure the SQL Server database using the script

1. In Appendix A copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on you local drive as a .vbs file, for example, C:\AppendixA.vbs

3. To run the script, double-click the icon for the .vbs file

4. Use TableViewer to confirm that the delta view returns the results you expect.

Configuring the SQL Server Tables

To synchronize the SQL Server object information to Active Directory, you must create the following tables:

• One objects table

• One reference table

The following illustration shows the required table structure.

The Structure of the Objects Table

The structure of an objects table in a real-world solution depends on the requirements of your scenario. In this document, the data stored for each object has the following columns:

• ObjectID

• ObjectType

• SamAccountName

• Description

The following illustration shows the attributes that are maintained for each object in the objects table that will be synchronized to Active Directory.

The ObjectID column acts as a unique identifier (ID) for each object stored in the objects table. A convenient way to maintain a unique identifier in SQL Server is the implementation of a column in which the IDENTITY property is set. By setting this property, you can specify both and identity number for the first row inserted into the table (Identity Seed property) and an increment (Identity Increment property) to be added to the seed to determine successive identity numbers.

When you insert values into a table with an identifier column, Microsoft SQL Server automatically generates the next identity value by adding the increment to the seed. This configuration ensures that each new record in the table is assigned a new unique identifier that is a counter, which is an increment of one of the last-used identity counter value.

The following illustration shows the configuration of a SQL Server column that automatically generates a unique ID in the form of a counter that is incremented by one for each new record added to the table.

In this document MIIS 2003 also uses the ObjectID column as anchor. An anchor is unique identifier that issued by MIIS 2003 to link objects in the connector space with objects in a connected data source.

In a SQL Server table, object data is stored as one record per object. In this document, all objects (the user object and the group object type) are stored as records in the same objects table. By adding an ObjectType column to the objects table, you can distinguish between individual objects types. The ObjectID and the ObjectType columns do not allow null values, but the other columns do. When MIIS 2003 configures a management agent for SQL Server, it extracts the object type schema form the existing objects table.

Note

When you configured the ObjectID attribute you specified a unique identifier for each object from Active Directory. However, the value of the ObjectID attribute is intuitive. A more intuitive unique identifier is samAccountName and, which is an attribute for a security principle in Active Directory. In this document, we use this intuitive attribute name to help you follow the data flow.

The Description field provides an attribute that you can use to test the impact of attribute level changes to this solution. By changing the description filed of one of the scenario objects, you can implement a simple attribute-level change.

Creating the Objects Table

The objects table is required to store object data that MIIS 2003 will synchronize to Active Directory. The objects table contains the columns for the SQL table as well as the SQL Server objects that reside in those columns.

The following table shows the definition of the objects table.

Column name	Data type	Length	Allow nulls
ObjectID	Int	4	Unchecked
ObjectType	Char	20	Unchecked
SamAccountName	Char	256	Checked
Description	Char	256	Checked

The ObjectID value is the unique identity (ID) of each object created in the objects table. To enable SQL Server to automatically calculate the value, you must define this column as Identity, and then set the Identity Seed value to 1 and the Identity Increment value to 1.

You can use the tools provided by SQL Server to create the objects table or you can use the script in Appendix C to create the table. If you create the objects table manually, you must save it with the name tblObjects. For more information about using the supplied scripts, see Running the Scripts.

To create the objects table using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To create the objects table using the script

1. In Appendix B copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixB.vbs.

3. To run the script, double-click the icon for the.vbs file.

4. Use TableViewer to confirm that the delta view returns the results you expect.

Populating the Objects Table

The objects table is populated with five objects, one group object and four user objects. The ObjectID is populated with the unique id for each object in the database, The ObjectType column is populated with the object type (group or user), the SamAccountName column is populated with the user id that will be provisioned into Active Directory, and the Description column is left blank.

The following illustration shows the configuration of the objects table after it has been populated.

You can use the tools provided by SQL Server to populate the objects in the objects table or you can use the script in Appendix C to populate the objects in the objects table. Fore more information about using the supplied scripts, see Running the Scripts.

To populate the objects table using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To populate the objects table using the script

1. In Appendix C copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixC.vbs.

3. To run the script, double-click the icon for the.vbs file.

4. Use TableViewer to confirm that the delta view returns the results you expect.

The structure of the references table

In Active Directory group membership data is stored as reference attribute. A reference attribute contains a pointer to another object. The actual group membership data is stored in an attribute name member, which contains the distinguished name of each object that is a member of a group.

The following illustration shows the logical design of a group in Active Directory.

In a common database, each row in the database represents a single object. For example, the following objects table has on row for each user and group object that is to be synchronized to Active Directory.

ObjectID	ObjectType	SamAccountName	Description
75	User	U2	Test user
77	Group	G1	Test group

When an object in a single row has an attribute that can have more than one value, that attribute is referred to as a multi-value attribute. MIIS 2003 does not have a standard table configuration for objects in a single row that have multi-valued attribute into a single column (member).

ObjectID	ObjectType	SamAccountName	Description	Member
75	User	U2	Test user	-
77	Group	G1	Test group	75, 78, 79

MIIS 2003 does not support a table in which multiple values exist in a single column. Because a single column for a single row can have more that one value, MIIS 2003 provides no guaranteed way of changing a single value.

To import objects with multi-value attributes from a database, you must specify a primary table and a secondary table for multi-value attributes. The secondary table must reside in the same database as the primary table. When you configure your management agent on the Connect to Database page of Management Agent Designer, you can specify the primary table and the multi-valued table. You use the primary table to import all objects into the connector space and export all objects from the connector space. You use the secondary, multi-value table to import and export the multi-value attributes. The multi-value table used in this document has three columns

• An ObjectID column to track the object with which the multi-value attribute is associated

• An AttributeName column to track the name of the multi-value attribute

• A ReferenceID column to track the ObjectID value for the object referenced by a value of a multi-value attribute

The following table shows these columns.

ObjectID value	AttributeName	ReferenceID
77	member	75
77	member	78
77	member	79

Creating the references table

The references table is required to store the multi-value membership data of the Active Directory group that will be synchronized to Active Directory. The following table shows the required definition of the references table.

Column name	Data type	Length	Allow nulls
ObjectID	Int	4	Unchecked
AttributeName	Char	20	Unchecked
ReferenceID	Int	4	Unchecked

You can use the tools provided by SQL server to create the references table or you can use the script in Appendix D to create the table. If you create the references table manually, you must save it with the name tblReferences. For more information about using the supplied scripts, see Running the scripts.

To create the references table using SQL Server tools

• For information about using SQL Server tools, see SQL Server Help.

To create the references table using the script

1. In Appendix D copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixD.vbs.

3. To run the script, double-click the icon for the.vbs file.

4. Use TableViewer to confirm that the delta view returns the results you expect.

The group membership information will not be populated in this procedure but will be populated after the SQL Server objects are synchronized to Active Directory. By doing this, the handling of group membership in SQL Server can be explained as a separate process.

Configure the Initial Active Directory Environment

The Active Directory environment in this document is based on one initial organizational unit that will hold the SQL Server objects synchronized from the SQL Server database.

The following illustration shows the initial Active Directory organization unit for this document.

You can use the tools provided by Active Directory to create the initial Active Directory environment or you can uses the script supplied in Appendix C.

To create the required objects using Active Directory tools

• For information about using SQL Server tools, see SQL Server Help.

To create the required objects using the script

1. In Appendix E, copy the script, and then paste it into a new Notepad file.

2. Save the Notepad file on your local drive as a .vbs file, for example, C:\AppendixE.vbs.

3. To run the script, double-click the icon for the.vbs file.

Update the Metaverse Schema

To simplify the provisioning logic for this document, you must have two new metaverse object types: ADGroup and ADUser. The following attributes are required for each object type:

• ADGroup: SamAccountName, description, member

• ADUser: SamAccountName, description

The following illustration shows the newly-created object types in Metaverse Designer.

To update the metaverse schema

1. In MIIS 2003, open Identity Manager.

2. Switch to Metaverse Designer.

3. On the Actions menu, click Create Object Type.

4. In the Object type name box, type ADUser.

5. In the Available attributes box, select description.

6. Click New attribute.

7. In the Attribute name box, type SamAccountName.

8. To add the new attribute to this object type, click OK.

9. On the Actions menu, click Create Object Type.

10. In the Object type name box, type ADGroup.

11. In the Available attributes box, select description, member, and SamAccountName.

12. To add the new object type, click OK.

Create the Management Agents

For this document you must create two management agents:

• Management agent for SQL Server

• Management agent for Active Directory

Creating the management agent for SQL Server

To create the management agent for SQL Server, use the Create Management Agent Wizard.

To create a management agent for SQL Server

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. On the Actions menu, click Create to start the Create Management Agent Wizard.

4. Specify the required parameters for each page, and then click Finish to create the management agent.

Create Management Agent page

On this page, you select the type of management agent you want to create, and then name it.

To complete the Create Management Agent page

1. In the Management agents for list, select SQL Server.

2. In the Name box, type MySQLMA, and then click Next.

Connect to Database page

On this page, you enter the location of the data that is managed by this management agent and data about the account that is used by this management agent to connect to the database.

To complete the Connect to Database page

1. In the Server box, type the name of your SQL Server.

2. In the Database box, type ADObjects.

3. In the Table/View box, type tblObjects.

4. In the Multivalue Table box, type tblReferences.

5. Select the Windows integrated authentication option button.

6. In the User name box, type administrator.

7. In the Password box, type the administrator's password.

8. In the Domain box, type fabrikam.

9. Click Next.

Configure Columns page

On this page, you provide details about the columns in your source tables that MIIS 2003 detected. For this document, you must set an anchor, configure the multi-value settings, and then specify object types.

To set the anchor

1. To open the Set Anchor dialog box, click Set Anchor.

2. In the Available attributes box, select ObjectID.

3. To add the attribute to the Selected attributes box, click Add.

4. To close the Set Anchor dialog box, click OK.

To configure multi-value settings

1. To open the Multi-value settings dialog box, click Multi-value.

2. In the Specify the attribute name box, select AttributeName.

3. Select the Number attribute column check box, and then select ReferenceID.

4. To open the Multi-value Attribute dialog box, click New.

5. In the Name box, type member.

6. In the Type box, select Number.

7. Select the Reference (DN) check box.

8. To close the Multi-value Attribute dialog box, click OK.

9. To close the Multi-value settings dialog box, click OK.

To specify object types

1. Click Object Type to open the Set Object Type dialog box.

2. Select the Object type column option button, and then select ObjectType.

3. To close the Set Object Type dialog box, click OK.

4. Click Next.

Configure Connector Filter page

You do not have to configure anything on this page.

To complete the Configure Connector Filter page

• Click Next.

Configure Join and Projection page

On this page, you configure the required join and projection rules for your scenario. This document requires you to configure a projection rule for the user object type and the group object type.

The following illustration shows the Configure Join and Projection Rules dialog box after you have applied all projection rules for this document.

The following table shows the data source object type and the metaverse object type pairs for which you must configure a projection.

Projection rule	Data source object type	Metaverse object type
Rule 1	user	ADUser
Rule 2	group	ADGroup

To complete the Configure Join and Projection Rules page

1. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Data Source Object Type column, select the data source object type shown for that row in the table.

   2. To open the Projection dialog box, click New Projection Rule.

   3. Select Declared.

   4. In the Metaverse object type list, select the metaverse object type shown for that row in the table.

   5. To close the Projection dialog box, click OK.

2. Click Next.

Configure Attribute Flow page

On this page, you provide the import and export attribute flow rules for your scenario. This document requires you to configure import attribute flow rules for the user and group objects of the management agent for SQL Server:

The following illustration shows the Configure Attribute Flow dialog box after you have applied all attribute flow rules for the user object.

The following table shows the data source and metaverse attribute pairs for which you must configure a flow rule.

Flow rule	Data source attribute	Metaverse attribute
Rule 1	Description	description
Rule 2	SamAccountName	SamAccountName

To complete the Configure Attribute Flow page for the object type user

1. In the Data source object type box, select User.

2. In the Metaverse object type box, select ADUser.

3. Under Mapping Type, select Direct.

4. Under Flow Direction, select Import.

5. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Data source attribute list, select the data source attribute shown for that row in the table.

   2. In the Metaverse attribute list, select the metaverse attribute shown for that row in the table.

   3. Click New.

The following illustration shows the Configure Attribute Flow dialog box after you have applied all attribute flow rules for the group object.

The following table shows the data source and metaverse attribute pairs for which you must configure a flow rule.

Flow rule	Data source attribute	Metaverse attribute
Rule 1	Description	description
Rule 2	member	Member
Rule 3	SamAccountName	SamAccountName

To complete the Configure Attribute Flow page for the object type group

1. In the Data source object type box, select Group.

2. In the Metaverse object type box, select ADGroup.

3. Under Mapping Type, select Direct.

4. Under Flow Direction, select Import.

5. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Data source attribute list, select the data source attribute shown for that row in the table.

   2. In the Metaverse attribute list, select the metaverse attribute shown for that row in the table.

   3. Click New.

Click Next.

Configure Deprovisioning page

You do not have to configure anything on this page.

To complete the Configure Deprovisioning page

• Click Next.

Configure Extensions page

You do not have to configure anything on this page.

To complete the Configure Extensions page

• To create the management agent, click Finish.

Creating the management agent for Active Directory

To create the management agent for Active Directory, you use the Create Management Agent Wizard.

To create a management agent for Active Directory

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. On the Actions menu, click Create to start the Create Management Agent Wizard.

4. Specify the required parameters for each page, and then click Next.
   The instructions for each page are provided as separate procedures below.

5. Click Finish to create the management agent.

Create Management Agent page

On this page, you select the type of management agent you want to create, and then name it.

To complete the Create Management Agent page

1. In the Management agents for list, select Active Directory.

2. In the Name box, type MyADMA, and then click Next.

Connect to Active Directory Forest page

On this page, you enter the name of your Active Directory forest and provide data for the account that this management agent uses to connect to that forest.

Note

In a real-world scenario, you can use whatever name you choose for the forest and domain, and any user account that has sufficient rights.

To complete the Connect to Active Directory Forest page

1. In the Forest name box, type fabrikam.com.

2. In the User name box, type administrator.

3. In the Password box, type the administrator's password.

4. In the Domain box, type fabrikam, and then click Next.

Configure Directory Partitions page

On this page, you select your directory partition and the container (organizational unit) that contains the Active Directory objects that are part of this document.

To complete the Configure Directory Partitions page

1. In the Select directory partitions box, select the check box next to DC=Fabrikam,DC=Com.

2. Click Containers to open the Select Containers dialog box.

3. In the Select Containers dialog box, clear the checkboxes next to all containers except.

4. To close the Select Containers dialog box, click OK.

5. On the Configure Directory Partitions page, click Next.

Select Object Types page

On this page, you select the object types you plan to manage with a management agent. For this document, you must select the object types specified in the following procedure.

To complete the Select Object Types page

1. In the Select Object Types box, select the following object types:

   • container

   • domainDNS

   • group

   • organizational unit

   • user

2. Click Next.

Select Attributes page

On this page, you specify the attributes in your scenario. For this document, you must select the attributes specified in the following procedure.

To complete the Select Attributes page

1. In the Attributes box, select the following attributes:

   • cn

   • description

   • member

   • sAMAccountName

2. Click Next.

Configure Connector Filter page

You do not have to configure anything on this page.

To complete the Configure Connector Filter page

• Click Next.

Configure Join and Projection Rules page

You do not have to configure anything on this page.

The complete the Configure Join and Projection page

• Click Next.

Configure Attribute Flow page

On this page, you provide the import and export attribute flow rules for your scenario. This document requires you to configure import attribute flow rules for the user and group objects of the management agent for Active Directory:

The following illustration shows the Configure Attribute Flow dialog box after you have applied all attribute flow rules for the user object.

The following table shows the data source and metaverse attribute pairs for which you must configure a flow rule.

Flow rule	Data source attribute	Metaverse attribute
Rule 1	Description	Description
Rule 2	sAMaccountName	SamAccountName

To complete the Configure Attribute Flow page for the object type user

1. In the Data source object type box, select User.

2. In the Metaverse object type box, select ADUser.

3. Under Mapping Type, select Direct.

4. Under Flow Direction, select Export.

5. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Data source attribute list, select the data source attribute shown for that row in the table.

   2. In the Metaverse attribute list, select the metaverse attribute shown for that row in the table.

   3. Click New.

The following illustration shows the Configure Attribute Flow dialog box after you have applied all attribute flow rules for the group object.

The following table shows the data source and metaverse attribute pairs for which you must configure a flow rule.

Flow rule	Data source attribute	Metaverse attribute
Rule 1	description	description
Rule 2	sAMaccountName	SamAccountName
Rule 3	member	member

To complete the Configure Attribute Flow page for the object type group

1. In the Data source object type box, select Group.

2. In the Metaverse object type box, select ADGroup.

3. Under Mapping Type, select Direct.

4. Under Flow Direction, select Export.

5. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Data source attribute list, select the data source attribute shown for that row in the table.

   2. In the Metaverse attribute list, select the metaverse attribute shown for that row in the table.

   3. Click New.

Configure Deprovisioning page

On this page, you configure the deprovisioning synchronization rule for your scenario. The deprovisioning synchronization rule defines what should happen to a connector space object that was disconnected during outbound synchronization. This document requires you to stage a deletion on the disconnected object.

To complete the Configure Deprovisioning page

• Select Stage a delete on the object for the next export operation, and then click Next.

Configure Extensions page

You do not have to configure anything on this page.

To complete the Configure Extensions page

• To create the management agent, click Finish.

Configure the Object Deletion Rule

In a scenario that synchronizes objects from SQL Server to Active Directory, it is important to include a solution for the case where objects are deleted in the SQL Server table. Because SQL Server is authoritative for the objects that are discussed in this document, you can safely delete metaverse objects after MIIS 2003 removes the link between an object in the SQL Server connector space and an object in the metaverse during inbound synchronization. You delete metaverse objects by setting the object deletion rule to delete metaverse objects when they are disconnected from an object in the SQL Server connector space.

For this document, you must configure two object deletion rules: one for the ADGroup object type and one for the ADUser object type.

The following illustration shows Metaverse Designer after you have applied all object deletion rules.

The following table shows the object types and the management agent pairs for which you must configure an object deletion rule.

Object deletion rule	Object types name	Management agent name
Rule 1	ADGroup	MySQLMA
Rule 2	ADUser	MySQLMA

To configure the object deletion rule for the ADUser and ADGroup object types

1. In MIIS 2003, open Identity Manager.

2. Switch to Metaverse Designer.

3. For each row in the table immediately above this procedure, complete the following steps:

   1. In the Name column of the Object types table, select the object types name shown for that row in the table.

   2. From the Actions menu, select Configure Object Deletion Rule to open the Configure Object Deletion Rule dialog box.

   3. Select the Delete metaverse object when connector from this management agent is disconnected option button.

   4. In the management agents box, select the management agent name shown for that row in the table.

   5. To close the Configure Object Deletion Rule dialog box, click OK.

Enable Object Provisioning

Enabling object provisioning has the following steps:

1. Write code for the provisioning method.

2. Build a metaverse rules extension.

3. Enable the metaverse rules extension.

First you write code for your provisioning method.

Note

This document provides you with two object types-the user object type and the group object type. You implement these object types in the provisioning method to decrease your code development time.

This code will set the container defined in your MyADMA management agent setup. The container used for this document is OU=MIISObjects,DC=fabrikam,DC=com. The code defining the container can be adjusted according to the setup you have in your test environment. The provisioning code will also construct the distinguished name based on the CN value (samAccountName is used to set the CN value in this code example) in the provisioning code. If the metaverse object type equals ADUser or ADGroup then a new connector is started for the object in the MyADMA connector space.

The following code shows the provisioning method.

Try
   Dim connectedMA As ConnectedMA
   connectedMA = mventry.ConnectedMAs("MyADMA")

   If connectedMA.Connectors.Count < 1 Then
      Dim myContainer As String
      myContainer = "OU=MIISObjects,DC=fabrikam,DC=com"

      Dim myRDN As String
      myRDN = "CN=" & mventry("samAccountName").Value

      Dim myDN As ReferenceValue
      myDN = connectedMA.EscapeDNComponent(myRDN).Concat(myContainer)

      Dim csEntry As CSEntry

      If (mventry.ObjectType.Equals("ADUser")) Then
         csEntry = connectedMA.Connectors.StartNewConnector("user")
      Else
          csEntry = connectedMA.Connectors.StartNewConnector("group")
      End If
      csEntry.DN = myDN
      csEntry.CommitNewConnector()
   End If
   Catch myEx As Exception
       Throw myEx
   End Try

Next, you build the metaverse rules extension using Visual Studio 2005. The metaverse rules extension is based on your provisioning code.

Finally, you enable the metaverse rules extension in MIIS 2003. The following illustration shows the Options dialog box in MIIS 2003, which you use to create the rules extension.

To build a metaverse rules extension

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the Tools menu, select Options to open the Options dialog box.

4. Select the Enable metaverse rules extension check box.

5. Select the Enable Provisioning rules extension check box.

6. Click Create Rules Extension Project to open the Create Extension Project dialog box.

7. In the Project name box, type MVExtension.

8. Select Launch in VS.Net IDE, and then click OK to start Visual Studio .NET.

9. To open Visual Studio .NET, click OK.

10. Copy the code snippet from Appendix F, and then paste it into the body of the Provision method for your new project.

11. From the Build menu, select Build Solution.

12. In MIIS 2003, in the Options dialog box, click Browse.

13. In the list of available files, select MVExtension.dll.

14. To activate your metaverse rules extension, click OK.

Configure Run Profiles

This topic provides instructions for creating and configuring the required run profiles. For this document, you must configure several run profiles for the management agents for SQL Server and Active Directory.

The following illustration shows the Configure Run profiles for dialog box after you have configured all run profiles for MySQLMA.

The following table show the run profiles that you must create for the management agent for MySQLMA.

Profile	Run profile name	Step type
Profile 1	Full Import	Full Import (Stage Only)
Profile 2	Full Synchronization	Full Synchronization
Profile 3	Delta Synchronization	Delta Synchronization

To create run profiles for the management agent for SQL Server

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. In the management agent list, select MySQLMA.

4. On the Actions menu, click Configure Run Profiles to open the Configure Run Profiles for dialog box.

5. For each run profile in the table immediately above this procedure, complete the following steps:

   1. To open the Configure Run Profile Wizard, click New Profile.

   2. In the Name box, type the profile name shown in the table, and click Next.

   3. In the type list, select the step type show in the table, and click Next.

6. Click Finish to create the run profile.

The following illustration shows the Configure Run Profiles for dialog box after you have configured all run profiles for MyADMA.

The following table shows the run profiles that you must create for the management agent for MyADMA

Profile	Run Profile Name	Step Type
Profile 1	Full Import	Full Import (Stage Only)
Profile 2	Export	Export

To create run profiles for the management agent for Active Directory

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. In the management agent list, select MyADMA.

4. On the Actions menu, click Configure Run Profiles to open the Configure Run Profiles for dialog box.

5. For each run profile in the table immediately above this procedure, complete the following steps:

   1. To open the Configure Run Profile Wizard, click New Profile.

   2. In the Name box, type the profile name shown in the table, and click Next.

   3. In the type list, select the step type shown in the table, and click Next.

6. Click Finish to create the run profile.

Test the Configuration

To test the configuration, perform the following steps:

1. Importing data from SQL server.

2. Verifying the import results.

3. Importing the organizational unit structure from Active Directory.

4. Verifying the import results.

5. Synchronizing data inside MIIS 2003.

6. Verifying the synchronization results.

7. Exporting the data to Active Directory.

8. Verifying the export results.

9. Importing data from Active Directory.

10. Verify the import results.

The following sections provide procedures for all steps of the full synchronization cycle.

Importing data from SQL Server

As a first step of the complete synchronization cycle, you need to import the SQL Server data in the connector space of the SQL Server management agent. To accomplish this you need to run a full import on the management agent called MySQLMA.

To import data from SQL Server

1. In MIIS 2003, open Identity Manager

2. Switch to Management Agents view.

3. From the management agent list, select MySQLMA.

4. On the Actions menu, click Run to open the Run Management Agent dialog box.

5. In the list of run profiles, select Full Import.

6. To start the run profile, click OK.

Verifying the Import Results

As a result of a successful import, the SQL Server data is staged in the connector space of the SQL Server management agent. You can verify this by using the connector space search feature. The following illustration shows the connector space search result of a successful data import.

Note

MIIS 2003 processes objects in random order, therefore your import results may not look exactly like the following illustration.

To verify the import results

1. In MIIS 2003 open Identity Manager

2. Switch to the Management Agents view.

3. From the management agent list select MySQLMA.

4. On the Actions menu, click Search Connector Space to open the Search Connector Space dialog box.

5. To search the connector space, click Search.

6. Verify that all test objects are listed in the search result.

Importing the organizational unit structure from Active Directory

The organization unit structure has to be imported into the connector space of the Active Directory management agent. This will ensure that all the objects synchronized from the SQL Server database will be placed in the right container. To accomplish this you need to run a full import on the management agent called MyADMA.

To import the organizational unit structure from Active Directory

1. In MIIS 2003 open Identity Manager.

2. Switch to Management Agents view.

3. From the management agents list, select MyADMA.

4. On the Actions menu, click Run to open the Run Management Agent dialog box.

5. In the list of run profiles, select Full Import.

6. To start the run profile, click OK.

Verifying the Import Results

As a result of a successful import, the Active Directory organization unit structure is staged in the connector space of the SQL Server management agent. You can verify this by using the connector space search feature. The following illustration shows the connector space search result of a successful data import.

To verify the import results

1. In MIIS 2003 open Identity Manager

2. Switch to the Management Agents view.

3. From the management agent list select MyADMA.

4. On the Actions menu, click Search Connector Space to open the Search Connector Space dialog box.

5. To search the connector space, click Search.

6. Verify that all test objects are listed in the search result.

Synchronizing data inside MIIS 2003

In this step, the SQL Server data that is staged in the connector space of the SQL Server management agent needs to be provisioned into the connector space of the Active Directory management called MyADMA. To accomplish this, you need to run the full synchronization run profile on the management agent called MySQLMA.

To synchronize data inside MIIS 2003

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list, select MySQLMA.

4. On the Actions menu, click Run to open the Run Management Agent dialog box.

5. In the list of run profiles, select Full Synchronization.

6. To start the run profile, click OK.

Verifying the Synchronization Results

Each synchronization run consists of two phases:

• Inbound Synchronization

• Outbound Synchronization

During inbound synchronization, all imported users and groups are projected into the metaverse and then during outbound synchronization all imported users and groups are provisioned into the connector space of the SQL Server management agent. Since these are two separate steps in a synchronization run, the verification also consists of two separate steps. You have to verify first the inbound synchronization results and then the outbound synchronization. The following sections provide more details for both verification steps.

Verifying Inbound Synchronization Results

You can verify whether all users and group objects have been successfully projected into the metaverse by using the metaverse search feature. Since the SQL Server management agent is configured to project all user and group objects into the metaverse, the metaverse search should return all four users and the group as shown in the following illustration.

Step	Attributes
1	<object GUID>
3	SamAccountName
4	member

To verify inbound synchronization results

1. In MIIS 2003, open Identity Manager.

2. Switch to the Metaverse Search view.

3. On the Actions menu, click Column Settings to open the Search Results Column Settings dialog box.

4. For each row in the table immediately above this procedure, complete the following steps:

   1. In Available Columns column, select the attribute shown for that row in the table.

   2. Click Add

5. To close Search Results Column Settings dialog box, click OK.

6. On the Actions menu, click Search to start a metaverse search.

7. To verify that all objects are projected, review the list of returned objects.

Verifying the Outbound Synchronization Results

The steps to verify whether all objects have been successfully provisioned to the connector space of the Active Directory management agent are almost the same as the steps for "Verifying the import results". During provisioning, representations for all scenario objects are created in the connector space of the SQL management agent. You can verify this by running a connector space search again - but this time on the Active Directory management agent. The following illustration shows the connector space search result after a successful full synchronization.

As you can see in the illustration, the distinguished name (also known as DN) of the objects match the distinguished name of the object attribute flow rule you defined when configuring the Active Directory management agent. The list returned in the connector space search reflects the Active Directory organizational unit structure as well as the objects projected into the connector space by the SQL Server management agent.

To verify the outbound synchronization results

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list, select MyADMA.

4. On the Actions menu, click Search Connector Space to open the Search Connector Space dialog box.

5. To search the connector space, click Search.

6. Verify all test objects are listed in the search result.

7. Close the Search Connector Space dialog box.

Verifying the Management Agent Statistics

Another helpful step to verify the outbound synchronization results is to check the management agent statistics. The Synchronization Statistics for that management agent should report Export Attribute Flow as 5 and Provisioning Adds as 5.

To verify the management agent statistics

1. In MIIS 2003 open Identity Manager.

2. Switch to the Management Agent list, select MySQLMA.

3. Scroll to the bottom of the Synchronization Statistics dialog box

4. Verify Outbound Synchronization for MyADMA list Export Attribute Flow as 5 and Provisioning Adds as 5.

Exporting the data to Active Directory

The newly provisioned data in the connector space of the Active Directory management agent has to be exported to Active Directory. To accomplish this, you need to run the export run profile on the management agent called MyADMA

To export data to Active Directory

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agent list, select MyADMA.

3. On the Actions menu, click Run to open the Run Management Agent dialog box.

4. In the list of run profiles, select Export.

5. To start the run profile, click OK.

Verifying the Export Results

To verify the export results, you should review the export results inside MIIS 2003 and in the connected data source to make sure that the objects are in both locations and have their expected values.

Verifying the Export Results inside MIIS 2003

After a successful export of the staged updates to Active Directory you view the Export Statistics to verify that the test objects were exported to Active Directory. The following illustration show the result after clicking the Adds link in the Export Statistics dialog box.

To verify the export results inside MIIS 2003

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agents list, select MyADMA.

4. In the Export Statistics pane on the bottom left corner, click the Adds link.

5. Verify that all test users are listed.

Verifying the Export Results in the Connected Data Source

To verify whether the test objects were successfully exported to Active Directory, you can open Active Directory and browse to the correct organizational unit and verify that all the test objects are present. The following illustration shows the results in Active Directory after a successful export.

To verify the export results in Active Directory

1. Open Active Directory Users and Computers.

2. Browse to the MIISObjects organizational unit and verify the test users and group are present.

   Note

   The users do not have to be enabled in this scenario.

Importing the Data from Active Directory

The last step of the full synchronization cycle is an import of the data from the Active Directory. This step is also known as a confirming import. A confirming import is always required after export. To run a confirming import you need to run the full import run profile on the management agent called MyADMA.

To import data from Active Directory

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list, select MyADMA.

4. On the Actions menu, click Run to open the Run Management Agent dialog box.

5. In the list of run profiles, select Full Import.

6. To start the run profile, click OK.

Verifying the Import Results

You can verify the import results by using the connector space search again. However, this time you should modify the column settings. Since the value for SamAccountName is now imported from the connected data source, the connector space search returns values for these attributes.

With the import of the scenario objects from SQL Server management agent, the user and group synchronization is complete.

Step	Attribute
2	SamAccountName
3	member

To verify the inbound synchronization results

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list select MyADMA.

4. On the Actions menu, click Search Connector Space to open the Search Connector Space dialog box.

5. To open the Search Results Column Settings dialog box, click Column Settings.

6. For the row in the table immediately above this procedure complete the following steps:

   1. In Available Columns column, select the attribute shown for that row in the table.

   2. Click Add.

7. To close Search Results Column Settings dialog box, click OK.

8. To search the connector space, click Search.

Populate the Reference Table in SQL Server

After provisioning all of the test data into Active Directory successfully, the populating of the group membership can now be demonstrated. As stated earlier when an object in a single row has an attribute that can have more than one value, that attribute is referred to as a multi-value attribute. The group membership data is stored as a reference attribute, which contains multiple values. MIIS 2003 does not support a table in which multiple values exist in a single column. Therefore you have to create a secondary table in the database to store those values. The secondary table store the multi-value attributes.

The reference table was created previously when configuring the SQL Server database. The group membership information needs to be added to the reference table.

Each column in the reference table needs to be populated with the information in the table below.

ObjectID	AttributeName	ReferenceID
1	member	2
1	member	3
1	member	4
1	member	5

The ObjectID column references the group object in objects table, the member AttributeName column references the attribute we assigned to the users in the group that was synchronized to Active Directory, and the ReferenceID column references the ObjectID (unique identifier) that was set when creating the user objects in the objects table.

To populate the reference table.

1. Open Microsoft SQL Server.

2. Navigate to the ADObjects database, and open the tblReferences table.

3. Populate each column in the table with the information provided in the table above this procedure.

Test the Configuration Changes

The test of your configuration change consists of a delta synchronization cycle of the SQL Server configuration. This cycle consist of the following steps:

1. Importing the group membership data from SQL Server.

2. Verifying the import results.

3. Synchronizing data inside MIIS 2003.

4. Verifying the delta synchronization results.

5. Exporting the data to Active Directory.

6. Verifying the export results.

7. Importing data from Active Directory.

8. Verifying the import results.

Importing the group membership data from SQL Server

The first step in verifying the delta synchronization cycle, you need to import the reference table test data into the connector space of the SQL Server management agent. To accomplish this, you need to run a full import on the management agent called MySQLMA

To import group membership data into SQL Server

1. In MIIS 2003, open Identity Manager

2. Switch to the Management Agents view.

3. From the management agent list, select MySQLMA.

4. On the Actions menu, click Run to open the Run Management Agent dialog box.

5. In the list of run profiles, select Full Import.

6. To start the run profile, click OK.

Verifying the Import Results

As result of the successful import the group membership data is stage in the connector space of the SQL Server management agent. You can verify this by using the connector space search feature and opening the Connector Space Object Properties dialog box. The following illustration shows the properties of the group object.

As you can see the member attribute values are not directly visible. Instead, the New Value column has a button that you can click. When you click this button, a new dialog box that lists the values for this attribute opens. The following illustration shows the values for the member attribute.

To verify the import results

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list, select MySQLMA.

4. On the Actions menu. Click Search Connector Space to open the Search Connector Space dialog box.

5. To search the connector space, click Search.

6. In the list of object, select the distinguished name of the Group Object Type.

7. To open the Connector Space Object Properties dialog box, click Properties.

8. To open the View Attribute Details dialog, click the button in the New Value column of the member attribute.

9. Verify that all test users are listed as members.

10. Close the Search Connector Space dialog box.

Synchronizing data inside MIIS 2003

In this step, the SQL Server group membership data that is staged in the connector space of the SQL Server management agent needs to be provisioned into the connector space of the Active Directory management agent call MyADMA. To accomplish this you need to run a delta synchronization run profile on the management agent called MySQLMA.

To import synchronized data inside MIIS 2003

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list, select MySQLMA.

4. On the Actions menu, click Run to open the Run Management Agent dialog box.

5. In the list of run profiles, select Delta Synchronization.

6. To start the run profile, click OK.

Verifying the Delta Synchronization Results

The two phases of the synchronization run, inbound synchronization and outbound synchronization were explained earlier in this document. After performing a delta synchronization you have verify the inbound synchronization results and then the out bound synchronization results.

Verifying inbound synchronization results

You can verify whether all group membership objects have been successfully projected in to the metaverse by using the metaverse search feature. You should determine whether the member attribute of the group object has the expected values. In the metaverse the values of the member attribute are transformed into a different format. The values of the member attribute are references to objects. In the metaverse, each object has a unique metaverse GUID. The member attribute of a group in the metaverse GUID of the group member.

The following illustration shows the transformed metaverse member attribute of the group, G1.

To verify inbound synchronization results

1. In MIIS 2003, open Identity Manager.

2. Switch to the Metaverse Search view.

3. On the Actions menu, click Search to start a metaverse search.

4. To verify all objects are projected, review the list of returned objects.

5. In the SamAccountName column of the result list, select G1.

6. On the Actions menu, click Properties to open the Metaverse Objects Properties dialog box.

7. In the Attribute Name column, select the member row, and click the button in the Value column to open the View Metaverse Attribute Value Information dialog box.

8. To verify that each GUID in the Value column is the GUID of a group member, click the GUID in the Value column.

9. Close all open dialog boxes.

Verifying the outbound delta synchronization results

The values of the member attribute of the group object transforms to the distinguished name of the referenced object in the local connector space when the member attribute transitions to the connector space of the Active Directory management agent. The following picture shows an example for the member attribute in the connector space of the Active Directory management agent after provisioning.

To verify the outbound delta synchronization results

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list, select MyADMA.

4. On the Actions menu, click Search Connector Space to open the Search Connector Space dialog box.

5. To search the connector space, click Search.

6. In the list of object, select the object with the Object Type group.

7. To open the Connector Space Object Properties dialog box, click Properties.

8. To open the View Attribute Details dialog box, click the button in the New Value column of the member attribute.

9. Verify that all the test users are listed as members.

10. Close the Search Connector Space dialog box.

Exporting the data to Active Directory

The newly provisioned members of the group object in the connector space of the Active Directory management agent have to be exported to Active Directory. To accomplish this, you need to run the export run profile on the management agent called MyADMA.

To export data to SQL Server

1. In MIIS 2003, open Identity Manager.

2. Switch to Management Agents view.

3. From the management agent list, select MyADMA.

4. On the Actions menu, click Run to open the Run Management Agent dialog box.

5. In the list of run profiles, select Export.

6. To start the run profile, click OK.

Verifying the export results

After a successful export of the staged updates to Active Directory you can verify the export results by opening Active Directory and viewing the MIISObjects organizational unit and view the members of the group object.

To verify the export results in Active Directory

1. Open Active Directory Users and Computers.

2. Browse to the MIISObjects organizational unit and double click the group object, G1.

3. Click the Members tab and verify all the test users are members of the group.

Importing the Data from Active Directory

The last step in the delta synchronization cycle is an import from the Active Directory. This step is known as a confirming import. A confirming import is always required after an export.

To import the group membership data from Active Directory

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list select MyADMA.

4. On the Actions menu, click Run to open the Run Management Agent dialog box.

5. In the list of run profiles, select Full Import.

6. To start the run profile click OK.

Verifying the Import Results

You can verify the import results by looking at the statistics column of the management agent view within MIIS 2003. It shows one update has been performed on the group object in MIIS 2003.

To verify the inbound synchronization results

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list, select MyADMA.

4. In the Synchronization Statistics dialog box, click Updates.

5. In the Object Details dialog box and highlight the group object, and click Properties.

6. In the New Value column, click the ellipses button and verify that the Changes columns have the value of add for all the group members.

Summary

In this document, you have been introduced to the essential steps of synchronizing objects from a SQL Server database to Active Directory in a lab environment. You have learned how to configure MIIS so as to let SQL Server maintain an anchor and also how to configure the SQL Server tables to support multi-valued attributes.

As a next step, you should examine different SQL Server configurations on your scenario. For example you should determine the impact of adding one or more groups or users to your objects table and manipulating your reference table to accommodate the new groups with users added to those groups.

If you have questions or other feedback regarding this document, please feel free to post a message on the Microsoft Identity Integration TechNet Forum (https://go.microsoft.com/fwlink/?linkid=68184).

Appendices

Appendix A: Script to Create a Database

'Name       : Snapshot02.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the SQL database for the SQL walkthrough

Option Explicit

Const SQL_SERVER = "localhost"
Const DB_NAME    = "ADObjects"

Dim sqlCmd
sqlCmd = "CREATE DATABASE " & DB_NAME 
Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=master;" 
objCmd.ActiveConnection = objConnection
objCmd.CommandText = sqlCmd
objCmd.Execute
objConnection.Close

msgbox "Command completed successfully!", 64, "Create Database"

Appendix B: Script to Create the Objects Table

'Name       : snapshot03.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the objects table

Option Explicit
Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"

Dim tableDefinition
tableDefinition  = "CREATE TABLE [tblObjects] (" + _
             "[ObjectID] [int] IDENTITY (1, 1) NOT NULL ," + _
             "[ObjectType] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ," + _
             "[SamAccountName] [char] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ," + _
             "[Description] [char] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ," + _
                   ") ON [PRIMARY]"

Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & DATABASE_NAME & ";" 
objCmd.ActiveConnection = objConnection
objCmd.CommandText = tableDefinition  
objCmd.Execute
objConnection.Close

msgbox "Command completed successfully!", 64, "Create Objects Table"

Appendix C: Script to Populate the Objects Table

'Name       : Snapshot05.vbs.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the schema objects

Option Explicit
Const SQL_SERVER = "localhost"
Const DATABASE_NAME = "ADObjects"

'Add the list of SQL commands here:
Dim tableObjects
tableObjects = array("'Group', 'G1'", _
                     "'User', 'U1'", _
         "'User', 'U2'", _
                     "'User', 'U3'", _
                     "'User', 'U4'")

Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & DATABASE_NAME & ";" 
objCmd.ActiveConnection = objConnection

Dim tableObject
For each tableObject in tableObjects 

   objCmd.CommandText = "Insert into tblObjects (ObjectType, SamAccountName) " & _
                        "Values(" & tableObject & ")"
   

objCmd.Execute
Next

objConnection.Close

msgbox "Command completed successfully!", 64, "Create Schema objects"

Appendix D: Script to Create the Reference Table

'Name       : Snapshot03.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the multi-value table

Option Explicit
Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"

Dim tableDefinition
tableDefinition  = "CREATE TABLE [tblReferences] (" + _
            "[ObjectID] [int] NOT NULL ," + _
             "[AttributeName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ," + _
            "[ReferenceID] [int] NOT NULL" + _ 
                   ") ON [PRIMARY]"

Dim objConnection 
Set objConnection = CreateObject("ADODB.Connection")

Dim objCmd
Set objCmd = CreateObject("ADODB.Command")
objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                   "Trusted_Connection=Yes;Initial Catalog=" & DATABASE_NAME & ";" 
objCmd.ActiveConnection = objConnection
objCmd.CommandText = tableDefinition  
objCmd.Execute
objConnection.Close

msgbox "Command completed successfully!", 64, "Create References Table"

Appendix E: Script to Populate Active Directory Object

'Name       : Snapshot01.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to create the Active Directory sample data

Option Explicit
Const OU_NAME    = "MIISObjects"
Const ADS_PROPERTY_APPEND = 3 

Dim objRoot, objDomain
Set objRoot   = GetObject("LDAP://rootDSE")
Set objDomain = GetObject("LDAP://" & objRoot.Get("defaultNamingContext"))

'Create OU: 
Dim objContainer
Set objContainer = objDomain.Create("organizationalUnit", "OU=" + OU_NAME)
msgbox "Creating container: " + OU_NAME, 64,"CreateObjects"    
objContainer.SetInfo

msgbox "Command completed successfully!", 64, "CreateObjects"

Appendix F: Provisioning Code

Try
   Dim connectedMA As ConnectedMA
   connectedMA = mventry.ConnectedMAs("MyADMA")

   If connectedMA.Connectors.Count < 1 Then
      Dim myContainer As String
      myContainer = "OU=MIISObjects,DC=fabrikam,DC=com"

      Dim myRDN As String
      myRDN = "CN=" & mventry("samAccountName").Value

      Dim myDN As ReferenceValue
      myDN = connectedMA.EscapeDNComponent(myRDN).Concat(myContainer)

      Dim csEntry As CSEntry

      If (mventry.ObjectType.Equals("ADUser")) Then
         csEntry = connectedMA.Connectors.StartNewConnector("user")
      Else
          csEntry = connectedMA.Connectors.StartNewConnector("group")
      End If
      csEntry.DN = myDN
      csEntry.CommitNewConnector()
   End If
   Catch myEx As Exception
       Throw myEx
   End Try

Appendix G: HTA Code to View the Contents of the Table

<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>SQL Table Viewer</title>

<style type="text/css">
body      {background-color:wheat;}
table     {width:100%;font-family:Tahoma; font-size:70%;background-color:lightgrey;}
td.tbname {font-weight:bold; background-color:papayawhip;}
td.head   {font-weight:bold; background-color:buttonface;}
td.data   {background-color:cornsilk;}
</style>

<script type="text/vbscript">
<!--
Option Explicit

Const AD_OPEN_STATIC = 3
Const AD_LOCK_OPTIMISTIC = 3
Const AD_USE_CLIENT = 3

'Customizable parameters:

Const MAX_ROWS      = 100
Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"

'Add the names of the tables or views you want to display here:
Dim tableNames
tableNames = array("tblObjects", _
                   "tblReferences")

Sub GetTableData
   Dim objConnection
   Set objConnection = CreateObject("ADODB.Connection")
   objConnection.Open "Provider=SQLOLEDB;Data Source=" & SQL_SERVER & ";" & _ 
                      "Trusted_Connection=Yes;Initial Catalog=" & DATABASE_NAME & ";" 
   Dim objRecordset
   Set objRecordset = CreateObject("ADODB.Recordset")
   objRecordset.CursorLocation = AD_USE_CLIENT

   Dim tableName, tableData, oTable, oRow, oTbNameCell, oCell
   Set oTable = document.getElementById("dataTable")
      
   For Each tableName in tableNames
      Set oRow              = oTable.insertRow()
      Set oTbNameCell       = oRow.insertCell() 
      oTbNameCell.ClassName = "tbname"
      oTbNameCell.InnerText = tableName 
     
      objRecordset.Open "SELECT * FROM " & tableName , objConnection, _
                        AD_OPEN_STATIC, AD_LOCK_OPTIMISTIC
      
      If Not objRecordset.eof Then
         objRecordset.MoveFirst
         Set oRow   = oTable.insertRow()
         Dim curField
         For each curField in objRecordset.fields
            Set oCell  = oRow.insertCell() 
            oCell.ClassName = "head"
            oCell.InnerText =  Trim(curField.Name) 
         Next
        oTbNameCell.colSpan = objRecordset.fields.count
      End If
  
      Dim rowCounter 
      rowCounter = 0
      Do While Not objRecordset.eof
         Set oRow   = oTable.insertRow()
         For each curField in objRecordset.fields
            Set oCell  = oRow.insertCell() 
            oCell.ClassName = "data"
            oCell.InnerText =  Trim(curField) 
         Next
         objRecordset.MoveNext

         rowCounter = rowCounter + 1
         If rowCounter = MAX_ROWS Then
            Exit Do
         End If
      Loop
      objRecordset.Close
   Next
   objConnection.Close
End Sub
-->
</script>

<HTA:APPLICATION ID="oMyApp"
APPLICATIONNAME="SQLTableViewer"
APPLICATION="yes"
CAPTION="yes"
SINGLEINSTANCE="yes"
SYSMENU="yes">
</HTA:APPLICATION>
</head>

<body onLoad="GetTableData">
<table ID="dataTable"></table>
</body>
</html>