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.

The Semi-Automated Install of ILM 2 Beta 3

ILM 2 Beta 3 won't perform a completely automatic quiet install but we can come close. Colleague Brad Turner and I have developed the following approach to the install and the post install tasks.

Brad worked out most of the issues with the ILM 2 Services install itself and then I worked on most of the issues with the post install tasks. I will cover the install of the Metadirectory services first, then the ILM 2 Beta 3 Identity Management Platform Services including its batch files and then discuss the post install tasks and present its related files.

First up the install of the Metadirectory services. At this point I assume you have covered the prerequisites mentioned in the ILM "2" Beta 3 Installation Guide (of course we posted some of this to the community content there).

Be sure and put in your own preexisting AD groups and path to the installation folder, as well as service account and password.

InstallSync.cmd

@echo off
rem This section specifies Group names, adding the domain\ in front configures them as a domain based group
set GROUPADMINS="info\ILM Admins"
set GROUPOPERATORS="info\ILM Operators"
set GROUPACCOUNTJOINERS="info\ILM Joiners"
set GROUPBROWSE="info\ILM Browse"
set GROUPPASSWORDSET="info\ILM PasswordSet"

rem ILM or DB directory?
set DBFileLocation=SQLDefault
set DBFILEMMSLOCATION="0"

rem To Use local server and instance (Default):
set SQLServerStore=LocalMachine
set SQLServerInstance=DefaultInstance

rem Installation Folder for x64
set INSTALLDIR64="E:\Program Files\Microsoft Identity Integration Server"

rem SERVICEACCOUNT is the Sync Engine Account
set SERVICEACCOUNT=svc.ilmsync
rem SERVICEDOMAIN is the domain the Sync Engine Account is in
set SERVICEDOMAIN=info
rem SERVICEPASSWORD is the password for the Sync Engine Account
set SERVICEPASSWORD=P@$$w0rd

msiexec /i "Identity Lifecycle Manager Evaluation.msi" /norestart /log setup.txt SERVICEACCOUNT=%SERVICEACCOUNT% SERVICEDOMAIN=%SERVICEDOMAIN% SERVICEPASSWORD=%SERVICEPASSWORD% DBFILEMMSLOCATION=%DBFILEMMSLOCATION% SQLServerStore=%SQLServerStore% SQLServerInstance=%SQLServerInstance% DBFileLocation=%DBFileLocation% GROUPADMINS=%GROUPADMINS% GROUPOPERATORS=%GROUPOPERATORS% GROUPACCOUNTJOINERS=%GROUPACCOUNTJOINERS% GROUPBROWSE=%GROUPBROWSE% GROUPPASSWORDSET=%GROUPPASSWORDSET% DBFILEMMSLOCATION=%DBFILEMMSLOCATION% INSTALLDIR64=%INSTALLDIR64%

Brad and I like to use environmental variables defined in the batch file to "self-document the batch file." Since the install and the post install tend to reuse many of the same settings I moved all of the environmental variables into one batch file which is then called from the InstallSever.cmd file and the PostInstallTasks.cmd file. This file is called SetInstallVariables.bat:

@echo off

set MAIL_SERVER="mail.ensynch.info"
set SERVICE_ACCOUNT_NAME=svc.ilmws
set SERVICE_ACCOUNT_PASSWORD=P@$$w0rd
set SERVICE_ACCOUNT_DOMAIN=info
set SERVICE_ACCOUNT_EMAIL="svc.ilmws@ensynch.info"
set RMS_PORT=526
set SERVICEADDRESS=localhost
set STS_PORT=527
set SHAREPOINT_PWD_RESET_SITE_URL="http://%COMPUTERNAME%/PasswordPortal/"
set SHAREPOINT_SITE_URL="http://localhost/identitymanagement/"
set SQLSERVER_SERVER="."
set SYNCHRONIZATION_SERVER_ACCOUNTNQ=info\svc.ilmma
set SYNCHRONIZATION_SERVER_ACCOUNT="%SYNCHRONIZATION_SERVER_ACCOUNTNQ%"

SET WSSSTSADM="%commonprogramfiles%\microsoft shared\web server extensions\12\bin\stsadm"

