Synchronizing Active Directory Objects to SQL Server

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, 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 what you need to do if you want to synchronize objects from Active Directory such as users and groups to a SQL Server database.

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 Active Directory data to a SQL Server database. After completing the procedures in this document, you will be able to:

• Let SQL server maintain the anchor attribute for objects.

• Implement different object types in SQL Server.

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

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 Active Directory, named MyADMA

• A management agent for SQL Server, named MySQLMA

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 metadirectory 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 SQL Server databases, including creating databases, tables, and views.

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

• 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).

For a description of all MIIS 2003 documentation, see Microsoft Identity Integration Server 2003 Documentation Roadmap (https://go.microsoft.com/fwlink/?LinkID=82465).

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 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 Active Directory as a primary source for user and group management data. Periodically, they use MIIS 2003 to synchronize this data from Active Directory to the central SQL Server database.

The 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 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 the information you must have to build an environment to synchronize objects from Active Directory to SQL Server. 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 TabbleViewer. 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 instructions 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 the Appendix.

Appendix	Description
Appendix A: Script to Populate Active Directory Objects	Script to populate Active Directory objects
Appendix B: Script to Create a Database	Script to create a database
Appendix C: Script to Create the Objects Table	Script to create the objects table
Appendix D: Script to Create the References Table	Script to create the references table
Appendix E: Script to Create the Schema Objects	Script to create the schema objects
Appendix F: The Provisioning Code Snippet	The provisioning code snippet
Appendix G Script to Provision to SQL	The ProvisionToSQL code
Appendix H Script to Clear the Objects Table	Script to clear the objects table
Appendix I: HTA Code to View the Content of the Tables for This Document	HTA code to view the content of the tables for this document

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

To implement TableViewer

1. In Appendix I, 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.

To run TableViewer

• In SQL Server, double-click the TableViewer icon.

Implementing the Procedures in This Document

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

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

1. Configure the Active Directory environment.

2. Configure the initial SQL Server 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.

Later topics provide more detail about these steps.

Note

You must complete the configuration of the connected data sources before you configure MIIS 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 a schema detection in a 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 Active Directory Environment

The Active Directory environment in this document is based on four test users and one security group. All objects are located in the organizational unit named MIISObjects.

The following illustration shows the Active Directory objects for this document.

As shown in the following illustration, the four test users (U1, U2, U3, and U4) are members of the security group (G1).

You can use the tools provided by SQL Server to create the Active Directory environment for this document or you can use the script in Appendix A to create the environment. For more information about using the supplied scripts, see Running the scripts.

All objects are located in the Active Directory organizational unit named MIISObjects.

Note

To complete the procedures in this document, you do not have to enable the users or assign passwords to them.

To create the required objects using SQL Server tools

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

To create the required objects 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 your 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 Initial SQL Server Environment

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

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

Required element	Description
One new SQL Server database	Stores all required data for this document.
One objects table	Stores the Active Directory data.
One references table	Stores the Active Directory data.
One data viewer (TableViewer)	Display the content of the scenario tables.

The complete SQL environment contains a delta view, also. You will find more details on developing the delta view later in this document.

Configuring the SQL Server database

To store the Active Directory and delta view data, you must create a database on the computer running SQL Server. The following illustration shows the database, named 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 or you can use the script in Appendix B to create the database. If you create the database manually, you must save it with the name ADObject. For more 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 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.

Configuring the SQL Server tables

To store the Active Directory and delta view data, you must create the following tables:

• One objects table

• One references 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

• DistinguishedName

• Description

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

The ObjectID column acts as 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 an 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 unique identifier calculated by SQL Server. Each new record is automatically 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 a unique identifier that is used 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 type 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 object types. The ObjectID and the ObjectType columns do not allow null values, but the other columns do. This is because you must add two dummy records as examples for different object types: one record for a user and one record for a group. When MIIS 2003 configures a management agent for SQL Server, it extracts the object type schema from the existing objects table. This is why this table must have one record for each supported object type when you configure a management agent for SQL Server.

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 not intuitive. More intuitive unique identifiers are samAccountName and distingusihedName, which are attributes for a security principle in Active Directory. In this document, we use intuitive attribute names 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 field 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 synchronized from Active Directory.

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
DistinguishedName	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 the 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 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 a reference attribute. A reference attribute contains a pointer to another object. The actual group membership data is stored in an attribute named 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 object in Active Directory.

In a common database, each row in the database represents a single object. For example, the following objects table has one row for each user and group object that was imported from Active Directory.

ObjectID	ObjectType	SamAccountName	DistinguishedName	Description
75	User	U2	CN=U2,OU=MIISObjects,DC=Fabrikam,DC=com	Test user
77	Group	G1	CN=G1,OU=MIISObjects, DC=Fabrikam,DC=com	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 has no standard table configuration for objects in a single row that have multi-value attributes. For example, the following objects table combines each value of a multi-value attribute into a single column (member).

ObjectID	ObjectType	SamAccountName	DistinguishedName	Description	Member
75	User	U2	CN=U2,OU=MIISObjects, DC=Fabrikam,DC=com	Test user	-
77	Group	G1	CN=G1,OU=MIISObjects, DC=Fabrikam,DC=com	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 than 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-value 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 used in this document. 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.

Adding schema objects to the objects table

This document supports multiple object types for the management agent for SQL Server. The object types are provided by the ObjectType column in the master table. When you configure your management agent for SQL Server, the supported object types must be available in the objects table. This is why you must add two schema records to the objects table: one for a sample user and one for a sample group.

The following table shows the initial content of the objects table.

tblObjects
ObjectID	ObjectType	SamAccountName	DistinguishedName	Description
1	User	null	null	null
2	Group	null	null	null

Note

The ObjectType is the only field that requires a specific value.

You must add two records to the objects table. For both records, you have to provide only the ObjectType data. Add one record with the user object type and one record with the group object type. For more information about using the scripts in this document, see Running the scripts.

You can use the tools provided by SQL Server to add the schema objects to the objects table or you can use the script in Appendix F to add the objects. If you add the objects manually, you must add two records to the objects table. For both records, only the ObjectType data is required. Add one record with the user object type and one record with the group object type. For more information about using the supplied scripts, see Running the scripts.

To add schema objects to the objects table using SQL Server tools

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

To add schema objects to the objects table 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.

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

Updating 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: DistinguishedName, SamAccountName, description, member

• ADUser: DistinguishedName, 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 DistinguishedName.

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

9. Click New attribute again.

10. In the Attribute name box, type SamAccountName.

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

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

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

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

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

Creating the Management Agents

For this document, you must create two management agents: one for Active Directory and one for SQL Server.

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, verify that only MIISObjects is selected.

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

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. Select ADUser.

   6. 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 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	<dn>	Distinguished Name
Rule 2	Description	Description
Rule 3	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	<dn>	Distinguished Name
Rule 2	Description	Description
Rule 3	sAMaccountName	SamAccountName
Rule 4	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 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.

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

To create the management agent for SQL Server, you 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

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 export 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 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	Distinguished Name	Distinguished Name
Rule 3	member	member
Rule 4	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 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 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	Distinguished Name	Distinguished Name
Rule 3	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.

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.

Configuring the Object Deletion Rule

In a scenario that synchronizes objects from Active Directory to SQL Server, it is important to include a solution for the case where objects are deleted in Active Directory. Because Active Directory 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 Active Directory 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 Active Directory 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	MyADMA
Rule 2	ADUser	MyADMA

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.

Enabling 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 the two object types—the user object type and the group object type—that you must implement in the provisioning method to decrease your code development time.

During provisioning, MIIS 2003 has to detect only whether a change was applied to an object that is of one of the new types. If it detects a change, it calls another custom method, ProvisionToSQL.

The following code shows an abbreviated version of the provisioning code.

Public Sub Provision(…) Implements …
   Try
      If (mventry.ObjectType.Equals("ADUser")) Then ProvisionToSQL(True, mventry)
      If (mventry.ObjectType.Equals("ADGroup")) Then ProvisionToSQL(False, mventry)

   Catch ex As Exception
      Throw ex
   End Try
End Sub

The ProvisionToSQL method reacts only if no connector to the SQL connector space is available. The ProvisionToSQL method uses the isUser Boolean parameter to determine the correct object type for a new connector.

In this document, the SQL Server database calculates and maintains the anchor for new objects.

To provision objects that will be exported to connected data sources that are in charge of managing the anchor attribute, you must assign a temporary, unique distinguished name to the attribute. You can create this distinguished name manually based on attributes on the metaverse object, or you can use the System.Guid.NewGuid method to create the temporary distinguished name. When the object is exported to the data source, the data source changes the distinguished name property from the temporary value to the permanent value.

The following code shows the ProvisionToSQL method.

Private Sub ProvisionToSQL(ByVal isUser As Boolean, _
                           ByVal mventry As MVEntry)
   Try
      Dim myMA As ConnectedMA = mventry.ConnectedMAs("MySQLMA")
      If myMA.Connectors.Count <> 0 Then Exit Sub

      Dim objectType As String = "Group"
      If isUser Then objectType = "User"

      Dim obCS As CSEntry
      obCS = myMA.Connectors.StartNewConnector(objectType)

      Dim DN As ReferenceValue
      DN = myMA.EscapeDNComponent(System.Guid.NewGuid().ToString)

      obCS.DN = DN
      obCS.CommitNewConnector()
   Catch ex As Exception
      Throw ex
   End Try
End Sub

Next, you build the metaverse rules extension, which is based on your provisioning code in Visual Studio .NET.

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. Copy the code for the ProvisionToSQL method from Appendix G, and then paste it under the Provision method.

12. From the Build menu, select Build Solution.

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

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

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

Configuring 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 agent for Active Directory and the management agent for SQL Server.

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

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	Delta Import	Delta Import (Stage Only)
Profile 2	Delta Synchronization	Delta Synchronization
Profile 3	Full Import	Full Import (Stage Only)
Profile 4	Full Synchronization	Full Synchronization

To create the 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 the table, and then click Next.

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

   4. Click Finish to create the run profile.

For this document, you use the Configure Run Profile Wizard to configure two parameters for each run profile.

The following illustration shows the Configure Run Profiles for dialog box after you have configured all run profiles for MySQLMA, the management agent for SQL Server.

The following table shows 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	Export	Export

To create the 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 the table, and then click Next.

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

6. Click Finish to create the run profile.

Testing the Configuration

The test of your configuration consists of a complete synchronization cycle of your Active Directory. This cycle consist of the following steps:

1. Deleting the Schema Objects in the Objects Table

2. Importing data from Active Directory

3. Verifying the import results

4. Synchronizing data inside MIIS

5. Verifying the synchronization results

6. Exporting the data to SQL Server

7. Verifying the export results

8. Importing data from SQL Server

9. Verifying the import results

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

Deleting the Schema Objects in the Objects Table

At this point, you can safely delete the schema object from the objects table. You can use the tools provided by SQL Server to delete the schema objects from the objects table or you can use the script in Appendix I to delete the objects. For more information about using the supplied scripts, see Running the scripts.

To delete the schema objects in the objects table using SQL Server tools

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

To delete the schema objects in the objects table using the script

1. In Appendix H 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:\AppendixH.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.

Importing Data from Active Directory

As a first step of the complete synchronization cycle, you need to import the Active Directory test data into the connector space of the Active Directory management agent. To accomplish this, you need to run a full import 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

As a result of a successful import, the Active Directory test data is staged in the connector space of the Active Directory 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.

In addition to verifying that all objects have been successfully staged in the Active Directory connector space, you should also determine whether the group object has the expected value for the member attribute. To do so, you need to open the Connector Space Object Properties dialog box. The following illustration shows the properties of the group object in the current scenario.

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 in the current scenario.

The import from Active Directory is successful when all test users of the current scenario are listed as members.

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.

7. In the list of objects, select CN=G1,OU=MIISObjects,DC=Fabrikam,DC=Com.

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

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

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

11. Close the Search Connector Space dialog box.

Synchronizing Data in MIIS

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

To import synchronize data inside MIIS

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 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 the group object have been successfully projected into the metaverse by using the metaverse search feature. Since the Active Directory management agent is configured to project all user and group objects into the metaverse, the metaverse search should return all four users (U1, U2, U3, U4) and the group (G1) as shown in the following illustration.

In addition to verifying that all objects are projected into the metaverse, you should also 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 is the metaverse GUID of the group member.

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

Step	Attributes
1	<object GUID>
2	DistinguishedName
3	SamAccountName
4	member

To verify the 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.

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

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

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

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

12. Close all open dialog boxes.

Verifying the outbound synchronization results

The steps to verify whether all objects have been successfully provisioned to the connector space of the SQL 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 SQL Server management agent. The following illustration shows the connector space search result after a successful full synchronization.

As you can see in this illustration, the distinguished name (also known as DN) of the objects has the temporary GUID assigned to the objects during provisioning as value. You should check the object properties by clicking on each entry in the search result list, which opens the Connector Space Object Properties dialog box. The following illustration shows an example for this dialog box.

The member attribute of the group requires again special attention. On the transition from the metaverse to the connector space of the SQL management agent, the values of this attribute are transformed again. Each member value is the distinguished name of the referenced object in the local connector space. The following picture shows an example for the member attribute in the connector space of the SQL Server management agent after provisioning.

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

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.

7. In the list of objects, select the object with the Object Type Group.

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

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

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

11. 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 statistics for that management agent should report 5 “Export Adds”.

To verify the management agent statistics

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. On the Actions menu, click Statistics to open the Statistics dialog box.

4. Verify that the Export Adds value for the management agent MySQLMA equals 5.

5. Close the Statistics dialog box.

Exporting the Data to SQL Server

The newly provisioned data in the connector space of the SQL management agent has to be exported to the SQL Server database. To accomplish this, you need to run the export run profile on the management agent called MySQLMA.

To export data to 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 Export.

6. To start the run profile, click OK.

Verifying the Export Results

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

Verifying the export results inside MIIS

After a successful export of the staged updates to the SQL Server database, the temporary distinguished name of the connector space objects is replaced by the distinguished name that the SQL Server has calculated. You can verify this by searching the connector space of the SQL Server management agent. The following illustration shows the result of a connector space search on the SQL Server management agent after an export.

As you can see in this illustration, the GUID values are replaced with the integer values calculated by SQL Server. The updated distinguished name values have also an impact on the values of the member attribute. As shown in the following illustration, the attribute values are updated with the new distinguished name values.

To verify the export results inside MIIS

1. In MIIS 2003, open Identity Manager.

2. Switch to the Management Agents view.

3. From the management agent list, select MySQL.

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.

7. In the list of objects, select the object with the Object Type Group.

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

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

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

11. Close the Search Connector Space dialog box.

Verifying the export results in the connected data source

To verify whether your scenario tables contain the expected data, you can use tools provided by SQL Server or use TableViewer. For more information about using TableViewer, see Configuring the database viewers.

The following illustration shows the database data returned by TableViewer.

Each object has an auto-generated anchor value, which is also used to store the group membership information in the references table.

To verify the export results in TableViewer

1. To run TableViewer, double-click the TableViewer icon from the folder where you have saved the .hta file.

   Note

   You must run TableViewer on the computer that is running SQL Server.

2. Verify that the objects table has five objects (four user and one group objects) and the references table has four records.

Importing the Data from SQL Server

The last step of the full synchronization cycle is an import of the data from the SQL Server. This step is also known as confirming import. A confirming import is always required after an export

To run a confirming import, you need to run the full import run profile on the management agent called MySQLMA.

To import data from 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

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

With the import of the scenario objects from the SQL Server management agent, the synchronization cycle is completed.

Step	Attributes
1	DistinguishedName
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 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.

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

8. To search the connector space, click Search.

Summary

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

As a next step, you should examine the impact of some Active Directory configuration variations on your scenario. For example, you should determine the impact of having a group member that is located in an organizational unit that is outside the MIIS container filter. Understanding the side effects of different source data configurations will help you design an optimal solution for your environment.

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 Populate Active Directory Objects

'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

'Create group: 
Dim objGroup
Set objGroup = objContainer.Create("Group", "cn=G1")
objGroup.Put "sAMAccountName", "G1"
msgbox "Creating group: G1",64,"CreateObjects"
objGroup.SetInfo

'Create users and add them to new group: 
Dim i, szSamName, objUser
For i = 1 To 4
    szSamName = "U" & i
    Set objUser = objContainer.Create("User", "CN=" + szSamName)
    objUser.Put "sAMAccountName", szSamName
    msgbox "Creating user: " + szSamName,64,"CreateObjects"
    objUser.SetInfo

    objGroup.PutEx ADS_PROPERTY_APPEND, "member", _
                   Array(objUser.distinguishedName)

    msgbox "Adding user to group: " + szSamName,64,"CreateObjects"
    objGroup.SetInfo
Next

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

Appendix B: 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 C: 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 ," + _
             "[DistinguishedName] [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 D: Script to Create the References 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 Create the Schema Objects

'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("User", _
                     "Group")

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) " & _
                        "Values('" & tableObject & "')"
   objCmd.Execute
