CSExport -- Getting the ILM Connector Space into SQL

How can I query the ILM Connector Space?

"You can't." -- Yes you can.

"You have to WMI." --  But WMI is limited in what you can query and slow

"You have to use CSExport and then use XML tools." -- that works, but this may be better

The above are various answers you may receive. However, thanks to the power of SQLXML we can issue SQL queries against the ILM Connector Space (after it has been exported using CSExport).

The following code will only work on SQL 2005. Some of the SQLXML tricks involved here have been described previously

So I will discuss some of the new tricks.

The csexport file spits out XML that looks a lot like entity attribute value schema and this means that each attribute gets its own row when you bring it into SQL. Since that doesn't work well for me to write further queries I use a pivot operator to make the data relational.

The code below will retrieve data from the pending import holograms of an AD connectorspace and doesn't handle multi-valued attributes.

 

SET NOCOUNT ON

DECLARE @ADXML TABLE (ADID int identity (1,1), XMLFROMCS XML)

INSERT @ADXML (XMLFROMCS)

SELECT * 

FROM OPENROWSET(BULK 'C:\FuzzyLogic\ADExport\ADExport.xml',SINGLE_BLOB) AS AD

DECLARE @ADXMLData XML

SELECT @ADXMLData = XMLFROMCS

FROM @ADXML

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @ADXMLData

TRUNCATE TABLE dbo.adUsers 

INSERT dbo.adUsers (dn, [cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title])

SELECT dn, [cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title]

FROM

    (

            SELECT dn, attrname, attrvalue  

            FROM OPENXML(@docHandle, N'/cs-objects/cs-object/pending-import-hologram/entry/attr/value',2)  

             WITH 

                (dn nvarchar(450) '../../@dn'

                ,primaryobjectclass nvarchar(450) '../../primary-objectclass'

                ,attrname nvarchar(450) '../@name'

                ,attrvalue nvarchar(450) '.'

                ,multivalued nvarchar(450) '../@multivalued'

                ) adusers

            WHERE adusers.primaryobjectclass = 'user' AND adusers.multivalued = 'false'

                AND attrname in ('cn','department','displayname', 'employeeid', 'givenName', 'mail', 'sn','title') 

        ) AS ADList1

    PIVOT (MIN(attrvalue) FOR attrname in ([cn],[department],[displayname],[employeeid],[givenname],[mail],[sn],[title])

 )AS ADUserPivot

EXEC sp_xml_removedocument @docHandle 

Other interesting changes in ILM RC0

From the release Notes:

http://technet.microsoft.com/en-us/library/dd239143.aspx

Announcing Identity Lifecycle Manager “2” Release Candidate

1) Don't create a multi-valued boolean attribute -- you will have to reinstall the ILM webservice

2) We can now create a required attribute binding without affecting existing objects!

3) support for separating the Portal from the database and having multiple portal servers

4) Enhancements for customizing notification and request emails

a) Request details will be included in the out of the box notifications

ILM 2 RC 0 is here!

Identity Lifecycle Manager "2" Release Candidate 0 is released.

 ILM '2' Release Candidate (RC)

Improvements at  First Glance

1. No need for "Managed:" to prefix metaverse attributes so that they can be managed by ILM 2 Web Service
2. Changes to Workflow to make it easier
3. Install routines improved
4. The confusing ARP file needed for custom activities has been replaced by an Object in the Data store
5. SQL 2008 is required

Is ILM 2 RC0 is out? Well the docs are!

Most of the online Technet docs now read "ILM "2" (Release Candidate)"

For example check out the new ILM 2 Release Candidate Installation Guide

http://technet.microsoft.com/en-us/library/cc561135.aspx

No more support for SQL 2005 from here on it is all SQL 2008

"SQL Server 2008 64-bit Standard or Enterprise Editions"

Good thing I just got back my score reports on my two SQL 2008 beta exams:

I am now an

MCTS: SQL Server 2008, Implementation and Maintenance

and

MCTS: SQL Server 2008, Business Intelligence Development and Maintenance

I guess that means I am qualified to keep working with ILM 2 ;)

ILM 2 Web Services Part 1 The Service Reference

Together, Mark Struck of Ipseity Inc and I, have figured out (after much beating of our heads against brick walls) how to use the ILM 2 Enumeration Endpoint to perform some basic reporting. (I figured out how to send the enumeration and get a response and then Mark figured out how to correctly form the pull messages so as to be able to retrieve the actual objects -- teamwork at its finest). We would also like to thank Mark Gabarra and Rob Ward for their input.

Here are some lessons we learned:

First lesson: the SDK provided with ILM 2 Beta 3 is incomplete and in some cases misleading. (Just one of those areas that hasn't been well documented yet)

Second lesson: Reading the WS-Enumeration specification is like drinking from a firehouse.

Third lesson: Case matters when specifying the endpoint.

Today's post will show you how to setup the Service Reference.

Type in http://localhost:526/ResourceManagementService/MEX/

The case of the url is important. R M S must be capitals and so must MEX.

The name you type in for name space is important as it is the name you will use in your code.

I recommend replacing the ServiceReference1 that you see in the figure with ILM_RMS.

After you click Go it shows you the various services available and operations for each service. The Search Service is the one we will want.

Once you click OK you see the following show up under service reference:

An enumeration.wsdl file is generated and your app.config file will also be populated with lots of settings such as this one.

   <binding name="ServiceMultipleTokenBinding_Search" closeTimeout="00:01:00"
                    openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
                    bypassProxyOnLocal="false" transactionFlow="false" hostNameComparisonMode="StrongWildcard"
                    maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
                    messageEncoding="Text" textEncoding="utf-8" useDefaultWebProxy="true"
                    allowCookies="false" contextProtectionLevel="Sign">
                    <readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
                        maxBytesPerRead="4096" maxNameTableCharCount="16384" />
                    <reliableSession ordered="true" inactivityTimeout="00:10:00"
                        enabled="false" />
                    <security mode="Message">
                        <transport clientCredentialType="Windows" proxyCredentialType="None"
                            realm="" />
                        <message clientCredentialType="Windows" negotiateServiceCredential="true"
                            algorithmSuite="Default" establishSecurityContext="false" />
                    </security>

You can also generate this info through a command line approach using the svcutil.exe utility.

Then in your code you make use of it like this as you see in my code:

Dim scReporting As ILM_RMS.SearchClient 
scReporting = New ILM_RMS.SearchClient("ServiceMultipleTokenBinding_Search")

See how to use the Namespace that you setup when you made the service reference, and how you need  use the binding name setup in the app.config file. Instead of using the settings in the config file you can use a programmatic approach to setting up the bindings. Look at the example from Mark Struck's C# code:

WSHttpContextBinding wsBinding = new WSHttpContextBinding(); 
// Cannot use WSHttpBinding since it does not allow you to Sign the EnumerationContext element 
// WsHttpContextBinding provides a property called ContextProtectionLevel which defaults to Sign, which is 
// what is needed to communicate with the web service when the action is Pull. 
// WsHttpBinding will work if you are just calling the web service with the Enumerate action. 
//WSHttpBinding wsBinding = new WSHttpBinding(); 

// Set binding properties 
wsBinding.ReceiveTimeout = new TimeSpan( 0, 5, 0); 
wsBinding.SendTimeout = new TimeSpan( 0, 5, 0); 
wsBinding.Security.Mode = SecurityMode.Message; 
wsBinding.Security.Message.EstablishSecurityContext = false; 
wsBinding.Security.Message.NegotiateServiceCredential = true; 
wsBinding.Security.Message.ClientCredentialType = MessageCredentialType.Windows; 
wsBinding.Security.Message.AlgorithmSuite = System.ServiceModel.Security.SecurityAlgorithmSuite.Default; 

// Create EndpointAddress object and create the SearchClient object with the binding and endpointaddress objects 
EndpointAddress ep = new EndpointAddress(ILMSERVICE_URI_ENUMERATION); 
SearchClient searchClient = new SearchClient(wsBinding, ep);

Under the hood of ILM 2 -- Part 2 Read the WCF Trace!

Take a look at Part 1 to enable tracing

To view the log you need to have installed the Windows SDK and then you use the Service Trace Viewer

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\SvcTraceViewer.exe

If the file is over 50 MB you will get the partial loading screen like this one. Try and limited the estimated size, if you open too much it will be very slow. Even 20 MB can be really slow.

If you need to adjust this after you open the file you can

To view what is happening you click on Activity 00000000000000 and then browse through the  actions

Most of it is just noise -- "The Policy Application Manager is executing" or "The Policy Application Manager has finished executing" (the first two showing below.

Take a look at the next action ExecuteQuery.ExecuteReader where in it is retrieving a list of Workflow Definitions objects (See the detail information where it shows the XPath query filter inside the parenthesis

Query: QueryProcessor.ExecuteQuery.ExecuteReader(/WorkflowDefinition)

The you can see how it retrieves the objects that were returned as part of the query

The process continues to retrieve objects and then sets up a WorkflowServiceHost for the workflows

Under the hood of ILM 2 -- Part 1 Enable WCF Tracing!

Want to understand what is happening with your custom ILM 2 workflow? or your calls to the web service?

Try enabling WCF Tracing. By enabling WCF tracing for the Identity Lifecycle Manager Resource Management Service you get to track requests to the webservice. This can help you figure out if your requests are even getting to the webservice.

To enable tracing open the config file:

C:\Program Files\Microsoft Identity Management\Common Services\

Microsoft.ResourceManagement.Service.exe.config

In the <configuration> after </configsections> and before <appsettings> add the following:

<!-- Enable Tracing -->
  <system.diagnostics>
    <trace autoflush="true" />
    <sources>
      <source name="Microsoft.ResourceManagement" switchValue="All">
        <listeners>
          <add name="text" type="System.Diagnostics.TextWriterTraceListener" initializeData="c:\logs\service.txt" traceOutputOptions="Timestamp,ThreadId,DateTime"/>
          <add name="xml" type="System.Diagnostics.XmlWriterTraceListener" initializeData="c:\logs\service.svclog" traceOutputOptions="Timestamp,ThreadId,DateTime"/>
        </listeners>
      </source>
    </sources>
  </system.diagnostics>
  <!-- End Enable Tracing -->

Then you need to create a directory called c:\ILMLogs and restart the ILM Common Services. If you don't create the directory then the logging still won't work, and you'll have to restart the service.

To view the log you need to have installed the Windows SDK and then you use the Service Trace Viewer

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\SvcTraceViewer.exe

For more info on how to read and interpret the trace see Part 2.

For more info on writing your own traces from your own code see Craig Martin's post on WCF tracing.

Happy Halloween -- It's all about Identity Management

Last night as I took my children trick or treating through our neighborhood I thought about Halloween from an Identity Management Perspective:

We provision temporary identities to our children (costumes) that allow them to make a claim when they show up at neighbors' doors "Trick or Treat (I am wearing costume -- the claim; will your grant me access to candy -- the resource request)?"

At which point the neighbor will almost invariably give out some candy.

But try showing up without a costume! Most have their permissions set up so that you can't get candy without a valid claim.

Then we as parent's provide a virus scanner -- looking over the candy to ensure that it is safe.

I am still trying to figure out how the pumpkins and Jack O'Lanterns fit into the metaphor. Happy Halloween!