SET INTIAL_EMAIL_ALIAS=%USERNAME%@%USERDNSDOMAIN%
SET INITIAL_DESCRIPTION="%USERNAME% Initial Admin for ILM Portal"

rem Don't work...
set SQMOPTINSETTING=0
set MAIL_SERVER_IS_EXCHANGE=0
set MAIL_SERVER_USE_SSL=0

rem Shows up in the UI, but doesn't apply...
rem set INSTALLDIR="E:\Program Files\Microsoft Identity Management\"

The installServer.cmd file:

@echo off

CALL SETINSTALLVARIABLES.bat

msiexec /i ilm-server-64bit.msi /log ilmserverx64.txt ACCEPT_EULA=1 MAIL_SERVER=%MAIL_SERVER% SERVICE_ACCOUNT_NAME=%SERVICE_ACCOUNT_NAME% SERVICE_ACCOUNT_PASSWORD=%SERVICE_ACCOUNT_PASSWORD% SERVICE_ACCOUNT_DOMAIN=%SERVICE_ACCOUNT_DOMAIN% SERVICE_ACCOUNT_EMAIL=%SERVICE_ACCOUNT_EMAIL% RUNNING_USER_EMAIL=%USERNAME%@%USERDNSDOMAIN% MAIL_SERVER_IS_EXCHANGE=%MAIL_SERVER_IS_EXCHANGE% MAIL_SERVER_USE_SSL=%MAIL_SERVER_USE_SSL% RMS_PORT=%RMS_PORT% SERVICEADDRESS=%SERVICEADDRESS% STS_PORT=%STS_PORT% SHAREPOINT_PWD_RESET_SITE_URL=%SHAREPOINT_PWD_RESET_SITE_URL% SHAREPOINT_SITE_URL=%SHAREPOINT_SITE_URL% SQLSERVER_SERVER=%SQLSERVER_SERVER% SQMOPTINSETTING=%SQMOPTINSETTING% SYNCHRONIZATION_SERVER_ACCOUNT=%SYNCHRONIZATION_SERVER_ACCOUNT%

After installation of ILM 2 Beta 3 you have several post install tasks per the ILM "2" Beta 3 Installation Guide:

1. Grant Full Control rights to the ILM "2" SharePoint site to the initial user of the site
2. Grant user rights for the ILM “2” Windows SharePoint Services site to domain users who require it
3. Configure the ILM “2” Password Management Portal for anonymous access
4. Disable SharePoint Indexing
5. Exchange Server 2007 Web Service (EWS) Configuration
6. Exchange Server 2007 Certificate installation
7. ILM MA permissions (SQL permissions)
8. Verify ILM Service account group membership
9. ILM “2” Web Portal Access

For items 1 and 2 the guide provides a command line but for steps 3-9 the guide only provides steps that must be done through the GUI.

With the help of some stsadm custom extensions written by SharePoint MVP Gary LaPointe we can easily automate step #3. We will use gl-setanonymousaccess

Step 4 could be automated by using the following standard stsadm command to stop the Search service

stsadm -o osearch -action stop -f

Or this could be handled during your WSS 3.0 install, which is how we did it. I'll have to ping another Ensynch colleague Jeff Holliday (he calls his blog the SharePoint Redemption)  to see how he did that when he created our install for WSS 3.0

Steps 5 and 6 are manual as is 9 (well 9 is pretty involved), but 7 (ILM MA user account SQL Permissions) is easy to automate with a SQL Script. (For the time being I am going to be lazy about step 8 -- which could be automated but which I leave as an exercise to the reader).

We need to create a login for the account we specified for the ILM 2 MA, grant it a user in the MSILM database and make it a member of the db_owner fixed database role.

You'll see that I took advantage of sqlcmd's ability to do some preprocessing replacement using parameters or environmental variables. In this case I used environmental variables. You can see wherever it says [$(something)] -- like this: [$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)] which is set in the SetInstallVariables.bat file

These environmental variables are set in a batch file that calls sqlcmd to execute this file: ILMMA_Permissions.sql

USE [master]

CREATE LOGIN [$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)] FROM WINDOWS WITH DEFAULT_DATABASE=[MSILM]
GO

USE [MSILM]
GO
CREATE USER [$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)] FOR LOGIN [$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)]
GO