Next

objConnection.Close

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

Appendix F: The Provisioning Code Snippet

Try
      If (mventry.ObjectType.Equals("ADUser")) Then ProvisionToSQL(True, mventry)
      If (mventry.ObjectType.Equals("ADGroup")) Then ProvisionToSQL(False, mventry)

   Catch ex As Exception
      Throw ex
   End Try

Appendix G: Provision to SQL Code

Private Sub ProvisionToSQL(ByVal isUser As Boolean, _
                           ByVal mventry As MVEntry)
   Try
      Dim myMA As ConnectedMA = mventry.ConnectedMAs("MySQLMA")
      If myMA.Connectors.Count <> 0 Then Exit Sub

      Dim objectType As String = "Group"
      If isUser Then objectType = "User"

      Dim obCS As CSEntry
      obCS = myMA.Connectors.StartNewConnector(objectType)

      Dim DN As ReferenceValue
      DN = myMA.EscapeDNComponent(System.Guid.NewGuid().ToString)

      obCS.DN = DN
      obCS.CommitNewConnector()
   Catch ex As Exception
      Throw ex
   End Try
End Sub

Appendix H: Script to Clear the Objects Table

'Name       : ClearObjectsTable.vbs 
'Author     : markvi@microsoft.com
'Date       : 12/12/06
'Description: Script to clear the content of the Objects table

Option Explicit

Const SQL_SERVER    = "localhost"
Const DATABASE_NAME = "ADObjects"
Const SQL_CMD       = "Delete from tblObjects"

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 = SQL_CMD 
objCmd.Execute
objConnection.Close

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

Appendix I: HTA Code to View the Content of the Tables for This Document

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