EXEC sp_addrolemember N'db_owner', N'$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)'

GO
DECLARE @myvar int
SELECT @myvar = (SELECT CASE
WHEN 1 = (SELECT COUNT(*) FROM sys.syslogins where name = '$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)')
AND 1 = (SELECT COUNT(*) FROM sys.database_principals WHERE name ='$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)' )
AND 1 = (SELECT COUNT(*)
FROM sys.database_role_members
WHERE member_principal_id =
(SELECT top 1 principal_id FROM sys.database_principals WHERE name ='$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)' )
AND role_principal_id =
(SELECT top 1 principal_id FROM sys.database_principals WHERE name ='db_owner')
) THEN 0
WHEN 0 = (SELECT COUNT(*) FROM sys.syslogins where name = '$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)')
THEN 1 -- Couldn't create Login
WHEN 0 = (SELECT COUNT(*) FROM sys.database_principals WHERE name ='$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)' )
THEN 2 -- Couldn't map user to MSILM database
WHEN 0 = (SELECT COUNT(*)
FROM sys.database_role_members
WHERE member_principal_id =
(SELECT top 1 principal_id FROM sys.database_principals WHERE name ='$(SYNCHRONIZATION_SERVER_ACCOUNTNQ)' )
AND role_principal_id =
(SELECT top 1 principal_id FROM sys.database_principals WHERE name ='db_owner')
)
THEN 3 -- Couldn't assign user to db_owner role
ELSE 4 -- unknown error
END)
EXIT(SELECT @myvar)

Here is the PostInstallTasks.cmd file:

@echo off

CALL SETINSTALLVARIABLES.bat

sqlcmd -S %SQLSERVER_SERVER%  -E -i ILMMA_Permissions.sql
if {%errorlevel%} == {4} (Echo  Unknown SQL Error
                goto SQLPermissionsError)
if {%errorlevel%} == {3} (Echo  Couldn't assign user %SYNCHRONIZATION_SERVER_ACCOUNTNQ% to db_owner role
                goto SQLPermissionsError)
if {%errorlevel%} == {2} (Echo  Couldn't map user %SYNCHRONIZATION_SERVER_ACCOUNTNQ% to MSILM database
                goto SQLPermissionsError)
if {%errorlevel%} == {1} (Echo  Couldn't create Login %SYNCHRONIZATION_SERVER_ACCOUNTNQ% On SQL Server
                goto SQLPermissionsError)

echo %WSSSTSADM% -o adduser -url %SHAREPOINT_SITE_URL% -userlogin %USERDOMAIN%\%USERNAME% -useremail %INTIAL_EMAIL_ALIAS% -username %INITIAL_DESCRIPTION%  -role "Full Control"
%WSSSTSADM% -o adduser -url %SHAREPOINT_SITE_URL% -userlogin %USERDOMAIN%\%USERNAME% -useremail %INTIAL_EMAIL_ALIAS% -username %INITIAL_DESCRIPTION%  -role "Full Control"
echo Done Setting access for initial user
echo %WSSSTSADM% -o adduser -url %SHAREPOINT_SITE_URL% -userlogin "%SERVICE_ACCOUNT_DOMAIN%\Domain Users" -useremail users@%USERDNSDOMAIN% -username "Domain Users" -role "Contributor"
%WSSSTSADM% -o adduser -url %SHAREPOINT_SITE_URL% -userlogin "%SERVICE_ACCOUNT_DOMAIN%\Domain Users" -useremail users@%USERDNSDOMAIN% -username "Domain Users" -role "Contributor"

REM comes from here http://stsadm.blogspot.com/2008/03/set-anonymous-access.html
echo Using This tool from http://stsadm.blogspot.com/2008/03/set-anonymous-access.html   to set anonymous access
%WSSSTSADM% -o gl-setanonymousaccess -url %SHAREPOINT_PWD_RESET_SITE_URL% -anonstate entireweb
if {%errorlevel%} NEQ {0} goto oopsNeedCustomstsadm

goto end

:SQLPermissionsError
echo please  run and troubleshoot ILMMA_Permissions.sql in SQL Management studio
echo remember to replace $(SYNCHRONIZATION_SERVER_ACCOUNTNQ) with %SYNCHRONIZATION_SERVER_ACCOUNTNQ%
goto end

:oopsNeedCustomstsadm
echo go download http://www.thelapointes.com/blog/stsadm.zip then run Package\ReleaseWSS\deploy.bat
echo if the deploy.bat doesn't work then change the first line to have the .wss.wsp like so
echo SET SOLUTION_NAME="Lapointe.SharePoint.STSADM.Commands.wss.wsp"

:end

SQL Server Agent should be running or install of ILM 2 Services fails

I posted the following to the Community Content Section of the ILM 2 Beta 3 Installation Guide

The SQL Agent Service account must be a sql sysadmin and the SQL Agent Service must be running or during install you may get "error -2147217900

Failed to execute sql string addtemporaleventsjobtoSQLServer" while trying to install ILM 2 Beta 3 Identity Management Platform Services. Apparently, the install routine needs to create a SQL Agent Job and with SQL 2005 the Agent must be running to create a job.

The job it creates is called ILM_TemporalEventsJob and according to its description it "Periodically identify workflows to be run on objects that have transitioned to or from temporal sets." It is scheduled to be run every day at 1 AM.

It has only one step of type T-SQL: EXEC dbo.TriggerTemporalEvents. So later on if you find that objects are not getting transitioned to and from temporal sets you might need to come and check this job's history, and ensure that the SQL Agent is running.

Projections showing up as Joins?

https://connect.microsoft.com/feedback/ViewFeedback.aspx?FeedbackID=373881&SiteID=433

So I found a slight inconsistency when following some of the ILM 2 walk-throughs. When you setup an inbound synch rule that creates objects in ILM the lineage says that the connector space object became a connector through join rules instead of projection rules. Minor bug -- but it sure can be confusing.

HR Inbound Sync Rule

General Information

Created Time

8/27/2008 8:10:09 PM

Connected System

{0cd165ec-2745-4afd-95c0-a8f7dbeefe44}

Connected Object Type

person

ILM Object Type

managed:Person

Precedence

1

Create ILM Object

True

Create Connected System Object

False

Disconnect Connected System Object

False

Flow Type

Inbound

Relationships

ILM Attribute

Data Source Attribute

managed:EmployeeID

EmployeeID

Parameters

Parameter Name

Type

Initial Import Flows

Destination

Source

managed:EmployeeID

EmployeeID

Persistent Import Flows

Destination

Source

managed:AccountName

UserID

managed:Company

Company

managed:FirstName

FirstName

managed:LastName

LastName

managed:Manager

Manager

managed:EmployeeType

EmployeeType

managed:DisplayName

FirstName  +  " "  +  LastName

MIIS/ILM Error: System.BadImageFormatException

So I had MIIS 2003 SP 1 reporting to me that the format of my GalSync-Extension.dll is invalid. So I tried recompiling it -- no luck. Same error. The only MSDN article on this indicated that unmanaged code is being passed to the load method.

Through trial and error we found the solution: stop and start the MicrosoftIdentityIntegrationService. If that doesn't work try a reboot.

SQL 2008: Processor or Server/CAL

Congrats to the SQL Server team for shipping 2008. It looks like a great product. Congrats as well for keeping the licensing costs the same and adding a new option with the web edition.

One question that many still have in mind is how to license SQL server. Processor licensing allows unlimited users and devices, whereas a server license allows unlimited users or devices as long as they have a CAL. Server CAL can be much cheaper than Processor license or it can become much more expensive.

Many ILM customers also have this question, and while the product team pushes you to processor licenses and says that if you want to go Server/CAL you need 1 CAL for each user that will connect directly to the SQL server (in case it does other stuff, or your ILM admins like to run unsupported queries under the hood) for each Management agent (how in the world did 1 device -- the ILM server -- become multiple devices and need many CALs? -- Maybe under some sort of multiplexing scenario since the data is getting pushed to other places) each situation will be different.

So I have developed a formula and a table to help you figure this out

Per Processor Licensing Costs =
Cost Per Processor * # of Servers * Avg CPUs per Server

Server + CAL Licensing Cost =
Cost Per Server * # of Servers + Cost Per CAL * # of CALs

Break Even Formula
# of CALs = (Cost Per Processor * Avg CPU per Server - Cost Per Server) * # of Servers/Cost Per CAL

As you can see the break even point between Server/CAL and Processor comes down to a ratio between CALs divded by the number of servers vs Processors (not cores, but physical packages) divded by the number of Servers. (Remember that one CAL allows one user or device to access an unlimited number of licensed servers -- you don't have to buy a CAL for each server you want to access. See http://download.microsoft.com/download/2/d/f/2df66c0c-fff2-4f2e-b739-bf4581cee533/SQLServer%202008CompareEnterpriseStandard.pdf)

Using the sample licensing costs from Microsoft's site

Edition	Workgroup*	Standard	Enterprise
Per Processor	$         3,700	$       5,737	$     23,911
Per Server	$            730	$         885	$       8,487
Per CAL	$            146	$         162	$          162

 

Using these sample numbers (and your costs maybe different)

 

Break Even Points in terms of CALs per Server vs CPU/Server
	Workgroup	Standard	Enterprise
CPUs/Server	CALs/Server	CALs/Server	CALs/Server
1	25	30	95
2	51	65	243
4		136	538
6			833
8			1,128
10			1,424
12			1,719
14			2,014
16			2,309
18			2,604
20			2,900
32			4,671
64			9,394

 

10 SQL Enterprise Edition servers with an average of 2 quad core processors (that is still just two processors for licensing purposes) and I have less than 2430 (243x10) devices or users to license and am likely to maintain these ratios than Server/CAL should be cheaper than processor licensing. 20 processor licenses at $478,220 and 10 server licenses $84,870 plus $393,350 cost the same. Hiring more people and/or acquiring more devices might tip the balance, but acquiring more SQL servers or adding processors to existing boxes could counter that.

As you can see this is really an enterprise wide decision how will I license SQL servers for my organization.

What to do about users coming in from the web, well you can use processor licensing for those SQL servers or you could go with the web edition for $15 per processor per month. What about data on internal servers? Replicate it to the web edition server! SQL to SQL communication does not require a CAL.

Remember that "SQL Server 2008 Web may be used only to support public and Internet-accessible Web pages, sites, applications, and services."

In terms of features it is comparable to Workgroup edition, so none of the high availability features like clustering or mirroring are supported, only log shipping. There are several minor differences in the functionality of Web vs Workgroup

No ad hoc reporting through report builder, in service broker it can only be a client and its development tools come from SQL Express Management studio.

http://msdn.microsoft.com/en-us/library/cc645993.aspx

Pending Exports Report in ILM

Hopefully this topic will stir up some excitement among those wondering how to query objects in the connector space. The technique I am about to explicate for you works for both exports and imports.

As many of you aware, my colleague and fellow ILM MVP Brad Turner created the community reporting pack for MIIS/ILM some time ago. This is a package of reports written in SQL Server Reporting Services (SSRS).

Most of you are also aware that you can tell an import or export run profile step to drop an audit file. The audit file is in DSML format (an XML format). You can use XML files as the source for SSRS reports, they can also be used.

A later report he created was for Pending Exports, to show clients what records are about to be exported (drop audit file and stop) or what records were just exported (drop audit file).

1) Turn on the drop audit file for the export run profile step.

2) Create a virtual directory in IIS that points to the MAData subfolder location and allows you to see the file

3) Create a data source in SSRS for that file and only that file. This means you have to create a data source for each audit file. Wow -- doable but painful!

A short while ago I took this process and made it even slicker. I present the background of all this to show why Brad and I form the nucleus of a great team. I had not thought of creating a report based on the audit file. I viewed the audit file as a troubleshooting technique, not as a great way to be able to report on exports or imports. My inspiration was how to make this more flexible.

I created a stored procedure (only works on SQL 2005) that uses SQLXML (specifically the sp_xml_preparedocument and OpenXML to shred the XML data to relational data). 

Additionally, I created the stored procedure so that it can accept a lot of parameters, allowing us to report the pending exports (or just exported) for any of the MAs).

That means that we only need one data source -- pointing to the database that houses the stored procedure.

First allow me to demonstrate the basic technique:

SET NOCOUNT ON

DECLARE @ADXMLData XML

SELECT @ADXMLData = BulkColumn 
FROM OPENROWSET(BULK 'C:\Ensynch_projects\Reports\ILMReports\copy of admaexports.xml',SINGLE_NCLOB) AS AD

DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @ADXMLData, '<mmsml xmlns:a="http://www.microsoft.com/mms/mmsml/v2"/>'

            SELECT * 
            FROM OPENXML(@docHandle, N'//a:mmsml/a:directory-entries/a:delta/a:dn-attr/a:dn-value/a:dn',2) 
             With (
                    DeltaOp varchar(100) '../../../@operation'
                    ,DNAOp varchar(100) '../../@operation'
                    ,DNVOp varchar(100) '../@operation'
                    --,ObjType varchar(50) '../../../primary-objectclass'
                    ,ObjectDN varchar(1000) '../../../@dn'
                    ,AttrName varchar(100) '../../@name'
                    ,dn        varchar(1000) '.'
            ) Export
ORDER BY GroupDN

EXEC sp_xml_removedocument @docHandle

For this query I was first focused on some group updates. I need to show the client how we were going to update their distribution lists.

This query takes the XML from the DSML file and shreds it back to relational data like so

 

update	add		CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=MontyHALL, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add		CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=Joe Montana, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add		CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=Steve Young, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add		CN=Group1, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=Fred Idaho, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add	add	CN=Group2, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=Fred Idaho, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add	add	CN=Group2, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=MontyHALL, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org
update	add	add	CN=Group2, OU=Distribution Groups, OU=Enterprise Groups, DC=Aclient,DC=org	member	CN=Joe Montana, OU=Groupwise Directory Sync - SJHS,OU=Exchange, DC=Aclient,DC=org

 

Next week I will show how to add the parameters and then I will show how to make the report. If you are lucky I might even make a video and post it!

Tech Ed -- Lotsa Buzz ILM 2 and CLM

On Tuesday Bob Muglia made a big announcement -- ILM 2 Beta 3 has been released. While the beta install is only 64 bit on Microsoft Connect you can download the 32-bit Virtual PC. At the ILM 2 booth at Tech Ed the Microsoft ILM Product Group and I were handing them out like crazy.

Thanks to Nima for inviting me to participate at the booth.

Best session I went was by Candy Stark from MS IT. She presented on the smart card deployment at MSFT using CLM.

Processing Actions Asynchronously outside of ILM MA's

For years developers have had access to the Microsoft Message Queue (MSMQ) as a way to be able to queue up actions for processing later or on a remote machine. With the release of SQL 2005 back in well 2005, developers with access to SQL 2005 could replace these MSMQ apps with SQL Service Broker Queues (SSB). With ILM 2007 /MIIS 2003 SP 2 supporting SQL 2005 the use of SQL Service Broker Queues became much more accessible to ILM Developers.



Here is an article comparing MSMQ with SQL Service Broker Queues

http://www.devx.com/dbzone/Article/34110



Here it is another discussion on the matter in an MSDN forum:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=661768&SiteID=1



Here is an interesting blog post about working with both SQL Service Broker Queues (SSB) and

Windows Workflow Foundation:

http://devhawk.net/2006/12/11/Transactions+In+Workflow+Foundationland.aspx

The DevHawk has many good insights into SSB.



One huge advantage to me in dealing with SSB is that I can query a view just like a table to examine its current contents. SSB is also very easy to scale out.

At DEC 2008 Craig Martin and Marshall Hamilton (both from OCG North America) spoke on the SSH MA (originally developed by Patrick Rempel from OCG Germany) which is being used to manage thousands of Unix servers (well together multiple instances of the MA are managing several thousand servers) through SSH connections and issuing he command line commands to create users etc.

One problem they had to solve was what if one server is down during import? Regurgitate data for systems that are down which necesitates caching the data somewhere. Another problem I envision in such scenarios is that performance is probably an issue. Imagine a slightly different design.

What if an intermediate database was used to hold the results from the servers. Importing data from the servers on separate schedules, then importing to ILM on yet another separate schedule. The requests to import could be popped into a queue for multiple processes to implement. For executing exports have the XMA pop the requests into a SSB queue and then have a process that pulls info from the queue. If performance on export becomes an issue it is child's play to scale out with SSB, add another process that pulls import request messages from the queue.

Another use case for SSB is to replace anytime MSMQ has been used. Benefits: SSB can be clustered for failover, back up is as simple as backing up the database hosting the SSB queues.

For a Gentle Intro to SSB

Identity Chaos? Get your Identities Ensynch!

I recently joined the Identity Management Practice, as a Solution Architect, at Ensynch Inc, an award winning Microsoft Gold Partner based in Tempe, AZ, with a strong award winning presence in Southern California. In 2006 Ensynch won Microsoft Worldwide Partner Award for Excellence in Active Directory and Identity Management and in 2007 was the only finalist from North America. In fact the only finalist from the Western Hemisphere.

I am especially happy to be working with Brad Turner, fellow ILM MVP and a good friend. I am also excited to work with the creator of the Camel Logic Configurator -- Jerry Camel.

The Grand Unified Demo of Identity Management

As I was architecting and assembling the Identity All Up workshop (part of the 2008 Directory Experts Conference see the review by Felix Gaehtgens, an analyst for Kuppinger Cole) designed to expose the attendees (or delegates) to all facets of the Microsoft Identity Access Platform, Lori Craw, from Microsoft referred to this as the "Grand Unified Demo". I chuckled, instantly catching the reference to the still undiscovered Grand Unified Field theory that eluded Einstein and even today's theoretical physicists.

In creating and delivering this workshop, I have reinforced, my earlier belief that the Active Directory (AD) is the medium through which most of these interactions happen that allow for interactions between these components of the platform, and Identity Lifecycle Manager (ILM) is the driving force.

Allow me to explain -- In order to manage the lifecycle of smart cards through Certificate Lifecycle Manager (CLM) you must belong to groups in AD that have been assigned permissions to the CLM Service Connection Point, the CLM Profile Template, the CLM Certificate Template, and a group that contains the user upon whom you will act. How do you get into these groups? Through Identity Lifecycle Manager! So AD is the medium and ILM the driver.

In the case of CLM, ILM also has a more direct connection through the Certificate Lifecycle Management agent through which ILM can provision, enroll requests, termination requests, suspend requests, renewal requests, and unblock requests.

Let's take a look at Active Directory Rights Management Services (RMS). With RMS permissions as with most other permissions, they are assigned to Groups in AD. Once more -- AD is the medium and ILM is the driver.

Now please turn your attention to Active Directory Federated Services (AD FS). Users get access to resources at the resource partner by virtue of having claim that gives them access, most of the time this claim will be a group claim. Once more -- ILM is driving through the medium of AD.

Even more, look at AD RMS integration with AD FS. Now we can extend Rights Management protection to documents while sharing them with partners without the unrealistic expectation for the partner to have their own AD RMS infrastructure (the requirement for RMS prior to Windows Server 2008). Once more, access for partners is through being member of a group that establishes an outgoing claim to the resource partner that is then consumed by RMS, and once more the best way to get users into groups is through ILM.

Expand your horizons, once more, now using a smart card (provisioned through an ILM request to CLM), we can authenticate to the Directory build the list of groups to which we belong (managed by ILM), we can access an RMS protected document at a Partner's SharePoint site, and have the appropriate restrictions apply to us.

Wait, what about AD Lightweight Directory Services (AD LDS -- formerly known as ADAM), and Windows Cardspace? Where do they fit in?

AD LDS can be used as another repository for storing identities usually for your extranet, for partners that aren't federation ready (either because of lack of size, technology, or policy). AD FS can use AD LDS as one of its account stores! Hence the same protection of RMS documents can be extended once more to non-federation partners without the need for another RMS infrastructure -- in fact vendors could offer RMS as a service using ADFS and AD LDS to cover the authentication needs.

What about Card Space? Card Space, can also be incorporated, but that is a topic for another day.

I want to give special thanks to Chris Calderon for his tireless efforts in helping me setup the virtual machines and hammering out the AD RMS AD FS integration with Sharepoint. Thanks also to David Wozny (pronounced Wahznee) for improving and delivering the deepdive into CLM. Thanks to Craig Martin for assisting David Wozny in improving the ILM deepdive. Additional thanks to Bob Tucker for helping with the VM setup. Thanks to Hugh Simpson-Wells and James Cowling for editing the labs. Thanks to James Booth for listening and improving while I dreamed up the scenarios used in the labs.